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