Re: Optimizing maximum/minimum queries (yet again)

From: Mark Kirkwood
Subject: Re: Optimizing maximum/minimum queries (yet again)
Date: ,
Msg-id: 42574993.20101@paradise.net.nz
(view: Whole thread, Raw)
In response to: Optimizing maximum/minimum queries (yet again)  (Tom Lane)
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, )

Looks great! I had been slowly thinking along similar lines via the 
equivalence:

SELECT min(x) FROM tab WHERE...

SELECT min(x) FROM (SELECT x FROM tab WHERE ... ORDER BY x LIMIT 1) AS t


However, it looks like your approach is more flexible than this :-)

best wishes

Mark


Tom Lane wrote:
> 
> I realized today that this may not be as hard as I thought.
> Specifically, I'm imagining that we could convert
> 
>     SELECT min(x), max(y) FROM tab WHERE ...
> 
> into sub-selects in a one-row outer query:
> 
>     SELECT (SELECT x FROM tab WHERE ... ORDER BY x LIMIT 1),
>            (SELECT y FROM tab WHERE ... ORDER BY y DESC LIMIT 1);
> 
> Breaking it down like that means we can consider each aggregate
> independently, which definitely simplifies matters.  



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?