issue with query optimizer when joining two partitioned tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have run into issue where the query optimizer is choosing the wrong execution plan when I'm trying to join two large tables that have been partitioned.  I would really appreciate it if someone could help me out this.  I don't know whether I've found a bug in the optimizer, or whether there is some parameter/option I need to set in postgres.  Below, I've included my execution plans.  I'm using postgres 9.0.3, and I'm running this on a pretty beefy Linux server.

My two tables:
-widget: has 4041866 records, and is broken up into 4 partitions (no records are in the parent table).
-icecream: I'm starting with zero records, but since this there could be billions of ice-cream records, I will partition and will not have any records in the parent table.

So, then I then create my first partition in icecream table, and load 4041866 records into it.

Here is the query I'm using to join the two tables:
explain analyze
SELECT
      r.widget_id,
      r.widget_type_id,
      avg(rc.cost)::double precision cost_avg
FROM
      widget r,
      icecream rc
WHERE
r.widget_type_id = 4
and r.widgetset_id = 5
AND r.widget_id = rc.widget_id
and rc.dataset_id = 281
group by r.widget_id,r.chromosome, r.start_pos, r.end_pos,r.widget_type_id
;

Here is the corresponding execution plan:

                                                                                          QUERY PLAN                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=147262.20..147299.12 rows=1136 width=41) (actual time=31876.290..31904.880 rows=11028 loops=1)
   ->  Merge Join  (cost=95574.83..112841.79 rows=1147347 width=41) (actual time=31130.870..31832.922 rows=11028 loops=1)
         Merge Cond: (r.widget_id = rc.widget_id)
         ->  Sort  (cost=1913.89..1942.27 rows=11352 width=21) (actual time=56.818..68.701 rows=11028 loops=1)
               Sort Key: r.widget_id
               Sort Method:  quicksort  Memory: 1246kB
               ->  Append  (cost=4.28..1149.30 rows=11352 width=21) (actual time=0.139..40.513 rows=11028 loops=1)
                     ->  Bitmap Heap Scan on widget r  (cost=4.28..12.75 rows=1 width=48) (actual time=0.030..0.030 rows=0 loops=1)
                           Recheck Cond: (widgetset_id = 5)
                           Filter: (widget_type_id = 4)
                           ->  Bitmap Index Scan on widget_widgetset_id_idx  (cost=0.00..4.28 rows=4 width=0) (actual time=0.023..0.023 rows=0 loops=1)
                                 Index Cond: (widgetset_id = 5)
                     ->  Index Scan using widget_part_5_widget_widget_type_id_idx on widget_part_5 r  (cost=0.00..1136.55 rows=11351 width=21) (actual time=0.106..18.489 rows=11028 loops=1)
                           Index Cond: (widget_type_id = 4)
                           Filter: (widgetset_id = 5)
         ->  Sort  (cost=93660.94..93711.47 rows=20214 width=24) (actual time=29730.522..30766.354 rows=946140 loops=1)
               Sort Key: rc.widget_id
               Sort Method:  external sort  Disk: 165952kB
               ->  Append  (cost=0.00..92215.33 rows=20214 width=24) (actual time=0.057..13731.204 rows=4041866 loops=1)
                     ->  Seq Scan on icecream rc  (cost=0.00..23.00 rows=5 width=24) (actual time=0.002..0.002 rows=0 loops=1)
                           Filter: (dataset_id = 281)
                     ->  Seq Scan on icecream_part_281 rc  (cost=0.00..92192.33 rows=20209 width=24) (actual time=0.051..5427.730 rows=4041866 loops=1)
                           Filter: (dataset_id = 281)
 Total runtime: 33182.945 ms
(24 rows)


The query is doing a merge join, is taking 33 seconds, but should take less than a second.  So, then I do: select * from icecream;

