0

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)"
"  ->  Seq Scan on products_by_location_mv  (cost=0.00..1457298.68 rows=24202368 width=721)"
"  ->  Hash  (cost=1414691.68..1414691.68 rows=25507868 width=643)"
"        ->  Seq Scan on obj4  (cost=0.00..1414691.68 rows=25507868 width=643)"
  • 2
    Indexing won't really help as you do want all the rows from both tables. There is no index that can speed up this query. – a_horse_with_no_name Mar 4 '14 at 16:03
  • To do much with this you'd need I/O and CPU parallelism. Not something PostgreSQL is good at I'm afraid. Perhaps check out Greenplum? – Craig Ringer Mar 4 '14 at 23:41
  • If I cut the number of columns, so that I am only trying to JOIN a 25M by 3 with a 25M by 2 would that speed things up? – Dr.YSG Mar 5 '14 at 15:16
0

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.

0

Like I said, it is a one time operation (I need it to do some one-time scrubbing of some big data, and it is never compliant like stuff in the ACID days).

That said, I did try it out with much fewer columns and the performance is very good. 37 minutes. (a quick VACUUM might also have helped).

See below:

SELECT
    public.obj4.id,
    public.obj4.size_bytes,
    public.obj4.object_date,
    public.obj4.description,
    public.pid_table.pid
INTO 
    public.obj_data
FROM
    public.obj4
INNER JOIN
    public.pid_table
ON
    (
        public.obj4.id = public.pid_table.id);
 16:33:22  [SELECT - 0 row(s), 2278.507 secs]  Command processed. No rows were affected
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 2278.507/0.000 sec  [0 successful, 1 warnings, 0 errors]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.