Thread: efficiency of group by 1 order by 1

 >> Is there a way to eliminate the ugly repeated use of
 >> date_trunc('day',endtime)?


 >>In this particular case you could say
 >>
 >>    ... GROUP BY 1 ORDER BY 1;

I use a similar SQL, e.g.:
  select func(x)....group by func(x) order by func(x)
but my func is rather expensive.  Is func(x) evaluated three times
in the above statement?  Would it be evaluated only once if I used
  select func(x)....group by 1 order by 1

TJ O'Donnell
www.gnova.com

> I use a similar SQL, e.g.:
>   select func(x)....group by func(x) order by func(x)
> but my func is rather expensive.  Is func(x) evaluated three times
> in the above statement?  Would it be evaluated only once if I used
>   select func(x)....group by 1 order by 1

try:

select q.v from (select func(t.x) as v from t) q order group by 1 order by 1;

Is your function immutable?  You have to be very careful with
expensive functions in the select clause.  for example

select f(x) from t where id = 1 order by n;

can cause f to execute for the entire table even if id is unique.
Solution is to subquery as in the above.

merlin

"Merlin Moncure" <mmoncure@gmail.com> writes:
> select f(x) from t where id = 1 order by n;
> can cause f to execute for the entire table even if id is unique.

Really?  I'd consider it a bug if so.  Compare

    select 1/x from t where x > 0

If the presence of zeroes in t can make this throw a zero-divide error,
the database is broken.  In my mind the SQL spec is perfectly clear that
WHERE filtering occurs before evaluation of the SELECT targetlist.
(Sorting, however, occurs afterward --- so there are certainly potential
gotchas of this ilk.  But the specific example you give is bogus.)

            regards, tom lane

On 3/17/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Merlin Moncure" <mmoncure@gmail.com> writes:
> > select f(x) from t where id = 1 order by n;
> > can cause f to execute for the entire table even if id is unique.
>
> Really?  I'd consider it a bug if so.  Compare
>
>         select 1/x from t where x > 0
>
> If the presence of zeroes in t can make this throw a zero-divide error,
> the database is broken.  In my mind the SQL spec is perfectly clear that
> WHERE filtering occurs before evaluation of the SELECT targetlist.
> (Sorting, however, occurs afterward --- so there are certainly potential
> gotchas of this ilk.  But the specific example you give is bogus.)

You are quite right..I didn't state the problem properly.  The
particular one that burned me was actually:

select f(x) from t where k order by y limit 1;

...which may or may not execute f(x) more than once depending on how
the planner implements order by y...the limit clause does not
necessarily guard against this, but a where clause does provide a
guarantee.

for posterity, the fix was:
select f(q.x) from (select x from t where k order by y limit 1) q;

if you will recall the f(x) in my case was a user_lock function and
the results were not pleasant :-)  So out of habit I tend to separate
the extration from the function execution via subquery.

Merlin