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
theplanner'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
forautovacuum to pick them up as needing maintenance, whereas temporary tables are usually queried immediately after
they'recreated 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
temporarytables. 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
arefirst loaded with data, and as they change throughout regular operation. When autovacuum is disabled, it is a good
ideato 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!