Re: Why does the query planner use two full indexes, when a dedicated partial index exists? - Mailing list pgsql-performance

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,

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

(this is the one we've been discussing)


2.  SELECT * from tbl_tracker where parcel_id_code=44533

3.  SELECT * from tbl_tracker where exit_code = 2

(2 and 3 are examples of queries I need to run for other purposes,
unrelated to this thread, but which use the other indexes.).


Thanks,

Richard