From: | Richard Neill <rn214(at)richardneill(dot)org> |
---|---|
To: | |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Why does the query planner use two full indexes, when a dedicated partial index exists? |
Date: | 2012-12-20 05:57:14 |
Message-ID: | 50D2A8BA.2070801@richardneill.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2012-12-20 14:06:29 | Re: hash join vs nested loop join |
Previous Message | Richard Neill | 2012-12-20 05:51:57 | Re: Why does the query planner use two full indexes, when a dedicated partial index exists? |