From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Kalchev <daniel@digsys.bg>
Cc: pgsql-hackers@postgresql.org
Subject: Re: again on index usage
Date: Wed, 09 Jan 2002 14:48:28 -0500
Message-ID: <14552.1010605708@sss.pgh.pa.us> (raw)
In-Reply-To: <200201091637.SAA20821@dcave.digsys.bg>
References: <200201091637.SAA20821@dcave.digsys.bg>
Daniel Kalchev <daniel@digsys.bg> writes:
> I understand the clustering might help with sequential scans, but why
> would it help with index scans?
No, the other way around: it makes no difference for seq scans, but can
speed up index scans quite a lot. With a clustered table, successive
index-driven fetches tend to hit the same pages rather than hitting
random pages throughout the table. That saves I/O.
Given the numbers you were quoting, if the table were in perfectly
random order by ipdate then there would probably have been about three
rows per page that the indexscan would've had to fetch. This would mean
touching each page three times in some random order. Unless the table
is small enough to fit in Postgres' shared buffer cache, that's going to
represent a lot of extra I/O --- a lot more than reading each page only
once, as a seqscan would do. At the other extreme, if the table is
perfectly ordered by ipdate then the indexscan need only hit a small
number of pages (all the rows we want are in a narrow range) and we
touch each page many times before moving on to the next. Very few I/O
requests in that case.
7.1 does not have any statistics about table order, so it uses the
conservative assumption that the ordering is random. 7.2 has more
statistical data and perhaps will make better estimates about the
cost of indexscans.
regards, tom lane
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-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, daniel@digsys.bg
Subject: Re: again on index usage
In-Reply-To: <14552.1010605708@sss.pgh.pa.us>
* 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