From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | tjo(at)acm(dot)org, pgsql-general(at)postgresql(dot)org |
Subject: | Re: efficiency of group by 1 order by 1 |
Date: | 2006-03-17 21:05:24 |
Message-ID: | b42b73150603171305h12b6e292m7926d8ad90f907b7@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 3/17/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Merlin Moncure" <mmoncure(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Rushabh Doshi | 2006-03-17 22:23:34 | db sever seems to be dropping connections |
Previous Message | SunWuKung | 2006-03-17 21:00:55 | Re: pgsql variables from records |