Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3463E2E2EB0 for ; Tue, 4 Dec 2007 12:06:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 55620-01 for ; Tue, 4 Dec 2007 12:06:24 -0400 (AST) Received: from mail1.livedatagroup.com (mail1.livedatagroup.com [216.154.205.166]) by postgresql.org (Postfix) with ESMTP id 40F842E3128 for ; Tue, 4 Dec 2007 12:06:14 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by mail1.livedatagroup.com (Postfix) with ESMTP id 1DCB1102CCD for ; Tue, 4 Dec 2007 11:06:09 -0500 (EST) Received: from mail1.livedatagroup.com ([192.168.3.224]) by localhost (mail1.livedatagroup.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04344-10 for ; Tue, 4 Dec 2007 11:06:08 -0500 (EST) Received: from [192.168.2.134] (gw.livedatagroup.com [205.242.255.66]) by mail1.livedatagroup.com (Postfix) with ESMTP id B5B37102CAA for ; Tue, 4 Dec 2007 11:06:06 -0500 (EST) Message-ID: <47557AED.8050005@livedatagroup.com> Date: Tue, 04 Dec 2007 11:06:05 -0500 From: Pallav Kalva User-Agent: Thunderbird 2.0.0.6 (X11/20070728) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Optimizer Not using the Right plan Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: amavisd-new at livedatagroup.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.396 tagged_above=0 required=5 tests=BAYES_00=-2.599, FB_LETTERS_21B=3.995 X-Spam-Level: * X-Archive-Number: 200712/45 X-Sequence-Number: 27861 Hi, Postgres 8.2.4 is not using the right plan for different values. From the below queries listing.addressvaluation table has 19million records , the other table listing.valuationchangeperiod is just lookup table with 3 records. If you can see the explain plans for the statements the first one uses a bad plan for 737987 addressid search, does a index scan backward on the primary key "addressvaluationid" takes more time to execute and the same query for a different addressid (5851202) uses the correct optimal plan with index scan on "addressid" column which is way quicker. Autovacuums usually vacuums these tables regularly, in fact I checked the pg_stat_user_tables the last vacuum/analyze on this table was last night. I did another manual vacuum analyze on the listing.addrevaluation table it uses the right plan for all the values now. Can anyone explain me this wierd behavior ? why does it have different plans for different values and after doing manual vacuum analyze it works properly ? Are autovacuums not effective enough ? Here are my autovacuum settings autovacuum_naptime = 120min autovacuum_vacuum_threshold = 500 autovacuum_analyze_threshold = 250 autovacuum_vacuum_scale_factor = 0.001 autovacuum_analyze_scale_factor = 0.001 autovacuum_freeze_max_age = 200000000 autovacuum_vacuum_cost_delay = -1 autovacuum_vacuum_cost_limit = -1 Here are the table structures listing.addressvaluation Table "listing.addressvaluation" Column | Type | Modifiers ----------------------------+-----------------------------+--------------------------------------------------------------------------- addressvaluationid | integer | not null default nextval(('listing.addressvaluationseq'::text)::regclass) fkaddressid | integer | not null fkaddressvaluationsourceid | integer | not null sourcereference | text | createdate | timestamp without time zone | not null default ('now'::text)::timestamp(6) without time zone valuationdate | timestamp without time zone | not null valuationamount | numeric(14,2) | valuationhigh | numeric(14,2) | valuationlow | numeric(14,2) | valuationconfidence | integer | valuationchange | numeric(14,2) | fkvaluationchangeperiodid | integer | historycharturl | text | regionhistorycharturl | text | Indexes: "pk_addressvaluation_addressvaluationid" PRIMARY KEY, btree (addressvaluationid), tablespace "indexdata" "idx_addressvaluation_createdate" btree (createdate), tablespace "indexdata" "idx_addressvaluation_fkaddressid" btree (fkaddressid), tablespace "indexdata" "idx_addressvaluation_fkaddressid2" btree (fkaddressid), tablespace "indexdata" Foreign-key constraints: "fk_addressvaluation_address" FOREIGN KEY (fkaddressid) REFERENCES listing.address(addressid) "fk_addressvaluation_addressvaluationsource" FOREIGN KEY (fkaddressvaluationsourceid) REFERENCES listing.addressvaluationsource(addressvaluationsourceid) "fk_addressvaluation_valuationchangeperiod" FOREIGN KEY (fkvaluationchangeperiodid) REFERENCES listing.valuationchangeperiod(valuationchangeperiodid) listing.valuationchangeperiod Table "listing.valuationchangeperiod" Column | Type | Modifiers -------------------------+---------+-------------------------------------------------------------------------------- valuationchangeperiodid | integer | not null default nextval(('listing.valuationchangeperiodseq'::text)::regclass) name | text | not null Indexes: "pk_valuationchangeperiod_valuationchangeperiodid" PRIMARY KEY, btree (valuationchangeperiodid), tablespace "indexdata" "uq_valuationchangeperiod_name" UNIQUE, btree (name), tablespace "indexdata" For Addressid 737987 after autovacuum before manual vacuum analyze ------------------------------------------------------------------------------------------- explain select this_.addressvaluationid as addressv1_150_1_, this_.sourcereference as sourcere2_150_1_, this_.createdate as createdate150_1_, this_.valuationdate as valuatio4_150_1_, this_.valuationamount as valuatio5_150_1_, this_.valuationhigh as valuatio6_150_1_, this_.valuationlow as valuatio7_150_1_, this_.valuationconfidence as valuatio8_150_1_, this_.valuationchange as valuatio9_150_1_, this_.historycharturl as history10_150_1_, this_.regionhistorycharturl as regionh11_150_1_, this_.fkaddressid as fkaddre12_150_1_, this_.fkaddressvaluationsourceid as fkaddre13_150_1_, this_.fkvaluationchangeperiodid as fkvalua14_150_1_, valuationc2_.valuationchangeperiodid as valuatio1_197_0_, valuationc2_.name as name197_0_ from listing.addressvaluation this_ left outer join listing.valuationchangeperiod valuationc2_ on this_.fkvaluationchangeperiodid=valuationc2_.valuationchangeperiodid where this_.fkaddressid=737987 order by this_.addressvaluationid desc limit 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..678.21 rows=1 width=494) -> Nested Loop Left Join (cost=0.00..883026.09 rows=1302 width=494) -> Index Scan Backward using pk_addressvaluation_addressvaluationid on addressvaluation this_ (cost=0.00..882649.43 rows=1302 width=482) Filter: (fkaddressid = 737987) -> Index Scan using pk_valuationchangeperiod_valuationchangeperiodid on valuationchangeperiod valuationc2_ (cost=0.00..0.28 rows=1 width=12) Index Cond: (this_.fkvaluationchangeperiodid = valuationc2_.valuationchangeperiodid) (6 rows) For Addressid 5851202 after autovacuum before manual vacuum analyze -------------------------------------------------------------------------------------------- select this_.addressvaluationid as addressv1_150_1_, this_.sourcereference as sourcere2_150_1_, this_.createdate as createdate150_1_, this_.valuationdate as valuatio4_150_1_, this_.valuationamount as valuatio5_150_1_, this_.valuationhigh as valuatio6_150_1_, this_.valuationlow as valuatio7_150_1_, this_.valuationconfidence as valuatio8_150_1_, this_.valuationchange as valuatio9_150_1_, this_.historycharturl as history10_150_1_, this_.regionhistorycharturl as regionh11_150_1_, this_.fkaddressid as fkaddre12_150_1_, this_.fkaddressvaluationsourceid as fkaddre13_150_1_, this_.fkvaluationchangeperiodid as fkvalua14_150_1_, valuationc2_.valuationchangeperiodid as valuatio1_197_0_, valuationc2_.name as name197_0_ from listing.addressvaluation this_ left outer join listing.valuationchangeperiod valuationc2_ on this_.fkvaluationchangeperiodid=valuationc2_.valuationchangeperiodid where this_.fkaddressid=5851202 order by this_.addressvaluationid desc limit 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=30.68..30.68 rows=1 width=494) -> Sort (cost=30.68..30.71 rows=11 width=494) Sort Key: this_.addressvaluationid -> Hash Left Join (cost=1.07..30.49 rows=11 width=494) Hash Cond: (this_.fkvaluationchangeperiodid = valuationc2_.valuationchangeperiodid) -> Index Scan using idx_addressvaluation_fkaddressid2 on addressvaluation this_ (cost=0.00..29.27 rows=11 width=482) Index Cond: (fkaddressid = 5851202) -> Hash (cost=1.03..1.03 rows=3 width=12) -> Seq Scan on valuationchangeperiod valuationc2_ (cost=0.00..1.03 rows=3 width=12) (9 rows) After manual vacuum analyze for addressid 737987 ------------------------------------------------------------------ explain select this_.addressvaluationid as addressv1_150_1_, this_.sourcereference as sourcere2_150_1_, this_.createdate as createdate150_1_, this_.valuationdate as valuatio4_150_1_, this_.valuationamount as valuatio5_150_1_, this_.valuationhigh as valuatio6_150_1_, this_.valuationlow as valuatio7_150_1_, this_.valuationconfidence as valuatio8_150_1_, this_.valuationchange as valuatio9_150_1_, this_.historycharturl as history10_150_1_, this_.regionhistorycharturl as regionh11_150_1_, this_.fkaddressid as fkaddre12_150_1_, this_.fkaddressvaluationsourceid as fkaddre13_150_1_, this_.fkvaluationchangeperiodid as fkvalua14_150_1_, valuationc2_.valuationchangeperiodid as valuatio1_197_0_, valuationc2_.name as name197_0_ from listing.addressvaluation this_ inner join listing.valuationchangeperiod valuationc2_ on this_.fkvaluationchangeperiodid=valuationc2_.valuationchangeperiodid where this_.fkaddressid=737987 order by this_.addressvaluationid desc limit 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=31.24..31.24 rows=1 width=494) -> Sort (cost=31.24..31.27 rows=11 width=494) Sort Key: this_.addressvaluationid -> Hash Join (cost=1.07..31.05 rows=11 width=494) Hash Cond: (this_.fkvaluationchangeperiodid = valuationc2_.valuationchangeperiodid) -> Index Scan using idx_addressvaluation_fkaddressid on addressvaluation this_ (cost=0.00..29.83 rows=11 width=482) Index Cond: (fkaddressid = 737987) -> Hash (cost=1.03..1.03 rows=3 width=12) -> Seq Scan on valuationchangeperiod valuationc2_ (cost=0.00..1.03 rows=3 width=12) (9 rows) Thanks! Pallav.