Re: Views With Unions

On Fri, 1 Aug 2003, Christopher Browne wrote:

> Stephan Szabo said:
> >
> >
> > What version are you using? In 7.3 and up it should be willing to
> > consider moving the clause down, unless there's something like a type
> > mismatch (because in that case it may not be equivalent without a bunch
> > more work on the clause).
>
> That was 7.2.4, although I had also tried it on 7.4 (yesterday's CVS).
>
> Which provides four findings:
>
> 1. On 7.2.4, adding additional type info just doesn't help, fitting with
> the notion that, consistent with your comment, improvement wouldn't happen
> earlier than 7.3.
>
> There's no help on 7.2 :-(, and the system I'm initially most interested
> in using this on is still on 7.2.

If you really wanted you could try going back and finding the diffs
associated with this in the CVS history or committers archives and see if
you can make equivalent changes to 7.2, but that's possibly going to be
difficult.

> 2. When I retried on 7.4, it _did_ find search paths based on Index Scan,
> when I added in additional type information. So the optimization I was
> wishing for _is_ there :-). In the longer term, that's very good news.
>
> 3. I'll have to test this out on 7.3.4, now, as I hadn't, and it sounds
> as though that is an interesting case.
>
> 4. It's often necessary to expressly specify type information in queries
> to get the optimizer to do the Right Thing.

Especially for cases like this. It takes the safer route of not pushing
things down when it's not sure if pushing down might change the semantics
(for example if a union piece has a different type from the union
output, simply pushing clauses down unchanged could change the results)

Tom would probably be willing to relax conditions if it could be proven
safe even for the wierd outlying cases with char and varchar and such.