Tom Lane wrote:
Kris Jurka <books@xxxxxxxxxx> 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=917444 loops=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=2934179 loops=1)
-> Seq Scan on liens_s4 l (cost=0.00..21069.39 rows=1214139 width=18) (actual time=0.065..2413.429 rows=1214139 loops=1)
-> Seq Scan on liens_s5 l (cost=0.00..29966.37 rows=1726837 width=18) (actual time=0.046..3394.974 rows=1726837 loops=1)
-> Seq Scan on liens_s6 l (cost=0.00..10587.18 rows=462918 width=18) (actual time=0.053..936.379 rows=462918 loops=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=4956182 loops=1)
-> Seq Scan on liens_s9 l (cost=0.00..320.29 rows=18429 width=18) (actual time=0.010..34.880 rows=18429 loops=1)
-> Seq Scan on liens_s10 l (cost=0.00..18398.16 rows=951016 width=18) (actual time=0.055..1889.948 rows=951016 loops=1)
-> Seq Scan on liens_s11 l (cost=0.00..9956.22 rows=543022 width=18) (actual time=0.055..1070.156 rows=543022 loops=1)
-> Seq Scan on liens_s12 l (cost=0.00..78813.85 rows=4541785 width=18) (actual time=0.012..9431.035 rows=4541792 loops=1)
-> Seq Scan on liens_s13 l (cost=0.00..4506.46 rows=182846 width=18) (actual time=0.049..374.788 rows=182846 loops=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=0 loops=1)
-> Seq Scan on impounds_s2 i (cost=0.00..913.87 rows=29587 width=18) (actual time=0.008..60.728 rows=29587 loops=1)
-> Seq Scan on impounds_s3 i (cost=0.00..18.14 rows=414 width=18) (actual time=0.009..0.848 rows=414 loops=1)
-> Seq Scan on impounds_s4 i (cost=0.00..95.96 rows=3296 width=18) (actual time=0.012..6.894 rows=3296 loops=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=18449961 loops=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.668 rows=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=1192 loops=1)
-> Seq Scan on liens_s3 l (cost=0.00..53793.79 rows=2934179 width=18) (actual time=8.751..6311.537 rows=2934179 loops=1)
-> Seq Scan on liens_s4 l (cost=0.00..21069.39 rows=1214139 width=18) (actual time=0.039..2412.847 rows=1214139 loops=1)
-> Seq Scan on liens_s5 l (cost=0.00..29966.37 rows=1726837 width=18) (actual time=0.038..3805.214 rows=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=462918 loops=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.213 rows=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=18429 loops=1)
-> Seq Scan on liens_s10 l (cost=0.00..18398.16 rows=951016 width=18) (actual time=0.039..2078.649 rows=951016 loops=1)
-> Seq Scan on liens_s11 l (cost=0.00..9956.22 rows=543022 width=18) (actual time=0.039..1254.598 rows=543022 loops=1)
-> Seq Scan on liens_s12 l (cost=0.00..78813.85 rows=4541785 width=18) (actual time=0.044..10506.463 rows=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=182846 loops=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=0 loops=1)
-> Seq Scan on impounds_s2 i (cost=0.00..913.87 rows=29587 width=18) (actual time=0.022..59.502 rows=29587 loops=1)
-> Seq Scan on impounds_s3 i (cost=0.00..18.14 rows=414 width=18) (actual time=0.009..0.829 rows=414 loops=1)
-> Seq Scan on impounds_s4 i (cost=0.00..95.96 rows=3296 width=18) (actual time=0.011..6.646 rows=3296 loops=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=29587 loops=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=3296 loops=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=0 loops=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.109 rows=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.008 rows=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.921 rows=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.334 rows=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.800 rows=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.604 rows=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.003 rows=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.141 rows=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.037 rows=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.162 rows=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.099 rows=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.074 rows=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.238 rows=0 loops=33297)
Index Cond: ((l.vin)::text = (i.vin)::text)
Total runtime: 319060.547 ms
(41 rows)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
