autovacuum and temp tables support

Hi there,

our client complained about slow query, which involves temporary tables.
Analyzing them manually solved the problem. I don't remember arguments
against temporary tables support by autovacuum. I'd appreciate any
pointers.

Also, it's worth to add autovacuum_enable_temp_tables variable to control
autovacuum behaviour ?

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Oleg Bartunov wrote:

> our client complained about slow query, which involves temporary tables.
> Analyzing them manually solved the problem. I don't remember
> arguments against temporary tables support by autovacuum. I'd
> appreciate any
> pointers.

Autovacuum can't process temp tables; they could reside in a backend's
private temp buffers (local memory, not shared).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Thu, Apr 8, 2010 at 2:53 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Oleg Bartunov wrote:
>
>> our client complained about slow query, which involves temporary tables.
>> Analyzing them manually solved the problem. I don't remember
>> arguments against temporary tables support by autovacuum. I'd
>> appreciate any
>> pointers.
>
> Autovacuum can't process temp tables; they could reside in a backend's
> private temp buffers (local memory, not shared).

On general thought I've had is that it would be nice if the first
attempt to SELECT against a table with no statistics would trigger an
automatic ANALYZE by the backend on which the query was executed.
It's pretty common to populate a table using INSERT, or CTAS, or COPY
and then try to immediately run a query against it, and I've often
found that it's necessary to insert manual analyze statements in there
to get decent query plans.

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Apr 8, 2010 at 2:53 PM, Alvaro Herrera

>> Autovacuum can't process temp tables; they could reside in a
>> backend's private temp buffers (local memory, not shared).
>
> it would be nice if the first attempt to SELECT against a table
> with no statistics would trigger an automatic ANALYZE by the
> backend on which the query was executed.

+1 as an RFE

-Kevin

On Thu, 8 Apr 2010, Robert Haas wrote:

> On Thu, Apr 8, 2010 at 2:53 PM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
>> Oleg Bartunov wrote:
>>
>>> our client complained about slow query, which involves temporary tables.
>>> Analyzing them manually solved the problem. I don't remember
>>> arguments against temporary tables support by autovacuum. I'd
>>> appreciate any
>>> pointers.
>>
>> Autovacuum can't process temp tables; they could reside in a backend's
>> private temp buffers (local memory, not shared).
>
> On general thought I've had is that it would be nice if the first
> attempt to SELECT against a table with no statistics would trigger an
> automatic ANALYZE by the backend on which the query was executed.
> It's pretty common to populate a table using INSERT, or CTAS, or COPY
> and then try to immediately run a query against it, and I've often
> found that it's necessary to insert manual analyze statements in there
> to get decent query plans.

Oracle does this. So, is't worth to add support (configurable, like
Oracle's optimizer_dynamic_sampling) ?

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On Thu, Apr 8, 2010 at 3:22 PM, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:
>> On general thought I've had is that it would be nice if the first
>> attempt to SELECT against a table with no statistics would trigger an
>> automatic ANALYZE by the backend on which the query was executed.
>> It's pretty common to populate a table using INSERT, or CTAS, or COPY
>> and then try to immediately run a query against it, and I've often
>> found that it's necessary to insert manual analyze statements in there
>> to get decent query plans.
>
> Oracle does this. So, is't worth to add support (configurable, like
> Oracle's optimizer_dynamic_sampling) ?

Well, dynamic sampling is considerably more complicated than what I
proposed, which is just to force an ordinary ANALYZE before the first
query against the table. It would be a very powerful feature if we
could use it to ameliorate, for example, the gross statistical errors
that sometimes occur when multiple, correlated filter conditions are
applied to the same base table; but I don't think it's in the direct
path of solving the present complaint.

...Robert

Robert Haas wrote:
> On Thu, Apr 8, 2010 at 3:22 PM, Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:
> >> On general thought I've had is that it would be nice if the first
> >> attempt to SELECT against a table with no statistics would trigger an
> >> automatic ANALYZE by the backend on which the query was executed.
> >> It's pretty common to populate a table using INSERT, or CTAS, or COPY
> >> and then try to immediately run a query against it, and I've often
> >> found that it's necessary to insert manual analyze statements in there
> >> to get decent query plans.
> >
> > Oracle does this. So, is't worth to add support (configurable, like
> > Oracle's optimizer_dynamic_sampling) ?
>
> Well, dynamic sampling is considerably more complicated than what I
> proposed, which is just to force an ordinary ANALYZE before the first
> query against the table. It would be a very powerful feature if we
> could use it to ameliorate, for example, the gross statistical errors
> that sometimes occur when multiple, correlated filter conditions are
> applied to the same base table; but I don't think it's in the direct
> path of solving the present complaint.

I have added this TODO:

Consider analyzing temporary tables when they are first used in a query

Autovacuum cannot analyze or vacuum temporary tables.

* http://archives.postgresql.org/pgsql-hackers/2010-04/msg00416.php

I have also applied the following documentation patch to document this
behavior.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com