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