Re: How to get RTREE performance from GIST index?

On 22 Nov 2009, at 13:19, Clive Page wrote:

> On 22/11/2009 12:09, Alban Hertroys wrote:
>> If you expect indexes to work efficiently on temporary tables you should analyse them after filling them to update the planner's statistics on their contents. If you don't you get the default query plan that's often not efficient.
>
> Alban
>
> Thanks - I didn't know that. I'll try removing the TEMPORARY tag.
>
> Is it documented somewhere that I should have seen?

It's not just temporary tables, it goes for all tables in fact. The difference is that with normal tables there is time for autovacuum to pick them up as needing maintenance, whereas temporary tables are usually queried immediately after they're created so that autovacuum is too late.

This specific case for using ANALYSE isn't explicitly documented, it more or less follows from the usage pattern of temporary tables. From the notes on the documentation of the ANALYZE command (http://www.postgresql.org/docs/8.4/interactive/sql-analyze.html):

"In the default PostgreSQL configuration, The Autovacuum Daemon takes care of automatic analyzing of tables when they are first loaded with data, and as they change throughout regular operation. When autovacuum is disabled, it is a good idea to run ANALYZE periodically, or just after making major changes in the contents of a table."

That last line isn't explicit about temporary tables, but the reason for running ANALYZE in both cases is the same.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b092e5911731012678321!