X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 710643A40CA for ; Mon, 25 Oct 2004 15:33:51 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08743-10 for ; Mon, 25 Oct 2004 14:33:42 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id C49723A40D0 for ; Mon, 25 Oct 2004 15:33:44 +0100 (BST) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.12.9/8.12.9) with ESMTP id i9PEXbMq021211; (envelope-from ) Mon, 25 Oct 2004 09:33:37 -0500 (CDT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by server11.icaen.uiowa.edu (8.12.9/smtp-service-1.6) with ESMTP id i9PEXaJ9001445 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); (envelope-from ) Mon, 25 Oct 2004 09:33:36 -0500 (CDT) Message-ID: <417D0EC2.5020206@johnmeinel.com> Date: Mon, 25 Oct 2004 09:33:38 -0500 From: John Meinel User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Curt Sampson Cc: pgsql-performance@postgresql.org Subject: Re: Sequential Scan with LIMIT References: <417C023F.1080502@johnmeinel.com> In-Reply-To: X-Enigmail-Version: 0.86.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig1883AEDB77A4CC3971BEA57B" X-Virus-Scanned: clamd / ClamAV version 0.75.1, clamav-milter version 0.66n X-Virus-Scanned: clamd / ClamAV version 0.75.1, clamav-milter version 0.75 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/410 X-Sequence-Number: 8886 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig1883AEDB77A4CC3971BEA57B Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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. What's also weird is stuff like: SELECT DISTINCT(NULL) FROM mytable WHERE col = 'myval' LIMIT 1; Also searches the entire table, sorting that NULL == NULL wherever col = 'myval'. Which is as expensive as the non-limited case (I'm guessing that the limit is occurring after the distinct, which is causing the problem. SELECT NULL FROM ... still uses a sequential scan, but it stops after finding the first one.) Actually, in doing a little bit more testing, the working query only works on some of the values. Probably it just increases the expense enough that it switches over. It also has the downside that when it does switch to seq scan, it is much more expensive as it has to do a sort and a unique on all the entries. John =:-> --------------enig1883AEDB77A4CC3971BEA57B Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBfQ7CJdeBCYSNAAMRAu0QAKDOWBXEP/2tshNMNTtGlr5fEPLuPQCeOURu 2Gsj++N4zoUmHhMJqnKvmFE= =sJHL -----END PGP SIGNATURE----- --------------enig1883AEDB77A4CC3971BEA57B--