Now, when I run the same query again, I get a different and correct execution plan (nested loop), and the query takes less than 1 second as I would expect.


                                                                                        QUERY PLAN                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=7223611.41..7223648.33 rows=1136 width=41) (actual time=392.822..420.166 rows=11028 loops=1)
   ->  Nested Loop  (cost=4.28..341195.22 rows=229413873 width=41) (actual time=0.231..331.800 rows=11028 loops=1)
         Join Filter: (r.widget_id = rc.widget_id)
         ->  Append  (cost=4.28..1149.30 rows=11352 width=21) (actual time=0.051..50.181 rows=11028 loops=1)
               ->  Bitmap Heap Scan on widget r  (cost=4.28..12.75 rows=1 width=48) (actual time=0.013..0.013 rows=0 loops=1)
                     Recheck Cond: (widgetset_id = 5)
                     Filter: (widget_type_id = 4)
                     ->  Bitmap Index Scan on widget_widgetset_id_idx  (cost=0.00..4.28 rows=4 width=0) (actual time=0.007..0.007 rows=0 loops=1)
                           Index Cond: (widgetset_id = 5)
               ->  Index Scan using widget_part_5_widget_widget_type_id_idx on widget_part_5 r  (cost=0.00..1136.55 rows=11351 width=21) (actual time=0.033..21.254 rows=11028 loops=1)
                     Index Cond: (widget_type_id = 4)
                     Filter: (widgetset_id = 5)
         ->  Append  (cost=0.00..29.88 rows=6 width=24) (actual time=0.014..0.018 rows=1 loops=11028)
               ->  Seq Scan on icecream rc  (cost=0.00..23.00 rows=5 width=24) (actual time=0.001..0.001 rows=0 loops=11028)
                     Filter: (rc.dataset_id = 281)
               ->  Index Scan using icecream_part_281_widget_id_idx on icecream_part_281 rc  (cost=0.00..6.88 rows=1 width=24) (actual time=0.009..0.010 rows=1 loops=11028)
                     Index Cond: (rc.widget_id = r.widget_id)
                     Filter: (rc.dataset_id = 281)
 Total runtime: 431.935 ms
(19 rows)


My guess as to what happened:
-because the icecream parent table has zero records, the query optimizer chooses the incorrect execution plan
-when I do select * from icecream, the optimizer now knows how many records are really in the icecream table, by knowing that the icecream table has partitions.

Next, if I run vacuum analyze on the parent table, I again get a wrong/slow execution plan (this time it uses the hash join).  Again, I think this is because the parent table itself has zero records.


                                                                                       QUERY PLAN                                                                                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=191926.03..191962.95 rows=1136 width=41) (actual time=28967.567..28994.395 rows=11028 loops=1)
   ->  Hash Join  (cost=166424.79..191585.47 rows=11352 width=41) (actual time=28539.196..28917.830 rows=11028 loops=1)
         Hash Cond: (r.widget_id = rc.widget_id)
         ->  Append  (cost=4.28..1149.30 rows=11352 width=21) (actual time=0.054..54.068 rows=11028 loops=1)
               ->  Bitmap Heap Scan on widget r  (cost=4.28..12.75 rows=1 width=48) (actual time=0.013..0.013 rows=0 loops=1)
                     Recheck Cond: (widgetset_id = 5)
                     Filter: (widget_type_id = 4)
                     ->  Bitmap Index Scan on widget_widgetset_id_idx  (cost=0.00..4.28 rows=4 width=0) (actual time=0.007..0.007 rows=0 loops=1)
                           Index Cond: (widgetset_id = 5)
               ->  Index Scan using widget_part_5_widget_widget_type_id_idx on widget_part_5 r  (cost=0.00..1136.55 rows=11351 width=21) (actual time=0.035..22.419 rows=11028 loops=1)
                     Index Cond: (widget_type_id = 4)
                     Filter: (widgetset_id = 5)
         ->  Hash  (cost=92214.73..92214.73 rows=4041823 width=24) (actual time=28438.419..28438.419 rows=4041866 loops=1)
               Buckets: 524288  Batches: 2  Memory Usage: 118449kB
               ->  Append  (cost=0.00..92214.73 rows=4041823 width=24) (actual time=0.020..14896.908 rows=4041866 loops=1)
                     ->  Seq Scan on icecream rc  (cost=0.00..23.00 rows=5 width=24) (actual time=0.002..0.002 rows=0 loops=1)
                           Filter: (dataset_id = 281)
                     ->  Seq Scan on icecream_part_281 rc  (cost=0.00..92191.73 rows=4041818 width=24) (actual time=0.012..5718.592 rows=4041866 loops=1)
                           Filter: (dataset_id = 281)
 Total runtime: 29007.937 ms
(20 rows)


select * from icecream does not fix this issue.


I could of course disable hash join and merge join to force postgres to use a nested loop, but my system is often joining these two tables, and I'd rather not have to set this in every single place.
set enable_mergejoin=off;
set enable_hashjoin=off;
set enable_nestloop = on;


thanks in advance!!!

Anish







[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux