Re: Limiting the number of parameterized indexpaths created

On 30 October 2012 21:57, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> So what I'm proposing instead, which is implemented in the other half of
> the attached patch, is that we simply put an arbitrary limit on how many
> outer-relation sets we'll consider while generating parameterized
> indexscans. As written, the patch limits the number of relid sets to 10
> times the number of join clauses it's working with, which is small
> enough to keep the runtime of this test case to something reasonable.
> I think that in practice this will still allow useful
> combination-outer-rel cases to be found. I wonder though if anyone has
> a better idea?

That seems like a useful limit, but it is arbitrary and not
controllable by user.

An example like that is actually fairly common when you put back
together a deep class hierarchy with one table per class. I'm a little
surprised the whole thing doesn't collapse anyway, when using
constants as shown since aid = 2 AND aid =3 will be no rows. But I
guess that's no really important.

We should be able to do a better job of recognising some other
aspect/symmetry of this situation and then simplifying the problem
from that. Calculating all paths treats the problem as a complete
unknown and we must be able to do better than that. If we look at the
problem from the perspective of how we would handle it if one of the
tables was very large, how would that change things? Can we use a
greedy algorithm starting with largest table?

This is hand-waving, but it is useful to discuss these things. I'd be
happy if you could give a fuller explanation of this issue so we can
all learn.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services