From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Meinel <john@johnmeinel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sequential Scan with LIMIT
Date: Sun, 24 Oct 2004 16:11:53 -0400
Message-ID: <17689.1098648713@sss.pgh.pa.us> (raw)
In-Reply-To: <417C023F.1080502@johnmeinel.com>
References: <417C023F.1080502@johnmeinel.com>
John Meinel <john@johnmeinel.com> 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
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: tgl@sss.pgh.pa.us, john@johnmeinel.com
Subject: Re: Sequential Scan with LIMIT
In-Reply-To: <17689.1098648713@sss.pgh.pa.us>
* 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