Optimizer Not using the Right plan

[Pallav Kalva <pkalva(at)livedatagroup(dot)com>]

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.