Re: Optimizing maximum/minimum queries (yet again)

From: Tom Lane
Subject: Re: Optimizing maximum/minimum queries (yet again)
Date: ,
Msg-id: 19257.1113020796@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Optimizing maximum/minimum queries (yet again)  (Neil Conway)
Responses: Re: Optimizing maximum/minimum queries (yet again)  (Neil Conway)
List: pgsql-hackers

Tree view

Optimizing maximum/minimum queries (yet again)  (Tom Lane, )
 Re: Optimizing maximum/minimum queries (yet again)  (Bruno Wolff III, )
  Re: Optimizing maximum/minimum queries (yet again)  (Tom Lane, )
   Re: Optimizing maximum/minimum queries (yet again)  (Bruno Wolff III, )
 Re: Optimizing maximum/minimum queries (yet again)  (Bruno Wolff III, )
  Re: Optimizing maximum/minimum queries (yet again)  (Tom Lane, )
 Re: Optimizing maximum/minimum queries (yet again)  (Bruno Wolff III, )
  Re: Optimizing maximum/minimum queries (yet again)  (Tom Lane, )
 Re: Optimizing maximum/minimum queries (yet again)  (Mark Kirkwood, )
 Re: Optimizing maximum/minimum queries (yet again)  (Neil Conway, )
  Re: Optimizing maximum/minimum queries (yet again)  (Tom Lane, )
   Re: Optimizing maximum/minimum queries (yet again)  (Neil Conway, )
    Re: Optimizing maximum/minimum queries (yet again)  (Tom Lane, )
     Re: Optimizing maximum/minimum queries (yet again)  (Bruno Wolff III, )
      Re: Optimizing maximum/minimum queries (yet again)  (Tom Lane, )

Neil Conway <> writes:
> Does this transformation work for a query of the form:
>      SELECT min(x), max(y) FROM tab WHERE random() > 0.5;

I've been going back and forth on that.  We wouldn't lose a lot in the
real world if we simply abandoned the optimization attempt whenever we
find any volatile functions in WHERE.  OTOH you could also argue that
we have never guaranteed that volatile functions in WHERE would be
evaluated at every table row --- consider something likeSELECT ... WHERE x > 42 AND random() > 0.5;
All that this optimization might do is to further cut the fraction of
table rows at which the volatile function actually gets checked.  So
I'm not seeing that it would break any code that worked reliably before.

Still, if it makes you feel at all uncomfortable, we can just refuse
the optimization in such cases.
        regards, tom lane



pgsql-hackers by date:

From: Tom Lane
Date:
Subject: Re: Optimizing maximum/minimum queries (yet again)
From: Tom Lane
Date:
Subject: Re: [GENERAL] table and column information from cursor?