Richard Neill <rn...@richardneill.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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
