Scott Marlowe <scott.marlowe@gmail.com> 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