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

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(at)richardneill(dot)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

>