Re: No hash join across partitioned tables? - Mailing list pgsql-performance

Tom Lane wrote:
> Kris Jurka <books@ejurka.com> writes:
>> The hash join takes less than twenty seconds, the other two joins I
>> killed after five minutes.  I can try to collect explain analyze results
>> later today if you'd like.
>

Attached are the explain analyze results.  The analyze part hits the
hash join worst of all, so I've also included the timings without analyzing.

Method       Time (ms)  Time w/Analyze (ms)
nestloop     304853     319060
merge        514517     683757
hash          18957     143731

Kris Jurka
 Aggregate  (cost=116546928.75..116546928.76 rows=1 width=0) (actual time=143731.602..143731.604 rows=1 loops=1)
   ->  Hash Join  (cost=1470.48..108802907.84 rows=3097608361 width=0) (actual time=308.015..143724.055 rows=2437
loops=1)
         Hash Cond: ((l.vin)::text = (i.vin)::text)
         ->  Append  (cost=0.00..332098.75 rows=18450775 width=18) (actual time=0.069..99984.899 rows=18449996 loops=1)
               ->  Seq Scan on liens l  (cost=0.00..14.00 rows=400 width=21) (actual time=0.003..0.003 rows=0 loops=1)
               ->  Seq Scan on liens_s1 l  (cost=0.00..18633.44 rows=917444 width=18) (actual time=0.060..1828.740
rows=917444loops=1) 
               ->  Seq Scan on liens_s2 l  (cost=0.00..20.92 rows=1192 width=18) (actual time=0.010..2.274 rows=1192
loops=1)
               ->  Seq Scan on liens_s3 l  (cost=0.00..53793.79 rows=2934179 width=18) (actual time=0.054..5777.782
rows=2934179loops=1) 
               ->  Seq Scan on liens_s4 l  (cost=0.00..21069.39 rows=1214139 width=18) (actual time=0.065..2413.429
rows=1214139loops=1) 
               ->  Seq Scan on liens_s5 l  (cost=0.00..29966.37 rows=1726837 width=18) (actual time=0.046..3394.974
rows=1726837loops=1) 
               ->  Seq Scan on liens_s6 l  (cost=0.00..10587.18 rows=462918 width=18) (actual time=0.053..936.379
rows=462918loops=1) 
               ->  Seq Scan on liens_s7 l  (cost=0.00..14.00 rows=400 width=21) (actual time=0.003..0.003 rows=0
loops=1)
               ->  Seq Scan on liens_s8 l  (cost=0.00..86004.68 rows=4956168 width=18) (actual time=0.045..9729.965
rows=4956182loops=1) 
               ->  Seq Scan on liens_s9 l  (cost=0.00..320.29 rows=18429 width=18) (actual time=0.010..34.880
rows=18429loops=1) 
               ->  Seq Scan on liens_s10 l  (cost=0.00..18398.16 rows=951016 width=18) (actual time=0.055..1889.948
rows=951016loops=1) 
               ->  Seq Scan on liens_s11 l  (cost=0.00..9956.22 rows=543022 width=18) (actual time=0.055..1070.156
rows=543022loops=1) 
               ->  Seq Scan on liens_s12 l  (cost=0.00..78813.85 rows=4541785 width=18) (actual time=0.012..9431.035
rows=4541792loops=1) 
               ->  Seq Scan on liens_s13 l  (cost=0.00..4506.46 rows=182846 width=18) (actual time=0.049..374.788
rows=182846loops=1) 
         ->  Hash  (cost=1050.77..1050.77 rows=33577 width=18) (actual time=256.374..256.374 rows=33297 loops=1)
               ->  Append  (cost=0.00..1050.77 rows=33577 width=18) (actual time=0.019..188.152 rows=33297 loops=1)
                     ->  Seq Scan on impounds i  (cost=0.00..11.40 rows=140 width=21) (actual time=0.002..0.002 rows=0
loops=1)
                     ->  Seq Scan on impounds_s1 i  (cost=0.00..11.40 rows=140 width=21) (actual time=0.002..0.002
rows=0loops=1) 
                     ->  Seq Scan on impounds_s2 i  (cost=0.00..913.87 rows=29587 width=18) (actual time=0.008..60.728
rows=29587loops=1) 
                     ->  Seq Scan on impounds_s3 i  (cost=0.00..18.14 rows=414 width=18) (actual time=0.009..0.848
rows=414loops=1) 
                     ->  Seq Scan on impounds_s4 i  (cost=0.00..95.96 rows=3296 width=18) (actual time=0.012..6.894
rows=3296loops=1) 
 Total runtime: 143731.788 ms
