Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id C7774B5DC4B for ; Wed, 31 Aug 2011 03:07:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 98777-06-5 for ; Wed, 31 Aug 2011 06:07:48 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.catalyst.net.nz (bertrand.catalyst.net.nz [202.78.240.40]) by mail.postgresql.org (Postfix) with ESMTP id BA4BFB5DBE0 for ; Wed, 31 Aug 2011 03:07:42 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mail.catalyst.net.nz (Postfix) with ESMTP id 558CB32442; Wed, 31 Aug 2011 18:07:40 +1200 (NZST) X-Virus-Scanned: Debian amavisd-new at catalyst.net.nz Received: from mail.catalyst.net.nz ([127.0.0.1]) by localhost (bertrand.catalyst.net.nz [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id tsmZTqzGs5h3; Wed, 31 Aug 2011 18:07:38 +1200 (NZST) Received: from [IPv6:2404:130:0:1000:222:15ff:fe5c:2471] (unknown [IPv6:2404:130:0:1000:222:15ff:fe5c:2471]) (Authenticated sender: mark.kirkwood) by mail.catalyst.net.nz (Postfix) with ESMTPSA id 05A50320F3; Wed, 31 Aug 2011 18:07:38 +1200 (NZST) Message-ID: <4E5DCFA4.5090806@catalyst.net.nz> Date: Wed, 31 Aug 2011 18:07:32 +1200 From: Mark Kirkwood User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.20) Gecko/20110805 Lightning/1.0b2 Thunderbird/3.1.12 MIME-Version: 1.0 To: Tom Lane CC: PGSQL Performance Subject: Re: 8.4 optimization regression? References: <4E5331FF.3000900@catalyst.net.nz> <15708.1314155740@sss.pgh.pa.us> In-Reply-To: <15708.1314155740@sss.pgh.pa.us> Content-Type: multipart/mixed; boundary="------------030402040109060402030009" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.404 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.504 X-Spam-Level: X-Archive-Number: 201108/308 X-Sequence-Number: 44755 This is a multi-part message in MIME format. --------------030402040109060402030009 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 24/08/11 15:15, Tom Lane wrote: > Mark Kirkwood writes: >> I am in the progress of an 8.3 to 8.4 upgrade for a customer. I seem to >> have stumbled upon what looks like a regression. The two databases >> (8.3.14 and 8.4.8) have identical tuning parameters (where that makes >> sense) and run on identical hardware. Both databases are regularly >> vacuumed and analyzed (not by autovacuum), and performing an ANALYZE >> does not change the plans shown below. > Hmmm ... this is structurally a pretty simple query, so I'm surprised > that 8.3 and 8.4 see it very much differently. The relation-level > estimates and plan choices are very nearly the same; the only thing > that's changed much is the estimates of the join sizes, and there were > not that many changes in the join selectivity estimation for simple > inner joins. I wonder whether you are seeing a bad side-effect of this > patch: > > http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=7f3eba30 > > That code would only be reached when one or both join columns lack MCV > lists in pg_stats; if you had analyzed, the only reason for that to be > the case is if the column is unique (or nearly so, in ANALYZE's opinion). > > I've come up with (hopefully) a good set of semi, anti and regular joins to demonstrate the effect of this commit. I've attached them, and the schema generator (I believe I've used this before for optimization examples...). Also I've tried out an experimental patch to make joins like the one I'm having trouble with *and* also the anti joins the commit was for - get better row estimates. So firstly consider an anti join (these are run against git HEAD rather than 8.4): EXPLAIN ANALYZE SELECT 1 FROM nodekeyword nk WHERE nk.keywordid < 100000 AND NOT EXISTS (SELECT 1 FROM node n WHERE n.nodeid = nk.nodeid AND n.updated > '2011-12-01'::timestamp ); With commit: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Hash Anti Join (cost=426079.34..765699.66 rows=1599293 width=0) (actual time=29907.716..47255.825 rows=1839193 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=5.373..11838.738 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=29883.980..29883.980 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=0.339..29295.764 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) Without commit: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Hash Anti Join (cost=426079.34..760501.96 rows=1 width=0) (actual time=30409.336..47919.613 rows=1839193 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=5.359..12081.372 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=30392.235..30392.235 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=0.384..29806.407 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) Note the rows estimate for the anti join is hopelessly wrong, so clearly the commitdoes the job here (I think this models the test case for said commit)! Now some joins: EXPLAIN ANALYZE SELECT 1 FROM NODE n JOIN nodekeyword nk ON (n.nodeid = nk.nodeid) WHERE n.updated > '2011-01-01'::timestamp AND nk.keywordid < 100000; With commit: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=501666.88..871512.65 rows=1991560 width=0) (actual time=30032.836..53073.731 rows=1993866 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=5.327..14393.629 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=4999510 width=4) (actual time=30017.777..30017.777 rows=4985269 loops=1) Buckets: 4096 Batches: 256 Memory Usage: 699kB -> Seq Scan on node n (cost=0.00..419643.00 rows=4999510 width=4) (actual time=0.005..23272.287 rows=4985269 loops=1) Filter: (updated > '2011-01-01 00:00:00'::timestamp without time zone) Without commit: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Hash Join (cost=501666.88..871510.70 rows=1991365 width=0) (actual time=30549.498..54852.399 rows=1993866 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=5.331..13760.417 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=4999510 width=4) (actual time=30534.464..30534.464 rows=4985269 loops=1) Buckets: 4096 Batches: 256 Memory Usage: 699kB -> Seq Scan on node n (cost=0.00..419643.00 rows=4999510 width=4) (actual time=0.005..23696.167 rows=4985269 loops=1) Filter: (updated > '2011-01-01 00:00:00'::timestamp without time zone) Another join: EXPLAIN ANALYZE SELECT 1 FROM NODE n JOIN nodekeyword nk ON (n.nodeid = nk.nodeid) WHERE n.updated > '2011-12-01'::timestamp AND nk.keywordid < 100000; With commit: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Hash Join (cost=426079.34..764424.63 rows=392267 width=0) (actual time=29295.966..45578.876 rows=160587 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=12.452..12367.760 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=29273.571..29273.571 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=10.899..28678.818 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) Without commit: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Hash Join (cost=426079.34..762064.41 rows=156245 width=0) (actual time=29179.313..44605.243 rows=160587 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=12.486..11546.469 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=29156.889..29156.889 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=10.915..28545.553 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) So in the case where we filer out a large percentage of the rows the commit inflates the estimates...consider a more extreme example: EXPLAIN ANALYZE SELECT 1 FROM NODE n JOIN nodekeyword nk ON (n.nodeid = nk.nodeid) WHERE n.updated > '2011-12-27'::timestamp AND nk.keywordid < 10000; With commit: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..569488.45 rows=16344 width=0) (actual time=55.452..65341.135 rows=604 loops=1) -> Seq Scan on node n (cost=0.00..419643.00 rows=16344 width=4) (actual time=13.537..46138.214 rows=14952 loops=1) Filter: (updated > '2011-12-27 00:00:00'::timestamp without time zone) -> Index Scan using nodekeyword_pk on nodekeyword nk (cost=0.00..9.16 rows=1 width=4) (actual time=1.277..1.279 rows=0 loops=14952) Index Cond: ((nodeid = n.nodeid) AND (keywordid < 10000)) Without commit: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..569488.45 rows=631 width=0) (actual time=43.969..64988.036 rows=604 loops=1) -> Seq Scan on node n (cost=0.00..419643.00 rows=16344 width=4) (actual time=2.060..46065.879 rows=14952 loops=1) Filter: (updated > '2011-12-27 00:00:00'::timestamp without time zone) -> Index Scan using nodekeyword_pk on nodekeyword nk (cost=0.00..9.16 rows=1 width=4) (actual time=1.259..1.260 rows=0 loops=14952) Index Cond: ((nodeid = n.nodeid) AND (keywordid < 10000)) So clearly this commit is not so good for this type of join (this models the case I posted initially). Now four semi joins: EXPLAIN ANALYZE SELECT 1 FROM nodekeyword nk WHERE nk.keywordid < 100000 AND EXISTS (SELECT 1 FROM node n WHERE n.nodeid = nk.nodeid AND n.updated > '2011-12-01'::timestamp ); With commit: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=426079.34..753629.40 rows=392267 width=0) (actual time=28405.965..43724.471 rows=160587 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=5.767..11561.340 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=28391.293..28391.293 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=0.038..27820.097 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) Without commit: QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=426079.34..780417.56 rows=1991560 width=0) (actual time=29447.638..44738.280 rows=160587 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=5.771..11501.350 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=29433.952..29433.952 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=0.040..28850.800 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) Clearly row estimation is hopelessly broken *without* this commit here. Another semi join: EXPLAIN ANALYZE SELECT 1 FROM nodekeyword nk WHERE nk.keywordid < 100000 AND EXISTS (SELECT 1 FROM node n WHERE n.nodeid = nk.nodeid AND n.updated > '2011-01-01'::timestamp ); With commit: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Hash Semi Join (cost=501666.88..871512.65 rows=1991560 width=0) (actual time=29048.154..51230.453 rows=1993866 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=12.423..13430.618 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=4999510 width=4) (actual time=29024.442..29024.442 rows=4985269 loops=1) Buckets: 4096 Batches: 256 Memory Usage: 699kB -> Seq Scan on node n (cost=0.00..419643.00 rows=4999510 width=4) (actual time=0.010..22384.904 rows=4985269 loops=1) Filter: (updated > '2011-01-01 00:00:00'::timestamp without time zone) Without commit: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Hash Semi Join (cost=501666.88..871512.65 rows=1991560 width=0) (actual time=28914.970..51162.918 rows=1993866 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=12.504..13780.506 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=4999510 width=4) (actual time=28891.705..28891.705 rows=4985269 loops=1) Buckets: 4096 Batches: 256 Memory Usage: 699kB -> Seq Scan on node n (cost=0.00..419643.00 rows=4999510 width=4) (actual time=0.008..22082.459 rows=4985269 loops=1) Filter: (updated > '2011-01-01 00:00:00'::timestamp without time zone) Another semi join: EXPLAIN ANALYZE SELECT 1 FROM nodekeyword nk WHERE nk.keywordid < 10000 AND EXISTS (SELECT 1 FROM node n WHERE n.nodeid = nk.nodeid AND n.updated > '2011-01-01'::timestamp ); With commit: QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=501666.88..823736.17 rows=192921 width=0) (actual time=30120.347..49646.175 rows=199050 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=192921 width=4) (actual time=12.359..16335.889 rows=199616 loops=1) Filter: (keywordid < 10000) -> Hash (cost=419643.00..419643.00 rows=4999510 width=4) (actual time=30072.444..30072.444 rows=4985269 loops=1) Buckets: 4096 Batches: 256 Memory Usage: 699kB -> Seq Scan on node n (cost=0.00..419643.00 rows=4999510 width=4) (actual time=0.009..23409.799 rows=4985269 loops=1) Filter: (updated > '2011-01-01 00:00:00'::timestamp without time zone) Without commit: QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=501666.88..823736.17 rows=192921 width=0) (actual time=29395.513..48857.600 rows=199050 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=192921 width=4) (actual time=12.528..16261.983 rows=199616 loops=1) Filter: (keywordid < 10000) -> Hash (cost=419643.00..419643.00 rows=4999510 width=4) (actual time=29348.826..29348.826 rows=4985269 loops=1) Buckets: 4096 Batches: 256 Memory Usage: 699kB -> Seq Scan on node n (cost=0.00..419643.00 rows=4999510 width=4) (actual time=0.009..22505.930 rows=4985269 loops=1) Filter: (updated > '2011-01-01 00:00:00'::timestamp without time zone) Final semi join: EXPLAIN ANALYZE SELECT 1 FROM nodekeyword nk WHERE nk.keywordid < 10000 AND EXISTS (SELECT 1 FROM node n WHERE n.nodeid = nk.nodeid AND n.updated > '2011-12-01'::timestamp ); With commit: QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=426079.34..730392.78 rows=192921 width=0) (actual time=29060.665..44713.615 rows=16003 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=192921 width=4) (actual time=12.366..15064.457 rows=199616 loops=1) Filter: (keywordid < 10000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=29026.017..29026.017 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=0.039..28441.039 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) Without commit: QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=426079.34..730392.78 rows=192921 width=0) (actual time=28969.107..43725.339 rows=16003 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=192921 width=4) (actual time=12.486..14198.613 rows=199616 loops=1) Filter: (keywordid < 10000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=28935.248..28935.248 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=0.047..28343.005 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) Well this guy is too sneaky for either case :-( We seem to need a patch variant that *only* clamps the estimates in the anti or semi join case, e.g (note against git HEAD): diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index e065826..bf5002f 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -2257,11 +2257,6 @@ eqjoinsel_inner(Oid operator, double nullfrac1 = stats1 ? stats1->stanullfrac : 0.0; double nullfrac2 = stats2 ? stats2->stanullfrac : 0.0; - if (vardata1->rel) - nd1 = Min(nd1, vardata1->rel->rows); - if (vardata2->rel) - nd2 = Min(nd2, vardata2->rel->rows); - selec = (1.0 - nullfrac1) * (1.0 - nullfrac2); if (nd1 > nd2) selec /= nd1; Now run all the queries, 1st the anti join: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Hash Anti Join (cost=426079.34..765699.66 rows=1599293 width=0) (actual time=30121.008..48503.453 rows=1839193 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=12.623..12853.522 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=30108.058..30108.058 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=0.347..29508.393 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) And the 3 joins: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Hash Join (cost=501666.88..871510.70 rows=1991365 width=0) (actual time=30148.073..52370.308 rows=1993866 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=12.291..13300.233 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=4999510 width=4) (actual time=30124.453..30124.453 rows=4985269 loops=1) Buckets: 4096 Batches: 256 Memory Usage: 699kB -> Seq Scan on node n (cost=0.00..419643.00 rows=4999510 width=4) (actual time=0.009..23334.774 rows=4985269 loops=1) Filter: (updated > '2011-01-01 00:00:00'::timestamp without time zone) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Hash Join (cost=426079.34..762064.41 rows=156245 width=0) (actual time=29954.251..46014.379 rows=160587 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=12.420..12126.142 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=29936.578..29936.578 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=10.934..29357.789 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..569488.45 rows=631 width=0) (actual time=44.065..67179.686 rows=604 loops=1) -> Seq Scan on node n (cost=0.00..419643.00 rows=16344 width=4) (actual time=2.165..48523.075 rows=14952 loops=1) Filter: (updated > '2011-12-27 00:00:00'::timestamp without time zone) -> Index Scan using nodekeyword_pk on nodekeyword nk (cost=0.00..9.16 rows=1 width=4) (actual time=1.241..1.242 rows=0 loops=14952) Index Cond: ((nodeid = n.nodeid) AND (keywordid < 10000)) And the 4 semi joins... QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=426079.34..753629.40 rows=392267 width=0) (actual time=29355.949..45220.958 rows=160587 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=5.731..11983.132 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=29342.387..29342.387 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=0.039..28763.514 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Hash Semi Join (cost=501666.88..871512.65 rows=1991560 width=0) (actual time=30823.334..53136.910 rows=1993866 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=1991560 width=4) (actual time=12.555..13881.366 rows=1999780 loops=1) Filter: (keywordid < 100000) -> Hash (cost=419643.00..419643.00 rows=4999510 width=4) (actual time=30800.017..30800.017 rows=4985269 loops=1) Buckets: 4096 Batches: 256 Memory Usage: 699kB -> Seq Scan on node n (cost=0.00..419643.00 rows=4999510 width=4) (actual time=0.010..24028.932 rows=4985269 loops=1) Filter: (updated > '2011-01-01 00:00:00'::timestamp without time zone) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=501666.88..823736.17 rows=192921 width=0) (actual time=29278.861..48346.647 rows=199050 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=192921 width=4) (actual time=12.523..15809.390 rows=199616 loops=1) Filter: (keywordid < 10000) -> Hash (cost=419643.00..419643.00 rows=4999510 width=4) (actual time=29232.161..29232.161 rows=4985269 loops=1) Buckets: 4096 Batches: 256 Memory Usage: 699kB -> Seq Scan on node n (cost=0.00..419643.00 rows=4999510 width=4) (actual time=0.009..22541.899 rows=4985269 loops=1) Filter: (updated > '2011-01-01 00:00:00'::timestamp without time zone) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Hash Semi Join (cost=426079.34..730392.78 rows=192921 width=0) (actual time=28594.210..42976.001 rows=16003 loops=1) Hash Cond: (nk.nodeid = n.nodeid) -> Seq Scan on nodekeyword nk (cost=0.00..297414.03 rows=192921 width=4) (actual time=12.581..13810.924 rows=199616 loops=1) Filter: (keywordid < 10000) -> Hash (cost=419643.00..419643.00 rows=392267 width=4) (actual time=28560.258..28560.258 rows=401678 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 891kB -> Seq Scan on node n (cost=0.00..419643.00 rows=392267 width=4) (actual time=0.048..27983.235 rows=401678 loops=1) Filter: (updated > '2011-12-01 00:00:00'::timestamp without time zone) (this last one was wildly inaccurate pre patching) So this looks quite encouraging (unless I have overlooked a set of queries that now perform worse - which could be the case), thoughts? regards Mark --------------030402040109060402030009 Content-Type: application/x-gzip; name="join-schema.tar.gz" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="join-schema.tar.gz" H4sIAJvBXU4AA+1c+3PaRhD2r+iv2BBngAbLSEgoE5K0xJYdWgwZINOkaScjo7OtGCQiCTvO 43/v3p0EskDCbcNjam08tnSPvef37d5D+ehY9v7OaqWCoqkq/StpqhT9G8qOJNVkrapUVA3T SZJc0XZAXXG9mEw833ABdsaO55+7xEtKZ54a66jOuuUjHX+PjCz6IIvep+GPL4MOcE1REse/ inODj39FVnHgK1K1Jld3oPLjqzIv93z89/aEvT2AvnsDzsSHM8cF/4KATzwfBoaHTxeGj78s D8aGP7iAoXVJPFEUMZugv33dajTb0Gg3Wu/+0KGnt/SDPkhw1O2cgO2Y5JLcXDuuCfYl/P5K 7+r4IAZhlgnPQGITABUcgv622ev3oDivBOwws0hfMeNzqid4pnltcTI2DZ+Y8AIKMvLHXoX+ FJ4+9a0RNsUYjaFUFzbd2VsoDP8rxD6VZfhXKhLHv1xTqxqmk2RN0TL8r0M4/p+IChi2CY59 bbimB+fEh1Pi+8QFRI81Qmh5ATcgEVwZrmXYPlxfWEgI3sCwPRgSzwPnjJOHcYqvMHDsAXFt Yt6BK9qdQ4Q3/NrBFDHe6LShuAj4pSkpxLEvyXHsM45YxDz3nhMY/nEwmf2XNmT/a1P8a2q1 yu1/LcP/OmR77H+70//BPsA8D2Q+QFxu+f/Kpvx/Zer/a6rC8F+tZPhfh2wD/lfg/mfQv6Nw /A8uyMhY2QJgCf4lSZUR/6qiaLKCyKf4l2qZ/V+LIPiN8dh1PoPKRyNw4j0iHHT1Rl+HfuNl S+dALAo5jrpcrtnu68d6l1nt9ptWqyzkAgzmcv3mid7rN05el3l6k3iDXF9/28f3M2s4JG6O vQqISco+ximlHim1/IHhh1XAx4QapFSOlwtM5stWUwtOLZQqxEjWRtaq+VJTWpvW3SF3pvf6 lE3/XasTaxApPbWIIDK97SCUMvbdSmH8f05shK4hjlfH/yn7/1pVo/v/qippSk3h+78SLgkz /l+DPHywP/Hc/VOcBWPiDoWH+E+YoN/n+a418Ovs+dpwbcs+95A8Rjewa1ouumCFfTpnKvvm ZDQu1FmExf94A2NIMIXENlhYEDKk61x7GKjCT0GKehhjmezx0dglV+wVCxq7lu1DHmcmcdGq UIr9086jNmdM7OLRqzLkX7Ca7GOMiDXJl+DbNzAtNBH5gWHbjg80KQTRT2H3QR7Zjzq4xV0L K1KpY4XRBQ3rRl8fPy7BVyHHK4VpaIsYw/PqHL2CPI8r429y7qDXTInftca+5diQBzFoED7k y18yuXfCJul3YTB0EDc4T0sBAKgFDxFAje5tEHD7zp8DN2oGCR65ABM0IgEUNCoNFWF8CizC GkdxMV1/MWDkwrpiQJ4tuygAPFbNs2L+UUU282V8LrqGbRYluQSPQSqVKDZSU8pPgpT1OPZ4 +eWw3DJzCecQGNQyg+D9lEUQDE1NBIbSHAxDA4RzMBFzgY5kW7QMeZEk6TZpAf5YwnqdPc+M 1BQ3oSWjwMlFwDpLEOrkKawzKJrkzLIRwbtzbf86tWSFvcIMVt956TmbfPapku9AhtjPLOzu Smj/09y5oeFxNQL+LLS0Ieg/Z3LvJBHKwapzZlFjBpVGDhFtz+EJC/iF+qr4ViwYBVEsfCmU ZunqUY2BFeZcMA1bQARBXAIRBLFpRBBJkkIEkYZGDfFsD3Vqi9lqHd1y9MUpURBjcAFFSRTD vigBpw1W6PNd2iHvGSuwvin9VacIzEXgNy2jzPKsBYCJAx7ZEInw96Jht2KuEw1LcJ7SxzCS YpkrdbexjLUhkdiXEnfQzuez+IjaBG6fdsXXmX8UcDLXtpTYl2u4E6uHPlwwtzY0qza97t4W uXX+t6nz/8j9v6oss/M/Sc32f9Yh23D+lx0Abk6m9/+qm7z/F9z/ljVNkSR+/0/O8L8O+Z/e /5O1O93/yyiB4X/oGObq4L8U/2pVC8//qzWVnv/ItVp2/2ctwvHfwgmAqw2GXroapIA96Lx+ x06/GUCjpz3hkUsB3vSa7WM41FvNk2Zf7/agUMblJ8vJDPd81nDvKzVveNi/sOQ7aQj5Y15D ZKm2tP7JWmKLviRN208vDP/I1J7vGrhE81ZBA8vu/6gVleG/ptRkWasg/hVZy/z/tQiC3yO+ J/T0PozoDCC2gWb7A87syw8jMqJHO9Lxyzy/N4LL+JGBSwWc+p7QaOFEj14PahwewkGn3et3 0dD3WdiH8SW87jZPGt138Jv+Di05t931W7lDvMcUBMFzOvi+MCSpWqBmsYpY1ijiFzQliEpo URmmzkVMbYLKxeqKUS20x+lOnnVuz/f4kj7j2+5Hna7ePG5H2wxd/QjdpvaB3qOJF/bEEt2B L3ZLeeiTRbTToMXjfceeXlc5M7fwVlGzoYiWFoTeGqhNg/g/yK39nxWtAf/J/W/64Qf7/iPj /7XINuz/ZJ9/bk6m+z8r2vulsnT/R1Zm33/W2PqPXhfM8L8GWbz/41wRN9z4YXzAztxc4k/o fg6cuc6IcwKdOSK0HZYM3z9NCDIJ3xIaYY9uy5bQPB9kn4QyYfjv6o3DE31lZSy1/7Va7P// kCuakuF/HdKnhp5//8OMP50P4IwRJ9YXg12p8/yJeSMIu3BlweyiOIR7hvR09yFdQwLbGKHB YFouGfiOe4O5xMjtcnwd0yzMu3gGs8+OYhGh7lhwbJtCELoTm21GOxPOPBbxngqxTO+pc/uN fuH8F23bzyznpns9k0wyySSTTDLJZHPyN1G2y0YAUAAA --------------030402040109060402030009--