25M x 25M inner join (postgresql) performance I have a one time need to do an inner join of 25M rows on 25M rows. The box is a Alienware area 51, 4 cores 25GB memory and SATA drive (non system disk). So far it has taken 22 hours. I did btree index the ID (bigint) column which the join is being done on for both tables. Any tips? How long do you think I have to wait?

EXPLAIN SELECT
    public.products_by_location_mv.id,
    public.products_by_location_mv."data_object.unique_id",
    public.products_by_location_mv.location AS outline,
    public.products_by_location_mv.elevation_ft,
    public.products_by_location_mv."geo_product.geo_product_id" AS pid,
    public.products_by_location_mv.cntry_name,
    public.products_by_location_mv.product_name,
    public.products_by_location_mv.product_type,
    public.products_by_location_mv.product_producer,
    public.products_by_location_mv.product_size,
    public.products_by_location_mv.do_location,
    public.products_by_location_mv.product_location,
    public.obj4.uid AS oid,
    public.obj4.size_bytes,
    public.obj4.object_date,
    public.obj4.description,
    public.obj4.location AS path
INTO
    public.inventory0
FROM
    public.obj4
INNER JOIN
    public.products_by_location_mv
ON
    (
        public.obj4.id = public.products_by_location_mv.id) ;




"Hash Join  (cost=3825983.03..12908235.27 rows=24202368 width=1356)"
"  Hash Cond: (products_by_location_mv.id = obj4.id)"
"  -&gt;  Seq Scan on products_by_location_mv  (cost=0.00..1457298.68 rows=24202368 width=721)"
"  -&gt;  Hash  (cost=1414691.68..1414691.68 rows=25507868 width=643)"
"        -&gt;  Seq Scan on obj4  (cost=0.00..1414691.68 rows=25507868 width=643)"
Although it is hard for me to understand the motivation for this kind of query, you cannot expect any kind of efficiency since the query results will not fit in main memory. If you want the results stored somehow, the only way to do it is through the COPY command (http://www.postgresql.org/docs/9.2/static/sql-copy.html). COPY has the advantage that once a row is retrieved from the query it goes directly to secondary storage. This way you actually have a chance for the query to finish after a few hours. Then, if needed you can use COPY again to load those results to another DB table.