From: | Stuart Bishop <stuart(at)stuartbishop(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "List, Postgres" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Excessive planner time for some queries with high statistics |
Date: | 2011-11-05 02:49:26 |
Message-ID: | CADmi=6NxoO28aC04yKwxzM3gdXnzOmg43JwsHr130N7AkiGBGg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Nov 5, 2011 at 1:26 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stuart Bishop <stuart(at)stuartbishop(dot)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(at)stuartbishop(dot)net>
http://www.stuartbishop.net/
From | Date | Subject | |
---|---|---|---|
Next Message | Brandon Phelps | 2011-11-05 04:34:19 | Function Question - Inserting into a table with foreign constraints |
Previous Message | Adrian Klaver | 2011-11-05 02:25:06 | Re: Strange problem with create table as select * from table; |