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

On Thursday, December 27, 2012, Richard Neill wrote:

>
>
> On 27/12/12 16:17, Jeff Janes wrote:
>
>>
>> I still think your best bet is to get rid of the partial index and trade
>> the full one on (parcel_id_code) for one on (parcel_id_code,exit_state).
>> I think that will be much less fragile than reindexing in a cron job.
>>
>>
> So, at the moment, I have 3 indexes:
> full: parcel_id_code
> full: exit_state
> full: parcel_id_code where exit state is null
>
> Am I right that when you suggest just a single, joint index
> (parcel_id_code,exit_state)
> instead of all 3 of the others,
>

No, just instead of 1 and 3. You still need an index on (exit_state) in
order to efficiently satisfy query 3 below.

Alternative, you could keep index 1, and replace 2 and 3 with one on
(exit_state, parcel_id_code). And in fact this might be the better way to
go, because a big problem you are facing is that the (exit_state) index is
looking falsely attractive, and the easiest way to overcome that is to get
rid of that index and replace it with one that can do everything that it
can do, but more.

Theoretically there is technique called "loose scan" or "skip scan" which
could allow you to make one index, (exit_state, parcel_id_code) to replace
all 3 of the above, but postgres does not yet implement that technique. I
think there is a way to achieve the same thing using recursive sql. But I
doubt it would be worth it, as too much index maintenance is not your root
problem.

> 3. SELECT * from tbl_tracker where exit_code = 2
>

Cheers,

Jeff