Re: Pointers needed on optimizing slow SQL statements - Mailing list pgsql-performance

From Simon Riggs
Subject Re: Pointers needed on optimizing slow SQL statements
Date
Msg-id 1244278252.15799.7.camel@ebony.2ndQuadrant
Whole thread Raw
In response to Re: Pointers needed on optimizing slow SQL statements  (Robert Haas <robertmhaas@gmail.com>)
Responses Re: Pointers needed on optimizing slow SQL statements
Re: Pointers needed on optimizing slow SQL statements
List pgsql-performance
On Wed, 2009-06-03 at 21:21 -0400, Robert Haas wrote:

> But, we're not always real clever about selectivity.  Sometimes you
> have to fake the planner out, as discussed here.
>
> http://archives.postgresql.org/pgsql-performance/2009-06/msg00023.php
>
> Actually, I had to do this today on a production application.  In my
> case, the planner thought that a big OR clause was not very selective,
> so it figured it wouldn't have to scan very far through the outer side
> before it found enough rows to satisfy the LIMIT clause.  Therefore it
> materialized the inner side instead of hashing it, and when the
> selectivity estimate turned out to be wrong, it took 220 seconds to
> execute.  I added a fake join condition of the form a || b = a || b,
> where a and b were on different sides of the join, and now it hashes
> the inner side and takes < 100 ms.
>
> Fortunately, these kinds of problems are fairly rare, but they can be
> extremely frustrating to debug.  With any kind of query debugging, the
> first question to ask yourself is "Are any of my selectivity estimates
> way off?".  If the answer to that question is no, you should then ask
> "Where is all the time going in this plan?".  If the answer to the
> first question is yes, though, your time is usually better spent
> fixing that problem, because once you do, the plan will most likely
> change to something a lot better.

The Function Index solution works, but it would be much better if we
could get the planner to remember certain selectivities.

I'm thinking a command like

    ANALYZE foo [WHERE .... ]

which would specifically analyze the selectivity of the given WHERE
clause for use in queries.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


pgsql-performance by date:

Previous
From: Robert Haas
Date:
Subject: Re: degenerate performance on one server of 3
Next
From: S Arvind
Date:
Subject: Vacuum ALL FULL