Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C8E1C632CBA for ; Fri, 17 Apr 2009 14:05:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 99533-02 for ; Fri, 17 Apr 2009 14:05:36 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail63.csoft.net (mail63.csoft.net [205.205.214.4]) by mail.postgresql.org (Postfix) with ESMTP id 5E37362DBDD for ; Fri, 17 Apr 2009 14:05:36 -0300 (ADT) Received: from [192.168.1.141] (adsl-71-128-101-241.dsl.irvnca.pacbell.net [71.128.101.241]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail63.csoft.net (Postfix) with ESMTP id 4F74928441; Fri, 17 Apr 2009 13:05:34 -0400 (EDT) Message-ID: <49E8B6DC.5040508@ejurka.com> Date: Fri, 17 Apr 2009 10:05:32 -0700 From: Kris Jurka User-Agent: Mozilla-Thunderbird 2.0.0.19 (X11/20090103) MIME-Version: 1.0 To: Tom Lane CC: pgsql-performance@postgresql.org Subject: Re: No hash join across partitioned tables? References: <7163.1239923531@sss.pgh.pa.us> <21238.1239980554@sss.pgh.pa.us> <49E89B29.10705@ejurka.com> <21400.1239980904@sss.pgh.pa.us> In-Reply-To: <21400.1239980904@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------080603070007010409050100" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.41 tagged_above=0 required=5 tests=SUBJECT_FUZZY_TION=0.41 X-Spam-Level: X-Archive-Number: 200904/289 X-Sequence-Number: 33656 This is a multi-part message in MIME format. --------------080603070007010409050100 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: > Kris Jurka 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 --------------080603070007010409050100 Content-Type: text/plain; name="hash.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="hash.txt" 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) --------------080603070007010409050100 Content-Type: text/plain; name="merge.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="merge.txt" 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) --------------080603070007010409050100 Content-Type: text/plain; name="nestloop.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="nestloop.txt" 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) --------------080603070007010409050100--