Message-ID: <50D2A8BA.2070801@richardneill.org>
Date: Thu, 20 Dec 2012 05:57:14 +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
CC: "pgsql-performance@postgresql.org" <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>
	<CAL_0b1s6_YZDdzt3NizwC8X+dYA37T-4aZOTzw3KJ3-iXuSjjw@mail.gmail.com>
	<50D2528E.7000800@richardneill.org>
	<CAL_0b1t5_h-VUE+sL2cfd1E78xYOq9T7P3n31iZhLT3vEa=mOA@mail.gmail.com>
	<50D25A59.3060105@richardneill.org>
	<CAMkU=1wYVjf63j+4ttZrVMmYfst7Q7ngez5e6X4s+keYbpcwdg@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1wYVjf63j+4ttZrVMmYfst7Q7ngez5e6X4s+keYbpcwdg@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

Dear Jeff,

Thanks for your help,

>             * The reindex solution doesn't work. I just tried it, and
>             the query planner
>             is still using the wrong indexes.
>
>
> It switched to a better one of the wrong indices, though, and got
> several times faster.
>

I think that this is a red herring. The switching between the two 
"wrong" indices seems to be caused by non-uniformity in the 
parcel_id_code: although it's distributed fairly well across 1-99999, 
it's not perfect.

As for the speed-up, I think that's mostly caused by the fact that 
running "Analyse" is pulling the entire table (and the relevant index) 
into RAM and flushing other things out of that cache.

> How did it get so bloated in the first place?  Is the table being
> updated so rapidly that the statistics might be wrong even immediately
> after analyze finishes?

I don't think it is. We're doing about 10 inserts and 20 updates per 
second on that table. But when I tested it, production had stopped for 
the night - so the system was quiescent between the analyse and the select.

> In any case, I can't get it to prefer the full index in 9.1.6 at all.
>   The partial index wins hands down unless the table is physically
> clustered by the parcel_id_code column.  In which that case, the partial
> index wins by only a little bit.

Interesting that you should say that... the original setup script did 
choose to cluster the table on that column.

Also, I wonder whether it matters which order the indexes are created in?


Best wishes,

Richard


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