Jeff Janes <jeff.janes@gmail.com> writes:
> In any case, I can't get it to prefer the full index in 9.1.6 at all. The
> partial index wins hands down unless the table is physically clustered by
> the parcel_id_code column. In which that case, the partial index wins by
> only a little bit.
> This is what I did for the table:
> create table tbl_tracker as select case when random()<0.001 then 2 else
> case when random()< 0.00003 then NULL else 1 end end as exit_state,
> (random()*99999)::int as parcel_id_code from generate_series(1,5000000) ;
What I did to try to duplicate Richard's situation was to create a test
table in which all the exit_state values were NULL, then build the
index, then UPDATE all but a small random fraction of the rows to 1,
then vacuum. This results in a rather bloated partial index, but I
think that's probably what he's got given that every record initially
enters the table with NULL exit_state. It would take extremely frequent
vacuuming to keep the partial index from accumulating a lot of dead
entries.
In this scenario, with 9.1, I got overly large estimates for the cost of
using the partial index; which matches up with his reports.
regards, tom lane