pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: John Meinel <john@johnmeinel.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sequential Scan with LIMIT
Date: Sun, 24 Oct 2004 16:51:24 -0500
Message-ID: <417C23DC.1090407@johnmeinel.com> (raw)
In-Reply-To: <17689.1098648713@sss.pgh.pa.us>
References: <417C023F.1080502@johnmeinel.com>
	<17689.1098648713@sss.pgh.pa.us>

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
=:->

Attachments:

  signature.asc (application/pgp-signature, 252B) download

view thread (13+ messages)  latest in thread

Message-ID: <417C23DC.1090407@johnmeinel.com>
Permalink:  ../417C23DC.1090407@johnmeinel.com/
Also on:    postgresql.org/message-id/417C23DC.1090407@johnmeinel.com

 · 

reply

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: john@johnmeinel.com, tgl@sss.pgh.pa.us
  Subject: Re: Sequential Scan with LIMIT
  In-Reply-To: <417C23DC.1090407@johnmeinel.com>

* 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