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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
