Re: Pointers needed on optimizing slow SQL statements

[Simon Riggs <simon(at)2ndQuadrant(dot)com>]

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