From: Daniel Kalchev <daniel@digsys.bg>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: again on index usage
Date: Wed, 09 Jan 2002 22:06:43 +0200
Message-ID: <200201092006.WAA22792@dcave.digsys.bg> (raw)
In-Reply-To: <14552.1010605708@sss.pgh.pa.us>
>>>Tom Lane said:
> 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.
Ok, time to go home :-), but...
> 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.
In any case, if we need to hit 50k pages (assuming the indexed data is
randomly scattered in the file), and having to read these three times each, it
will be less I/O than having to read 1.7 million records. The table will never
be laid sequentially on the disk, at least not in this case (which adds to the
table day after day - and this is why data is almost ordered by ipdate).
What I am arguing about is the scaling - is 50k random reads worse than 1.7
million sequential reads? Eventually considering the tuple size, disk block
size etc.
I will wait patiently for 4.2 to release and see how this same table performs.
:-)
Daniel
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: daniel@digsys.bg, tgl@sss.pgh.pa.us
Subject: Re: again on index usage
In-Reply-To: <200201092006.WAA22792@dcave.digsys.bg>
* 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