Message-ID: <50DC7ABA.5010109@richardneill.org>
Date: Thu, 27 Dec 2012 16:43:38 +0000
From: Richard Neill <rn214@richardneill.org>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:16.0) Gecko/20121010 Thunderbird/16.0.1
MIME-Version: 1.0
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Why does the query planner use two full indexes, when
	a dedicated partial index exists?
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>
In-Reply-To: 
 <CAMkU=1wGV4KZnDbcqdELyaxE4sMvza05KurjA0VH7m9cTn5J_A@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance