I think a summary of where the discussion went might be helpful
(especially for me after a week or so away doing perl).
There were a number of approaches suggested, which I will attempt to
summarize in a hand wavy fashion - (apologies for any misrepresentation
caused):
i) Rewrite max/min que
On 15 Nov 2004 02:00:37 -0500, Greg Stark <[EMAIL PROTECTED]> wrote:
> I think people should get away from thinking about "order by + limit". That
> isn't going to work for anything with a GROUP BY. And it isn't going to work
> for anything more complex than a single min() or max().
>
> min() only
Jan Wieck <[EMAIL PROTECTED]> writes:
> 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.
I think people should get away from thinking about "order by + limit". That
isn't going to
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 simpl
On Thu, Nov 11, 2004 at 08:00:01AM -0600, Bruno Wolff III wrote:
> On Thu, Nov 11, 2004 at 17:52:19 +1100,
> John Hansen <[EMAIL PROTECTED]> wrote:
> > Why not just change the function all together to 'select $1 from $2
> > order by $1 desc limit 1;'
> >
> > Is there ANY situation where max(col)
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > Tom Lane <[EMAIL PROTECTED]> writes:
> >> Oh? How is a first() aggregate going to know what sort order you want
> >> within the group?
>
> > It would look something like
>
> > select x,first(a),first(b) from (sel
Greg Stark <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> writes:
>> Oh? How is a first() aggregate going to know what sort order you want
>> within the group?
> It would look something like
> select x,first(a),first(b) from (select x,a,b from table order by x,y) group
> by x
> whi
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > It would also make it possible to deprecate DISTINCT ON in favour of GROUP
> > BY
> > with first() calls.
>
> Oh? How is a first() aggregate going to know what sort order you want
> within the group? AFAICS first
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Re: knowing internal representation, I think this is required anyway;
> else the optimization would only work on a very limited numer of
> situations.
The point of my remark is that pushing this knowledge out to a function
is helpful only if you can put
Greg Stark <[EMAIL PROTECTED]> writes:
> It would also make it possible to deprecate DISTINCT ON in favour of GROUP BY
> with first() calls.
Oh? How is a first() aggregate going to know what sort order you want
within the group? AFAICS first() is only useful when you honestly do
not care which g
On Thu, Nov 11, 2004 at 10:24:34 -0500,
Tom Lane <[EMAIL PROTECTED]> wrote:
>
> We know how to determine that an index matches an ORDER BY clause.
> But what has an aggregate called MAX() got to do with ORDER BY? Magic
> assumptions about operators named "<" are not acceptable answers; there
>
Tom Lane <[EMAIL PROTECTED]> writes:
> As a real-world example of why I won't hold still for hard-wiring this:
> a complex-number data type might have btree opclasses allowing it to be
> sorted either by real part or by absolute value. One might then define
> max_real() and max_abs() aggregates
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote:
>> We know how to determine that an index matches an ORDER BY clause.
>> But what has an aggregate called MAX() got to do with ORDER BY?
> Wouldn't knowing an opclass and direction associated with an aggregrate
> f
On Thu, Nov 11, 2004 at 17:52:19 +1100,
John Hansen <[EMAIL PROTECTED]> wrote:
> Why not just change the function all together to 'select $1 from $2
> order by $1 desc limit 1;'
>
> Is there ANY situation where max(col) as it is, would be faster?
Yes. A couple I can think of are:
When count(col
> How are you planning to represent the association between MIN/MAX and
> particular index orderings in the system catalogs?
Don't we already have that info to decide whether an index handles
an "ORDER BY" without a sort node ?
Andreas
---(end of broadcast)-
On Thu, Nov 11, 2004 at 01:18:05 -0600,
"Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> Certainly handling only one case is better than none. I just wanted to
> bring up the multiple aggregate scenario. Also, consider that
>
> SELECT min(a), max(a), min(b), max(c) FROM table
>
> could be optimized
On Thu, Nov 11, 2004 at 01:08:39AM -0500, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > What about having a new column in pg_aggregate which would point to a
> > function that would try to optimize the aggregate's handling?
>
> I can't get very excited about this, because how wo
"Zeugswetter Andreas DAZ SD" <[EMAIL PROTECTED]> writes:
>> How are you planning to represent the association between MIN/MAX and
>> particular index orderings in the system catalogs?
> Don't we already have that info to decide whether an index handles
> an "ORDER BY" without a sort node ?
We kn
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> I don't think you should be rewriting queries as much as providing
> alternate plans and letting the rest of the optimizer decided which
> plan to use. If you just rewrite a query you might lock yourself into
> using a poor plan.
Moreover, none of th
Probably for a small table, where the machinery of reading the index,
followed by checking the table for non-visible tuples is more costly
than just scanning the table!
regards
Mark
John Hansen wrote:
Why not just change the function all together to 'select $1 from $2
order by $1 desc limit 1;'
There seems to be (as Tom indicated) a choice of approaches:
i) rewrite max/min querys and then plan 'em
ii) provide alternate plans based on presence of certain aggregate types
in the query
when I first examined this TODO item, I was really thinking about i),
but I suspect that ii) is probably
Certainly handling only one case is better than none. I just wanted to
bring up the multiple aggregate scenario. Also, consider that
SELECT min(a), max(a), min(b), max(c) FROM table
could be optimized as well (into 4 index scans, assuming a, b, and c all
had indexes).
I don't think any other agg
Why not just change the function all together to 'select $1 from $2
order by $1 desc limit 1;'
Is there ANY situation where max(col) as it is, would be faster?
... John
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> What about having a new column in pg_aggregate which would point to a
> function that would try to optimize the aggregate's handling?
I can't get very excited about this, because how would you make a
reasonably stable/narrow API for such a thing? The f
On Thu, Nov 11, 2004 at 17:57:42 +1300,
Mark Kirkwood <[EMAIL PROTECTED]> 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"
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
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 i
On Wed, Nov 10, 2004 at 22:21:31 -0300,
Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> On Wed, Nov 10, 2004 at 07:18:59PM -0500, Tom Lane wrote:
>
> > A more radical way of handling it would be to detect the relevance of an
> > indexscan in indxpath.c and generate a special kind of Path node; this
Tom Lane wrote:
A more radical way of handling it would be to detect the relevance of an
indexscan in indxpath.c and generate a special kind of Path node; this
would not generalize to other sorts of things as you were hoping, but
I'm unconvinced that the mechanism is going to be very general-purpos
On Wed, Nov 10, 2004 at 07:18:59PM -0500, Tom Lane wrote:
> A more radical way of handling it would be to detect the relevance of an
> indexscan in indxpath.c and generate a special kind of Path node; this
> would not generalize to other sorts of things as you were hoping, but
> I'm unconvinced th
Mark Kirkwood <[EMAIL PROTECTED]> writes:
> I am looking at implementing this TODO item. e.g. (max case):
> My initial thoughts revolved around extending the existing RULE system
> to be able to handle more general types of rewrite - like conditionals
> in SELECT rules and rewrites that change e
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)
Suggestions about the most suitable point in the parser/planner stage to
perform this sort of rewrite would be most welco
32 matches
Mail list logo
