"Jim C. Nasby" <jnasby@xxxxxxxxxxxxx> writes: > FWIW, Sybase supported something similar a long time ago. It had the > ability to build a temporary 'clustered table' (think index organized > table) when there was enough benefit to do so. This is actually > much easier to make happen inside a transaction for us, because we don't > need to keep visibility information around. There's probably also some > index metadata that could be done away with. Perhaps the materialize > node could be made to allow this. How does what you describe differ from a merge join? Or a hash join, if you imagine the temp index as being a hash rather than btree index? The issue at hand really has nothing to do with temp indexes, it's with the constrained way that the planner deals with EXISTS subplans. The subplans themselves are cheap enough, even in the poorly-indexed variant, that the planner would certainly never have decided to create an index to use for them. The problem only becomes apparent at the next level up, where those subplans are going to be repeated a huge number of times ---- but the subplan plan is already chosen and won't be changed. So even if we invented a temp-index facility, it would fail to be applied in Kevin's example. The limiting factor is that EXISTS subplans aren't flattened ... and once that's fixed, I doubt the example would need any new kind of join support. regards, tom lane
