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 15:20:59 -0500
Message-ID: <14755.1010607659@sss.pgh.pa.us> (raw)
In-Reply-To: <200201092006.WAA22792@dcave.digsys.bg>
References: <200201092006.WAA22792@dcave.digsys.bg>
Daniel Kalchev <daniel@digsys.bg> writes:
> 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.
How do you arrive at that? Assuming 100 records per page (probably the
right order of magnitude), the seqscan alternative is 17k page reads.
Yes, you examine more tuples, but CPUs are lots faster than disks.
That doesn't even address the fact that Unix systems reward sequential
reads and penalize random access. In a seqscan, we can expect that the
kernel will schedule the next page read before we ask for it, so that
our CPU time to examine a page is overlapped with I/O for the next page.
In an indexscan that advantage goes away, because neither we nor the
kernel know which page will be touched next. On top of the loss of
read-ahead, the filesystem is probably laid out in a way that rewards
sequential access with fewer and shorter seeks.
The tests I've made suggest that the penalty involved is about a factor
of four -- ie, a seqscan can scan four pages in the same amount of time
that it takes to bring in one randomly-accessed page.
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: <14755.1010607659@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