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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
