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 735523A3BFF for ; Sun, 24 Oct 2004 22:51:58 +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 37854-04 for ; Sun, 24 Oct 2004 21:51:21 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 6949B3A2BE5 for ; Sun, 24 Oct 2004 22:51:48 +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 i9OLpuMq000340; (envelope-from ) Sun, 24 Oct 2004 16:51:56 -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 i9OLpPBN019459; (envelope-from ) Sun, 24 Oct 2004 16:51:56 -0500 (CDT) Message-ID: <417C23DC.1090407@johnmeinel.com> Date: Sun, 24 Oct 2004 16:51:24 -0500 From: John Meinel User-Agent: Mozilla Thunderbird 0.8 (Windows/20040913) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Sequential Scan with LIMIT References: <417C023F.1080502@johnmeinel.com> <17689.1098648713@sss.pgh.pa.us> In-Reply-To: <17689.1098648713@sss.pgh.pa.us> 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="------------enigFCA213021A1493416B41EFC0" 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/401 X-Sequence-Number: 8877 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigFCA213021A1493416B41EFC0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: > John Meinel writes: > >>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, by mytable has 500,000 rows, and 'myval' occurs >>maybe 100,000 times. Without the LIMIT, this query should definitely do >>a sequential scan. > > >>But with the LIMIT, doesn't it know that it will return at max 1 value, >>and thus be able to use the index? > > > But the LIMIT will cut the cost of the seqscan case too. Given the > numbers you posit above, about one row in five will have 'myval', so a > seqscan can reasonably expect to hit the first matching row in the first > page of the table. This is still cheaper than doing an index scan > (which must require reading at least one index page plus at least one > table page). > > The test case you are showing is probably suffering from nonrandom > placement of this particular data value; which is something that the > statistics we keep are too crude to detect. > > regards, tom lane You are correct about non-random placement. I'm a little surprised it doesn't change with values, then. For instance, # select count(*) from finst_t where store_id = 52; 13967 Still does a sequential scan for the "select id from..." query. The only value it does an index query for is 9605 which only has 1 row. It estimates ~18,000 rows, but that is still < 3% of the total data. This row corresponds to disk location where files can be found. So when a storage location fills up, generally a new one is created. This means that *generally* the numbers will be increasing as you go further in the table (not guaranteed, as there are multiple locations open at any one time). Am I better off in this case just wrapping my query with: set enable_seqscan to off; query set enable_seqscan to on; There is still the possibility that there is a better way to determine existence of a value in a column. I was wondering about something like: SELECT 1 WHERE EXISTS (SELECT id FROM finst_t WHERE store_id=52 LIMIT 1); Though the second part is the same, so it still does the sequential scan. This isn't critical, I was just trying to understand what's going on. Thanks for your help. John =:-> --------------enigFCA213021A1493416B41EFC0 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 iD8DBQFBfCPgJdeBCYSNAAMRAgUkAJ9HPwhlnYuLWyDEcuukne0KL7TvsACghgPu 6vL+YolBeCgHTPMTfP/ldm4= =NvTk -----END PGP SIGNATURE----- --------------enigFCA213021A1493416B41EFC0--