On Thursday, December 20, 2012, Tom Lane wrote:
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.
I played with this scenario too, but still the only way I could get it to spurn the partial index is if I rebuild the full one (to remove the bloat) but not the partial one. But still, the cost were always in the 8 to 11 range for either index with default cost settings. It is hard to imagine the amount of bloat needed to drive it up to 5000, like in his initial report before he rebuilt it.
Cheers,
Jeff