agora inbox for pgus-general@postgresql.orghelp / color / mirror / Atom feed
Query runtime dependent on ANALYZE run 2+ messages / 2 participants [nested] [flat]
* Query runtime dependent on ANALYZE run @ 2012-06-06 17:59 Viktor Rosenfeld <listuser36@googlemail.com> 0 siblings, 1 reply; 2+ messages in thread From: Viktor Rosenfeld @ 2012-06-06 17:59 UTC (permalink / raw) To: pgus-general Hi, I've noticed that the selection of the executed query plan (and therefore query runtime) is dependent on the statistics generated by an ANALYZE run. As an demonstration, I chose the best runtime of 5 consecutive runs of the query linked below, regenerated the statistics for the column node_annotation.value and re-ran the query. This experiment was repeated a hundred times each for the statistics targets 10, 100 (default), 1000, 10000. I've used PostgreSQL 9.1.3. Query: http://www.informatik.hu-berlin.de/~rosenfel/analyze/query.sql Schema: http://www.informatik.hu-berlin.de/~rosenfel/analyze/schema.pdf The next table shows for each statistics target the number of distinct plans generated in the experiment, how often the most common plan was generated, the runtime (ms) of the best and worst non-unique plan, and the number of timeouts where the query did not finish within 60 seconds. | Statistics | # plans | most common plan | Best | Worst | Timeouts | |------------+---------+------------------+------+-------+----------| | 10 | 37 | 60 | 1876 | 2180 | 0 | | 100 | 90 | 4 | 2225 | 7927 | 14 | | 1000 | 75 | 6 | 2214 | 6329 | 22 | | 10000 | 6 | 85 | 2195 | 2900 | 3 | The distribution for each statistics target is linked below: Distribution: http://www.informatik.hu-berlin.de/~rosenfel/analyze/histogram.pdf As one can see, using the default value of 100 (and also 1000) there is a considerable spread in the runtime of the query. The best plan is chosen most often, but only about a quarter of the time and there are also many timeouts. The best results can be achieved with a statistics target of 10: The most stable query plan is the second best and there are no timeouts. Using a statistics target of 10000 generates the most stable plan selection, i.e. the same plan is chosen most often, but it is almost a second slower than the best plan. I would like to know how I can mitigate against these random results. Cheers, Viktor ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Query runtime dependent on ANALYZE run @ 2012-06-07 00:18 Josh Berkus <josh@agliodbs.com> parent: Viktor Rosenfeld <listuser36@googlemail.com> 0 siblings, 0 replies; 2+ messages in thread From: Josh Berkus @ 2012-06-07 00:18 UTC (permalink / raw) To: pgus-general Viktor, This is a very good analysis! However, you're not going to get much of a response here. I suggest posting it to the pgsql-performance mailing list instead. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-06-07 00:18 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2012-06-06 17:59 Query runtime dependent on ANALYZE run Viktor Rosenfeld <listuser36@googlemail.com> 2012-06-07 00:18 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox