Tom Lane wrote:
> 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
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
=:->
