pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAuto-ANALYZE?
2+ messages / 2 participants
[nested] [flat]
* Auto-ANALYZE?
@ 2007-05-23 23:46 Craig James <craig_james@emolecules.com>
0 siblings, 1 reply; 2+ messages in thread
From: Craig James @ 2007-05-23 23:46 UTC (permalink / raw)
To: pgsql-performance
Auto-vacuum has made Postgres a much more "friendly" system. Is there some reason the planner can't also auto-ANALYZE in some situations?
Here's an example I ran into:
create table my_tmp_table (...);
insert into my_tmp_table (select some stuff from here and there);
select ... from my_tmp_table join another_table on (...);
The last statement generated a horrible plan, because the planner had no idea what was in the temporary table (which only had about 100 rows in it). Simply inserting an ANALYZE before the SELECT improved performance by a factor of 100 or so.
There are several situations where you could automatically analyze the data.
1. Any time you have to do a full table scan, you might as well throw in an ANALYZE of the data you're scanning. If I understand things, ANALYZE takes a random sample anyway, so a full table scan should be able to produce even better statistics than a normal ANALYZE.
2. If you have a table with NO statistics, the chances of generating a sensible plan are pretty random. Since ANALYZE is quite fast, if the planner encounters no statistics, why not ANALYZE it on the spot? (This might need to be a configurable feature, though.)
3. A user-configurable update threshold, such as, "When 75% of the rows have changed since the last ANALYZE, trigger an auto-analyze." The user-configurable part would account for the fact that some tables stats don't change much even after many updates, but others may need to be reanalyzed after a modest number of updates.
Auto-vacuum, combined with auto-analyze, would eliminate many of the problems that plague neophyte (and sometimes experienced) users of Postgres. A substantial percentage of the questions to this list are answered with, "Have you ANALYZED?"
Craig
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Auto-ANALYZE?
@ 2007-05-23 23:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Craig James <craig_james@emolecules.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2007-05-23 23:55 UTC (permalink / raw)
To: Craig James <craig_james@emolecules.com>; +Cc: pgsql-performance
Craig James <craig_james@emolecules.com> writes:
> Auto-vacuum has made Postgres a much more "friendly" system. Is there some reason the planner can't also auto-ANALYZE in some situations?
autovacuum handles analyze too. Trying to make the planner do it is
a crummy idea for a couple of reasons:
* unpredictable performance if queries sometimes go off for a few
seconds to collect stats
* pg_statistics update requires semi-exclusive lock
* work is lost if transaction later rolls back
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-05-23 23:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-23 23:46 Auto-ANALYZE? Craig James <craig_james@emolecules.com>
2007-05-23 23:55 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox