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