Re: Pet-peevishness (When does a check constraint execute?)

On Mon, Mar 27, 2006 at 03:46:17PM +0200, Alban Hertroys wrote:
> Jim C. Nasby wrote:
> >On Fri, Mar 24, 2006 at 11:25:35AM -0700, Edmund(dot)Bacon(at)elb_lx(dot)onesystem(dot)ca
> >wrote:
> ><pet-peeve>
> >If you only want to know if something exists, do NOT use count!
> >
> >
> >>test$# (select * from test_dates t1
> >>test$# where EXISTS ( select * from test_dates t2
> >>test$# where (t1.from_date, t1.to_date) overlaps
> >>test$# (t2.from_date, t2.to_date)) )$$;
> >
> >
> >On a small dataset you may not notice much difference, but you'll
> >certainly see it on a large dataset.
> ></pet-peeve>
>
> Certainly true, I keep telling people here at work. But I was kind of
> wondering why you'd "select * from" inside "exists", if you're not going
> to use those values.
>
> I tend to write "where exists (select 1 from", but now I'm curious
> whether the planner handles this for you or if there's actually a
> difference in meaning?
>
> I assume it's always better to not let the planner decide these kind of
> things. Try to write what you really want, instead of waving hands in
> the general direction and make the planner decide what you mean ;)
>
> Well, this is getting awfully pet-peevish, of course...

It's actually highly database-dependant. Many databases know all about
EXISTS (SELECT * ...) and can optimize accordingly. Other databases will
do better with SELECT 1, and finally some will actually do worse with
SELECT 1. I actually have a book that delves into how things like this
are handled on different databases, though I don't remember the title
off-hand.

In any case, any difference from SELECT 1 vs SELECT * will be lost in
the noise compared to getting rid of the count(*).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461