(26 rows)

                                                                    QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=57241210.61..57241210.62 rows=1 width=0) (actual time=683467.350..683467.352 rows=1 loops=1)
   ->  Merge Join  (cost=2940810.41..49497189.70 rows=3097608361 width=0) (actual time=434026.342..683460.545 rows=2437
loops=1)
         Merge Cond: ((l.vin)::text = (i.vin)::text)
         ->  Sort  (cost=2937235.46..2983362.40 rows=18450775 width=18) (actual time=433519.957..637389.755
rows=18449961loops=1) 
               Sort Key: l.vin
               Sort Method:  external merge  Disk: 504728kB
               ->  Append  (cost=0.00..332098.75 rows=18450775 width=18) (actual time=14.764..102905.170 rows=18449996
loops=1)
                     ->  Seq Scan on liens l  (cost=0.00..14.00 rows=400 width=21) (actual time=0.003..0.003 rows=0
loops=1)
                     ->  Seq Scan on liens_s1 l  (cost=0.00..18633.44 rows=917444 width=18) (actual
time=14.755..2167.668rows=917444 loops=1) 
                     ->  Seq Scan on liens_s2 l  (cost=0.00..20.92 rows=1192 width=18) (actual time=0.012..2.304
rows=1192loops=1) 
                     ->  Seq Scan on liens_s3 l  (cost=0.00..53793.79 rows=2934179 width=18) (actual
time=8.751..6311.537rows=2934179 loops=1) 
                     ->  Seq Scan on liens_s4 l  (cost=0.00..21069.39 rows=1214139 width=18) (actual
time=0.039..2412.847rows=1214139 loops=1) 
                     ->  Seq Scan on liens_s5 l  (cost=0.00..29966.37 rows=1726837 width=18) (actual
time=0.038..3805.214rows=1726837 loops=1) 
                     ->  Seq Scan on liens_s6 l  (cost=0.00..10587.18 rows=462918 width=18) (actual time=0.037..990.027
rows=462918loops=1) 
                     ->  Seq Scan on liens_s7 l  (cost=0.00..14.00 rows=400 width=21) (actual time=0.003..0.003 rows=0
loops=1)
                     ->  Seq Scan on liens_s8 l  (cost=0.00..86004.68 rows=4956168 width=18) (actual
time=0.037..9992.213rows=4956182 loops=1) 
                     ->  Seq Scan on liens_s9 l  (cost=0.00..320.29 rows=18429 width=18) (actual time=0.012..35.403
rows=18429loops=1) 
                     ->  Seq Scan on liens_s10 l  (cost=0.00..18398.16 rows=951016 width=18) (actual
time=0.039..2078.649rows=951016 loops=1) 
                     ->  Seq Scan on liens_s11 l  (cost=0.00..9956.22 rows=543022 width=18) (actual
time=0.039..1254.598rows=543022 loops=1) 
                     ->  Seq Scan on liens_s12 l  (cost=0.00..78813.85 rows=4541785 width=18) (actual
time=0.044..10506.463rows=4541792 loops=1) 
                     ->  Seq Scan on liens_s13 l  (cost=0.00..4506.46 rows=182846 width=18) (actual time=7.880..591.710
rows=182846loops=1) 
         ->  Sort  (cost=3574.95..3658.89 rows=33577 width=18) (actual time=494.662..559.550 rows=33312 loops=1)
               Sort Key: i.vin
               Sort Method:  quicksort  Memory: 4138kB
               ->  Append  (cost=0.00..1050.77 rows=33577 width=18) (actual time=0.037..178.794 rows=33297 loops=1)
                     ->  Seq Scan on impounds i  (cost=0.00..11.40 rows=140 width=21) (actual time=0.005..0.005 rows=0
loops=1)
                     ->  Seq Scan on impounds_s1 i  (cost=0.00..11.40 rows=140 width=21) (actual time=0.002..0.002
rows=0loops=1) 
                     ->  Seq Scan on impounds_s2 i  (cost=0.00..913.87 rows=29587 width=18) (actual time=0.022..59.502
rows=29587loops=1) 
                     ->  Seq Scan on impounds_s3 i  (cost=0.00..18.14 rows=414 width=18) (actual time=0.009..0.829
rows=414loops=1) 
                     ->  Seq Scan on impounds_s4 i  (cost=0.00..95.96 rows=3296 width=18) (actual time=0.011..6.646
rows=3296loops=1) 
 Total runtime: 683757.901 ms
