From: Tom Lane <tgl@sss.pgh.pa.us>
To: Strange, John W <john.w.strange@jpmorgan.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Trying to understand Stats/Query planner issue
Date: Sun, 13 Nov 2011 11:17:22 -0500
Message-ID: <6469.1321201042@sss.pgh.pa.us> (raw)
In-Reply-To: <D86EB8F058615F40948C622359204B3F02F63E194C@EMASC201VS01.exchad.jpmchase.net>
References: <D86EB8F058615F40948C622359204B3F02F63E194C@EMASC201VS01.exchad.jpmchase.net>
"Strange, John W" <john.w.strange@jpmorgan.com> writes:
> I have a question on how the analyzer works in this type of scenario.
> We calculate some results and COPY INTO some partitioned tables, which we use some selects to aggregate the data back out into reports. Everyone once in a while the aggregation step picks a bad plan due to stats on the tables that were just populated. Updating the stats and rerunning the query seems to solve the problem, this only happens if we enable nested loop query plans.
Well, even if auto-analyze launches instantly after you commit the
insertions (which it won't), it's going to take time to scan the table
and then commit the updates to pg_statistic. So there is always going
to be some window where queries will get planned with obsolete
information. If you're inserting enough data to materially change the
statistics of a table, and you need to query that table right away,
doing a manual ANALYZE rather than waiting for auto-analyze is
recommended.
> The other option is just to analyze each table involved in the query after the insert, but that seems a bit counterproductive.
Why would you think that? This type of scenario is exactly why ANALYZE
isn't deprecated as a user command.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, john.w.strange@jpmorgan.com
Subject: Re: Trying to understand Stats/Query planner issue
In-Reply-To: <6469.1321201042@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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