Re: Views With Unions - Mailing list pgsql-performance

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> 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.

Somehow I don't think that'll fly; I have taken a brief look at some
of the optimizer code, and I very much don't want to leap into that at
the moment.  (I don't imagine I'd be able to muster much enthusiasm
for the idea from others that are involved, either.  More likely, I'm
understating the probable opposition to the idea...  :-))

I was hoping there would be some help on 7.2, but can live without it.
This approach to improving log purgeability is NOT the sort of thing
that you deploy on a day's notice because it seems like a "neat idea."
If it waits a couple months to be implemented, that's doubtless OK.

>> 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.

It turns out nicely on 7.3.4, using index scans for the subqueries in
the query:

   select count(*) from log_table where event_date between
         '2003-04-01' and '2003-05-01';

Which is a Good Thing.

>> 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.

Evidently the dates of the form '2003-04-01' and such are getting
types promoted properly enough.  I don't see anything to "lobby" for
at this point.

The DOMAIN case I mentioned the other day had something odd going on
that LOST the type information associated with the domain.  Albeit
that was on 7.3, whereas the changes in DOMAIN functionality that make
them meaningfully useful come in 7.4...
--
let name="cbbrowne" and tld="libertyrms.info" in name ^ "@" ^ tld;;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)