On Wed, Jun 3, 2009 at 6:04 PM, Janine Sisk <janine@xxxxxxxxxx> wrote: > Ok, I will look into gathering better statistics. This is the first time > I've had a significant problem with a PG database, so this is uncharted > territory for me. > > If there is more info I could give that would help, please be more specific > about what you need and I will attempt to do so. > > Thanks! > > janine You might find it helpful to try to inline the content_item__get_latest_revision function call. I'm not sure whether that's a SQL function or what, but the planner isn't always real clever about things like that. If you can redesign things so that all the logic is in the actual query, you may get better results. 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. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
