Scott Marlowe <scott.marlowe@xxxxxxxxx> writes: > Yep. Added the indexes and performance went right into the dumper. New > plan on new table with old data added in random order now looks like > the old table, only worse because it's on a slower drive. Just to be > complete here's the plan: http://explain.depesz.com/s/PYH Note that I > created new table with order by random() and created indexes. Ran > analyze on it, and the select plan looks similar now: > http://explain.depesz.com/s/bsE > So maybe I can make a test case now. But to summarize, when it can use > indexes this query gets REAL slow because it lacks a materialize step. > That seem about right? Well, the plans shown here could *not* use a materialize step because the inner scan makes use of a value from the current outer row. The materialized plan has to omit one of the index conditions from the inner scan and then apply it as a join condition. I suspect the real reason that the fast case is fast is that the inner relation, even without the p.tree_sortkey >= pro_partners.tree_sortkey condition, is empty, and thus the join runs very quickly. But the planner doesn't know that. Its estimate of the row count isn't very large, but it's definitely not zero, plus it thinks that adding the additional index condition reduces the rowcount by a factor of 3 from there. So it comes to the wrong conclusion about the value of materializing a fixed inner relation as opposed to using a parameterized indexscan. Have you tried increasing the statistics targets for these join columns? It's also possible that what you need to do is adjust the planner's cost parameters ... regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
