On Sun, Dec 20, 2009 at 11:26 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Greg Stark <gsstark@xxxxxxx> writes: >> In the original poster's plan that isn't an issue. We could scan the >> index, perform the joins and restriction clauses, and only check the >> visibility on the resulting tuples which slip through them all. That >> would be possible even without crash-safe visibility bits. > > Yeah, this was floated years ago as being a potentially interesting > approach when all the join-condition fields are indexed. You end up > never having to fetch rows that don't pass the join. > > It certainly seems reasonably straightforward on the executor side. > As Greg said, the hard part is planning it sanely. Yeah, but that seems REALLY hard. First, there's the difficulty of actually generating all the paths and costing them appropriately. A plan to perform an index scan but defer the heap fetches until later has a hidden cost associated with it: the heap fetches will cost something, but we don't know how much until we get the row estimate for the node where we choose to implement them. Without knowing that cost, it's hard to be confident in discarding other plans that are apparently more expensive. That's probably solvable by adopting a more sophisticated method for comparing costs, but that gets you to the second problem, which is doing all of this with reasonable performance. You're going to have a lot more paths than we do now, and there will be many queries for which there are only trivial cost differences between them (like any query where most or all of the joins have a selectivity of exactly 1.0, which is a very common case for me). ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
