Jeff Janes <jeff.janes@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
