On Wednesday, December 19, 2012, Richard Neill wrote:
Thanks for your help,
On 20/12/12 00:08, Sergey Konoplev wrote:
On Wed, Dec 19, 2012 at 3:49 PM, Richard Neill <rn214@richardneill.org> wrote:
* The reindex solution doesn't work. I just tried it, and the query planner
is still using the wrong indexes.
It switched to a better one of the wrong indices, though, and got several times faster.
How did it get so bloated in the first place? Is the table being updated so rapidly that the statistics might be wrong even immediately after analyze finishes?
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) ;
Cheers,
Jeff