pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim C. Nasby <decibel@decibel.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: John Meinel <john@johnmeinel.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sequential Scan with LIMIT
Date: Thu, 28 Oct 2004 18:54:30 -0500
Message-ID: <20041028235430.GJ55164@decibel.org> (raw)
In-Reply-To: <9172.1099007368@sss.pgh.pa.us>
References: <417C023F.1080502@johnmeinel.com>
	<17689.1098648713@sss.pgh.pa.us>
	<20041028234658.GI55164@decibel.org>
	<9172.1099007368@sss.pgh.pa.us>

On Thu, Oct 28, 2004 at 07:49:28PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <decibel@decibel.org> writes:
> > On Sun, Oct 24, 2004 at 04:11:53PM -0400, Tom Lane wrote:
> >> 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.
>  
> > Isn't that exactly what pg_stats.correlation is?
> 
> No.  A far-from-zero correlation gives you a clue that on average, *all*
> the data values are placed nonrandomly ... but it doesn't really tell
> you much one way or the other about a single data value.
 
Maybe I'm confused about what the original issue was then... it appeared
that you were suggesting PGSQL was doing a seq scan instead of an index
scan because it thought it would find it on the first page if the data
was randomly distributed. If the correlation is highly non-zero though,
shouldn't it 'play it safe' and assume that unless it's picking the min
or max value stored in statistics it will be better to do an index scan,
since the value it's looking for is probably in the middle of the table
somewhere? IE: if the values in the field are between 1 and 5 and the
table is clustered on that field then clearly an index scan would be
better to find a row with field=3 than a seq scan.
-- 
Jim C. Nasby, Database Consultant               decibel@decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



view thread (13+ messages)

Message-ID: <20041028235430.GJ55164@decibel.org>
Permalink:  ../20041028235430.GJ55164@decibel.org/
Also on:    postgresql.org/message-id/20041028235430.GJ55164@decibel.org

 · 

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

* 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