Re: MAX/MIN optimization via rewrite (plus query rewrites

On 11/10/2004 11:57 PM, Mark Kirkwood wrote:

> Your example and ones like :
> 
> SELECT max(foo), count(foo) FROM bar
> SELECT max(a.foo1), max(b.foo2) FROM bar1 AS a NATURAL JOIN bar2 AS b
> 
> have made me realize that the scope of "what should be optimized" is 
> somewhat subtle.
> 
> I am inclined to keep it simple (i.e rather limited) for a first cut, 
> and if that works well, then look at extending to more complex rewrites.
> 
> What do you think?

The problem is, that
    select min(foo) from bar where foo > 100;

is still solvable with an index scan, assuming there is an index on foo.

But
    select min(foo) from bar where baz = 'IT';

is only doable with an index scan if you have a compound index on (foo,baz).

Both cases can be expressed with order by + limit queries, that would 
indeed utilize those indexes. But what's been discussed so far does not 
cover any of them.


Jan

> 
> 
> Jim C. Nasby wrote:
> 
>>On Thu, Nov 11, 2004 at 11:48:49AM +1300, Mark Kirkwood wrote:
>>  
>>
>>>I am looking at implementing this TODO item. e.g. (max case):
>>>
>>>rewrite
>>>SELECT max(foo) FROM bar
>>>as
>>>SELECT foo FROM bar ORDER BY foo DESC LIMIT 1
>>>if there is an index on bar(foo)
>>>    
>>>
>> 
>>Out of curiosity, will you be doing this in such a way that 
>>
>>SELECT min(foo), max(foo) FROM bar
>>
>>will end up as
>>
>>SELECT (SELECT foo FROM bar ORDER BY foo ASC LIMIT 1), (SELECT ... DESC
>>LIMIT 1)
>>
>>?
>>  
>>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #