pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Brown <time@bigpond.net.au>
To: Jim C. Nasby <decibel@decibel.org>
Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: index scan on =, but not < ?
Date: Thu, 10 Mar 2005 10:24:46 +1000
Message-ID: <422F93CE.10400@bigpond.net.au> (raw)
In-Reply-To: <20050309172238.GD528@decibel.org>
References: <001f01c5240d$a920c8c0$0200a8c0@dell8200>
	<20050309043821.GA1569@wolff.to>
	<20050309045519.GA509@decibel.org>
	<20050309052020.GB1569@wolff.to>
	<20050309172238.GD528@decibel.org>

Jim C. Nasby wrote:

>Ahh, I was thinking of a high correlation factor on the index. I still
>question 5% though... that seems awefully low.
>  
>
Not really. It all depends on how many records you're packing into each 
page. 1% may well be the threshold for small records.

Tom mentioned this in the last couple of months. He was citing a uniform 
distribution as an example and I thought that sounded a little 
pessimistic, but when I did the (possibly faulty) math with a random 
distribution, I discovered he wasn't far off.

It's not this simple, but if you can fit 50 randomly organized records 
into each page and you want to retrieve 2% of the rows, it's likely 
you'll have to fetch every page - believe it or not.

What concerns me is that this all depends on the correlation factor, and 
I suspect that the planner is not giving enough weight to this. 
Actually, I'm wondering if it's even looking at the statistic, but I 
haven't created a test to check. It might explain quite a few complaints 
about the planner not utilizing indexes.



view thread (12+ messages)  latest in thread

Message-ID: <422F93CE.10400@bigpond.net.au>
Permalink:  ../422F93CE.10400@bigpond.net.au/
Also on:    postgresql.org/message-id/422F93CE.10400@bigpond.net.au

 · 

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: time@bigpond.net.au, decibel@decibel.org, tgl@sss.pgh.pa.us
  Subject: Re: index scan on =, but not < ?
  In-Reply-To: <422F93CE.10400@bigpond.net.au>

* 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