Re: Why does the query planner use two full indexes, when a dedicated partial index exists?

Jeff Janes <jeff(dot)janes(at)gmail(dot)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