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 C31883A3FC3 for ; Sun, 24 Oct 2004 21:11:56 +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 11828-10 for ; Sun, 24 Oct 2004 20:11:20 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id EFF0C3A2BE5 for ; Sun, 24 Oct 2004 21:11:47 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i9OKBrIH017690; Sun, 24 Oct 2004 16:11:53 -0400 (EDT) To: John Meinel Cc: pgsql-performance@postgresql.org Subject: Re: Sequential Scan with LIMIT In-reply-to: <417C023F.1080502@johnmeinel.com> References: <417C023F.1080502@johnmeinel.com> Comments: In-reply-to John Meinel message dated "Sun, 24 Oct 2004 14:27:59 -0500" Date: Sun, 24 Oct 2004 16:11:53 -0400 Message-ID: <17689.1098648713@sss.pgh.pa.us> From: Tom Lane 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/400 X-Sequence-Number: 8876 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