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

Richard Neill <rn214(at)richardneill(dot)org> writes:
> 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,

I think he was just recommending replacing the first and third indexes.

> it will allow me to optimally run all of the following? :
> 1. SELECT * from tbl_tracker WHERE parcel_id_code=22345 AND exit_state
> IS NULL
> 2. SELECT * from tbl_tracker where parcel_id_code=44533
> 3. SELECT * from tbl_tracker where exit_code = 2

You will need an index with exit_state as the first column to make #3
perform well --- at least, assuming that an index is going to help at
all anyway. The rule of thumb is that if a query is going to fetch
more than a few percent of a table, an index is not useful because
it's going to be touching most table pages anyway, so a seqscan will
win. I've forgotten now what you said the stats for exit_code values
other than null were, but it's quite possible that an index is useless
for #3.

These considerations are mostly covered in the manual:
http://www.postgresql.org/docs/9.2/static/indexes.html

regards, tom lane