On Wed, Jan 25, 2012 at 11:24 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > I wrote: >> Attached is a WIP patch for parameterized paths, along the >> lines we have discussed before: ... > > I've made considerable progress on the TODO items I listed: indxpath.c > has been ripped apart and restructured, and I have it considering > parameterized paths for hash sub-joins. I made a deliberate policy > decision not to work very hard on exploring parameterized mergejoin > plans, because that seems to inflate the number of paths considered way > more than the relative usefulness of merge over hash joins justifies.
I don't fully understand this code, especially not on my first time through it, but it seems to me that the key to getting the performance of this code up to where we'd like it to be is to control the number of useless paths that get generated. Is there a guard in here against joining a parameterized path to an intermediate relation when no SJ is involved? In other words, if we're joining a parameterized path on A to a path on B, then either the join to B should satisfy at least part of the parameterization needed by A, or there should be a special join with A and B on one side and a relation that satisfies at least part of the parameterization of A on the other. In the probably not uncommon case where there are no SJs at all or all such SJs have only a single rel on the nullable side, we ought to be able to avoid creating any more paths than we do right now. Even if there are SJs with multiple rels on the outside, we could try to implement some fast check for whether intermediate paths make any sense: e.g. union the set of rels on the nullable sides of SJs. Then, if the joinrel whose paths we're trying to build isn't a subset of that set, the only thing worth considering at this level is satisfying a parameterization by building a parameter-driven nestloop: a bigger parameterized path will do us no good. Maybe there's a heuristic like this already in there; I just didn't see it on first glance. I guess I'm surprised my the amount of increase you're seeing in paths considered, though admittedly I haven't seen your test cases. If we're properly filtering out the paths that don't matter, I wouldn't have expected it to have as much impact as you're describing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers