Re: Query planner making bad decisions

From: Cory Coager <ccoager(at)davisvision(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Query planner making bad decisions
Date: 2009-05-12 12:05:19
Message-ID: 16144_1242129919_4A0965FF_16144_51_1_4A0965FF.2040003@handel.davisvision.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Tom Lane said the following on 05/11/2009 07:02 PM:
> where we're off by a factor of 1500+ :-(
>
> I think most likely the ~~ operator is the biggest problem.
> Unfortunately 8.1's estimator for ~~ is not terribly bright. You could
> try increasing your statistics target but I don't think it will help
> much. Is there any chance of updating to 8.2 or later? 8.2 can do
> significantly better on this type of estimate as long as it has enough
> stats.
>
> In any case I'd suggest raising default_statistics_target to 100 or so,
> as you seem to be running queries complex enough to need good stats.
> But I'm not sure that that will be enough to fix the problem in 8.1.
>
> regards, tom lane
>
I should have mentioned the statistics for every column are already set
to 1000. I guess we'll have to add an upgrade to the project list.
Thanks for the info.

------------------------------------------------------------------------
The information contained in this communication is intended
only for the use of the recipient(s) named above. It may
contain information that is privileged or confidential, and
may be protected by State and/or Federal Regulations. If
the reader of this message is not the intended recipient,
you are hereby notified that any dissemination,
distribution, or copying of this communication, or any of
its contents, is strictly prohibited. If you have received
this communication in error, please return it to the sender
immediately and delete the original message and any copy
of it from your computer system. If you have any questions
concerning this message, please contact the sender.
------------------------------------------------------------------------

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Dimitri 2009-05-12 12:28:50 Re: Any better plan for this query?..
Previous Message Simon Riggs 2009-05-12 11:49:41 Re: Any better plan for this query?..