Optimizer Not using the Right plan

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.