pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / 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