>>> On Tue, Feb 28, 2006 at 11:05 am, in message <16076.1141146348@xxxxxxxxxxxxx>, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > > 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. Yet when the index exists, the query is optimized well. > 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. That depends. If the planner was able to generate hypothetical index descriptions which might be useful, and analyze everything based on those (adding in creation cost, of course) -- why would it not be able to come up with the plan which it DID use when the index existed. > 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. <digression> I'm all for that. So far, we've been going after the low-hanging fruit in our use of PostgreSQL. When we get to the main applications, we're going to be dealing with a lot more in the way of EXISTS clauses. The product we're moving from usually optimized an IN test the same as the logically equivalent EXISTS test, and where a difference existed, it almost always did better with the EXISTS -- so we encouraged application programmers to use that form. Also, EXISTS works in situations where you need to compare on multiple columns, so it is useful in many situations where EXISTS or MIN/MAX techniques just don't work. </digression> If fixing this would allow hash or merge techniques to cover this as well as the index did, and that is true in a more general sense (not just for this one example), then temporary indexes would clearly not have any value. -Kevin
