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. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
