From: Jaime Casanova <systemguards@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Sequential Scan with LIMIT
Date: Tue, 26 Oct 2004 15:19:06 -0500 (CDT)
Message-ID: <20041026201906.30736.qmail@web50001.mail.yahoo.com> (raw)
In-Reply-To: <417D0EC2.5020206@johnmeinel.com>
--- John Meinel <john@johnmeinel.com> escribió:
> Curt Sampson wrote:
> > On Sun, 24 Oct 2004, John Meinel wrote:
> >
> >
> >>I was looking into another problem, and I found
> something that surprised
> >>me. If I'm doing "SELECT * FROM mytable WHERE col
> = 'myval' LIMIT 1.".
> >>Now "col" is indexed...
> >>The real purpose of this query is to check to see
> if a value exists in
> >>the column,...
> >
> >
> > When you select all the columns, you're going to
> force it to go to the
> > table. If you select only the indexed column, it
> ought to be able to use
> > just the index, and never read the table at all.
> You could also use more
> > standard and more set-oriented SQL while you're at
> it:
> >
> > SELECT DISTINCT(col) FROM mytable WHERE col =
> 'myval'
> >
> > cjs
>
> Well, what you wrote was actually much slower, as it
> had to scan the
> whole table, grab all the rows, and then distinct
> them in the end.
>
> However, this query worked:
>
>
> SELECT DISTINCT(col) FROM mytable WHERE col =
> 'myval' LIMIT 1;
>
>
> Now, *why* that works differently from:
>
> SELECT col FROM mytable WHERE col = 'myval' LIMIT 1;
> or
> SELECT DISTINCT(col) FROM mytable WHERE col =
> 'myval';
>
> I'm not sure. They all return the same information.
of course, both queries will return the same but
that's just because you forced it.
LIMIT and DISTINCT are different things so they behave
and are plenned different.
>
> What's also weird is stuff like:
> SELECT DISTINCT(NULL) FROM mytable WHERE col =
> 'myval' LIMIT 1;
why do you want to do such a thing?
regards,
Jaime Casanova
_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: systemguards@yahoo.com
Subject: Re: Sequential Scan with LIMIT
In-Reply-To: <20041026201906.30736.qmail@web50001.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox