From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Jean-Christophe Boggio <cat(at)thefreecat(dot)org> |
Cc: | Postgresql Mailing List <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Strange DISTINCT ! |
Date: | 2001-08-20 17:11:02 |
Message-ID: | Pine.BSF.4.21.0108201004470.99110-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Mon, 20 Aug 2001, Jean-Christophe Boggio wrote:
> Hello, can someone explain to me why this query is so slow :
>
> select distinct t.idmembre,p.datecrea
> from tmp_stat t,prefs p
> where p.idmembre=t.idmembre
> limit 5;
>
> And this one is so fast :
>
> select t.idmembre,p.datecrea
> from (select distinct idmembre from tmp_stat) as t,
> prefs p
> where p.idmembre=t.idmembre
> limit 5;
>
> (I currently have idmembre as an index on tmp_stat and prefs)
I'd suggest looking at the explain output for the two queries,
however, I believe the first query is likely to result in a sort
and unique step and the second is going to probably use the index
to distinct on tmp_stat. I think that *possibly* if you used
select distrinct p.idmembre, p.datecrea and made an index on (idmembre,
datecrea) on prefs you could possibly see an improvement but I really
don't know.
In addition, these two queries may not do the same thing. The first
will unique over both membre and datecrea whereas I think the second
will not, so if you had two prefs rows with the same idmembre and
datecrea, I believe the first will give one row and the second two.
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Eckermann | 2001-08-20 17:28:34 | Re: Sequential select queries...?? |
Previous Message | Ross J. Reedstrom | 2001-08-20 17:09:43 | Re: Sequential select queries...?? |