From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Neill <rn214@richardneill.org>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
Date: Thu, 27 Dec 2012 12:05:18 -0500
Message-ID: <14671.1356627918@sss.pgh.pa.us> (raw)
In-Reply-To: <50DC7ABA.5010109@richardneill.org>
References: <50D22DE2.4090301@richardneill.org>
<28778.1355972790@sss.pgh.pa.us>
<50D3CAC4.3070800@richardneill.org>
<CAMkU=1w1yqYepDpmKo5fuL9Akkk4t_AOiwoc6u2k5ysfAEt1kQ@mail.gmail.com>
<CAMkU=1wGV4KZnDbcqdELyaxE4sMvza05KurjA0VH7m9cTn5J_A@mail.gmail.com>
<50DC7ABA.5010109@richardneill.org>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Richard Neill <rn214@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, rn214@richardneill.org
Subject: Re: Why does the query planner use two full indexes, when a dedicated partial index exists?
In-Reply-To: <14671.1356627918@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox