Re: To use a VIEW or not to use a View.....

On Wed, 22 Jan 2003, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > On Wed, 22 Jan 2003, Tom Lane wrote:
> >> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> >>> The filter is applied only to a. So, if you really wanted the
> >>> c.a=3 condition to be applied for whatever reason you're out of
> >>> luck.
> >>
> >> FWIW, CVS tip is brighter: the condition does propagate to both relations.
>
> > Yeah. I was going to ask how hard you thought it would be to do for
> > this particular sort of case. I thought about the simple case of using
> > and realized it'd probably be reasonable in amount of work, but it seems
> > I don't have to think about it. :)
>
> It could still use more eyeballs looking at it. One thing I'm concerned
> about is whether the extra (derived) conditions lead to double-counting
> restrictivity and thus underestimating the number of result rows. I
> haven't had time to really test that, but I suspect there may be a problem.

I haven't looked at code yet but tried examples like Tomasz's and some
simple ones and have gotten reasonable seeming output for the estimates
given accurate statistics (joining two estimate 3 outputs, getting 8 for
the estimated rows, joining that with another copy getting 50 some odd
where in this case the real would be 81). Not that I did a
particularly thorough test. I hope to get a chance over the next couple
of days to look and run more tests.

Tomasz, if you have the chance, you might want to try CVS and see what it
does for the queries you've been working with.