Thread: Excessive planner time for some queries with high statistics
I'm seeing some queries, possibly to do with using a UNIQUE index, that have fast time reported by EXPLAIN ANALYZE but the actual time as reported by \timing at 150ms+ higher. PostgreSQL 8.4.9 Simple example queries: http://paste.ubuntu.com/726131/ Table definitions: http://paste.ubuntu.com/726193/ Rewriting the query to use common table expressions worked around the original problem: http://paste.ubuntu.com/726141/ ('fixed' version of the original more complex query). We also found this problem did not occur on one of our staging systems, which had a default statistics target of 100. Lowering the statistics on the relavant columns from 1000 to 100 and reanalyzing made the overhead unnoticeable. Thoughts on IRC was this might be a regression in 8.4.9, but I haven't got earlier versions to test with at the moment. I was asked to obtain some traces but have not been able to organize getting tools on a suitable server yet. -- Stuart Bishop <stuart@stuartbishop.net> http://www.stuartbishop.net/
Stuart Bishop <stuart@stuartbishop.net> writes:
> We also found this problem did not occur on one of our staging
> systems, which had a default statistics target of 100. Lowering the
> statistics on the relavant columns from 1000 to 100 and reanalyzing
> made the overhead unnoticeable.
eqjoinsel() is O(N^2) in the number of entries in the MCV lists.
I wouldn't expect this to be an issue unless comparison is pretty
expensive, but maybe those are string not integer columns?
> Thoughts on IRC was this might be a regression in 8.4.9, but I haven't
> got earlier versions to test with at the moment.
eqjoinsel has worked like that for many years. Are you claiming you
didn't see this behavior in a prior release? If so, which one?
regards, tom lane
On Sat, Nov 5, 2011 at 1:26 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Stuart Bishop <stuart@stuartbishop.net> writes: >> We also found this problem did not occur on one of our staging >> systems, which had a default statistics target of 100. Lowering the >> statistics on the relavant columns from 1000 to 100 and reanalyzing >> made the overhead unnoticeable. > > eqjoinsel() is O(N^2) in the number of entries in the MCV lists. > I wouldn't expect this to be an issue unless comparison is pretty > expensive, but maybe those are string not integer columns? No, per http://paste.ubuntu.com/726193/ there are only integer columns being used. >> Thoughts on IRC was this might be a regression in 8.4.9, but I haven't >> got earlier versions to test with at the moment. > > eqjoinsel has worked like that for many years. Are you claiming you > didn't see this behavior in a prior release? If so, which one? I'm repeating speculation from discussions on IRC with pg devs in my timezone. I haven't tested with earlier versions. I can't confirm if this problem appeared with 8.4.9 or not - it is only recently that our query times got to the stage where we can start worrying about milliseconds instead of seconds :-) Previously, we have never noticed the planner overhead so had set default_statistics_target to 1000 to minimize the chance of bad plans due to a skewed sample. 150ms+ seems excessive though in this simple case. -- Stuart Bishop <stuart@stuartbishop.net> http://www.stuartbishop.net/