Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 88F31B5DBDC for ; Wed, 24 Aug 2011 00:49:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 24116-03 for ; Wed, 24 Aug 2011 03:49:03 +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 689D2B5DBCE for ; Wed, 24 Aug 2011 00:49:02 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mail.catalyst.net.nz (Postfix) with ESMTP id E5D4132524; Wed, 24 Aug 2011 15:48:58 +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 7-GOAYbiIbcB; Wed, 24 Aug 2011 15:48:57 +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 1484932468; Wed, 24 Aug 2011 15:48:57 +1200 (NZST) Message-ID: <4E5474A8.8040101@catalyst.net.nz> Date: Wed, 24 Aug 2011 15:48:56 +1200 From: Mark Kirkwood User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11 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: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.405 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RP_MATCHES_RCVD=-0.505 X-Spam-Level: X-Archive-Number: 201108/258 X-Sequence-Number: 44705 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 t= o >> 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=3Dpostgresql.git&a=3Dcommitdiff&h=3D= 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= ). > Right that will be the case - audit_id is primary key for audit_log.=20 Stats entries for the join columns look like: =3D# SELECT tablename=20 ,attname,n_distinct,most_common_vals,most_common_freqs,histogram_bounds=20 FROM pg_stats WHERE tablename IN ('correspondence','audit_log') AND=20 attname IN ('audit_id','generated_audit_id'); -[ RECORD 1=20 ]-----+------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= --------------------------------------------------------- tablename | correspondence attname | generated_audit_id n_distinct | 4625 most_common_vals |=20 {11983812,15865407,4865496,717803,842478,725709,7255002,2389608,4604147,9= 996442,8693810,4604145,5916872,2389606,3135764,3307895,10527855,7254994,8= 959356,9595632,6279892,9595640,2604937,5916870,6279950,1180586,2604768,11= 80638,11526036,4451499,5252795,6279919,6279955,8958886,2604929,6279904,75= 43722,8959031,2604804,7543823,8958930,8959226,1180650,2604871,3530205,627= 9960,11051216,11051224,3530140,7838365,15060203,1180309,1180423,3530177,7= 543749,7543790,8959026,8959083,12834024,1180447,1180632,1180664,2604779,2= 604901,2604943,6279944,6280027,7543820,8958992,8959011,3530107,6279923,75= 43085,15866296,1180470,1180473,2604846,2604874,2604892,6279977,6280046,75= 43496,8958904,8958914,1180281,1180497,2604801,2604973,3529965,6280051,754= 3654,7543667,7543815,2604840,2604852,2604877,6279947,6279991,6280016,6280= 095} most_common_freqs |=20 {0.0787667,0.0769333,0.00906667,0.00886667,0.00826667,0.00593333,0.003266= 67,0.003,0.00293333,0.0027,0.00266667,0.00263333,0.00256667,0.0025,0.0024= 6667,0.00203333,0.00203333,0.00196667,0.0019,0.00186667,0.00183333,0.0018= ,0.00173333,0.00173333,0.00173333,0.0017,0.0017,0.00166667,0.00166667,0.0= 0163333,0.00163333,0.00163333,0.00163333,0.00163333,0.0016,0.0016,0.0016,= 0.0016,0.00156667,0.00156667,0.00156667,0.00156667,0.00153333,0.00153333,= 0.00153333,0.00153333,0.00153333,0.00153333,0.0015,0.0015,0.0015,0.001466= 67,0.00146667,0.00146667,0.00146667,0.00146667,0.00146667,0.00146667,0.00= 146667,0.00143333,0.00143333,0.00143333,0.00143333,0.00143333,0.00143333,= 0.00143333,0.00143333,0.00143333,0.00143333,0.00143333,0.0014,0.0014,0.00= 14,0.0014,0.00136667,0.00136667,0.00136667,0.00136667,0.00136667,0.001366= 67,0.00136667,0.00136667,0.00136667,0.00136667,0.00133333,0.00133333,0.00= 133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,= 0.0013,0.0013,0.0013,0.0013,0.0013,0.0013,0.0013} histogram_bounds |=20 {-614,149124,436276,734992,1111802,1180324,1180449,1180481,1180507,118061= 0,1180640,1180656,1180672,1475625,1671884,1882852,2257454,2521497,2604750= ,2604785,2604821,2604857,2604895,2604923,2604957,2683740,3050195,3264561,= 3529673,3529821,3529894,3530041,3530072,3530093,3530125,3530151,3530181,3= 530216,3655474,3947599,4230064,4451407,4451648,4604143,4899541,5229325,54= 42183,5783894,6044973,6279792,6279830,6279872,6279934,6279988,6280024,628= 0057,6280087,6448106,6666623,6935161,7223774,7543005,7543220,7543548,7543= 678,7543706,7543733,7543763,7543785,7543831,7730234,8168222,8473126,87049= 50,8958785,8958894,8958920,8958946,8958981,8959021,8959054,8960124,896342= 7,9092223,9393810,9649295,9915513,10116459,10340456,10533434,10908764,114= 74630,12282455,13428124,14054953,14755339,15060207,15769093,16442810,1707= 1416,17860068} -[ RECORD 2=20 ]-----+------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= --------------------------------------------------------- tablename | audit_log attname | audit_id n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds |=20 {-899,172915,346206,520991,707646,900140,1090647,1274076,1455922,1631357,= 1802760,1992032,2160450,2341946,2514505,2670638,2851069,3031271,3190297,3= 359936,3536716,3706348,3899491,4067528,4232343,4405734,4574480,4753591,49= 30502,5122384,5287148,5460009,5657326,5824340,6020883,6214608,6409401,660= 6366,6779433,6945221,7123123,7294108,7495488,7649303,7816323,7997936,8191= 973,8362771,8526974,8733309,8911487,9099916,9289773,9472155,9661398,98259= 69,10004845,10176201,10351232,10527642,10680265,10853519,11040326,1122965= 0,11422181,11605451,11806172,11985734,12171654,12364324,12559368,12729402= ,12912927,13073102,13287145,13455458,13649471,13826738,14004258,14187125,= 14356543,14539334,14715631,14895857,15060855,15231913,15404735,15577098,1= 5742060,15901413,16088450,16270629,16458319,16650444,16826581,17003138,17= 158176,17315993,17497551,17687046,17879372} Cheers Mark