(31 rows)


 Aggregate  (cost=11171206.17..11171206.18 rows=1 width=0) (actual time=319059.9 81..319059.983 rows=1 loops=1)
   ->  Nested Loop  (cost=0.27..3427185.27 rows=3097608361 width=0) (actual time =2918.842..319051.903 rows=2437
loops=1)
         Join Filter: ((i.vin)::text = (l.vin)::text)
         ->  Append  (cost=0.00..1050.77 rows=33577 width=18) (actual time=0.034 ..259.582 rows=33297 loops=1)
               ->  Seq Scan on impounds i  (cost=0.00..11.40 rows=140 width=21) (actual time=0.004..0.004 rows=0
loops=1)
               ->  Seq Scan on impounds_s1 i  (cost=0.00..11.40 rows=140 width=21) (actual time=0.002..0.002 rows=0
loops=1)
               ->  Seq Scan on impounds_s2 i  (cost=0.00..913.87 rows=29587 width=18) (actual time=0.021..111.905
rows=29587loops=1) 
               ->  Seq Scan on impounds_s3 i  (cost=0.00..18.14 rows=414 width=18) (actual time=4.488..6.009 rows=414
loops=1)
               ->  Seq Scan on impounds_s4 i  (cost=0.00..95.96 rows=3296 width=18) (actual time=8.542..27.109
rows=3296loops=1) 
         ->  Append  (cost=0.27..101.85 rows=15 width=19) (actual time=9.048..9.563 rows=0 loops=33297)
               ->  Bitmap Heap Scan on liens l  (cost=0.27..5.60 rows=2 width=21) (actual time=0.007..0.007 rows=0
loops=33297)
                     Recheck Cond: ((l.vin)::text = (i.vin)::text)
                     ->  Bitmap Index Scan on liens_pk (cost=0.00..0.27 rows=2 width=0) (actual time=0.003..0.003
rows=0loops=33297) 
                           Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using newliens_s1_pk on liens_s1 l (cost=0.00..7.60 rows=1 width=18) (actual
time=1.109..1.109rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using liens_s2_pk on liens_s2 l (cost=0.00..7.32 rows=1 width=18) (actual
time=0.008..0.008rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using newliens_s3_pk on liens_s3 l (cost=0.00..8.07 rows=1 width=18) (actual
time=0.921..0.921rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using newliens_s4_pk on liens_s4 l (cost=0.00..7.67 rows=1 width=18) (actual
time=1.334..1.334rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using newliens_s5_pk on liens_s5 l (cost=0.00..7.79 rows=1 width=18) (actual
time=1.800..1.800rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using newliens_s6_pk on liens_s6 l (cost=0.00..7.49 rows=1 width=18) (actual
time=0.604..0.604rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using liens_s7_pk on liens_s7 l (cost=0.00..3.47 rows=1 width=21) (actual
time=0.003..0.003rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using newliens_s8_pk on liens_s8 l (cost=0.00..8.53 rows=1 width=18) (actual
time=1.141..1.141rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using newliens_s9_pk on liens_s9 l (cost=0.00..7.33 rows=1 width=18) (actual
time=0.037..0.037rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using newliens_s10_pk on liens_s10 l (cost=0.00..7.61 rows=1 width=18) (actual
time=1.162..1.162rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using newliens_s11_pk on liens_s11 l (cost=0.00..7.51 rows=1 width=18) (actual
time=0.099..0.099rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using newliens_s12_pk on liens_s12 l (cost=0.00..8.43 rows=1 width=18) (actual
time=1.073..1.074rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
               ->  Index Scan using newliens_s13_pk on liens_s13 l (cost=0.00..7.43 rows=1 width=18) (actual
time=0.238..0.238rows=0 loops=33297) 
                     Index Cond: ((l.vin)::text = (i.vin)::text)
 Total runtime: 319060.547 ms
(41 rows)