From: Greg Stark <gsstark@mit.edu>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Gaetano Mendola <mendola@bigfoot.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Date: 28 Aug 2004 04:15:01 -0400
Message-ID: <87k6vjllve.fsf@stark.xeocode.com> (raw)
In-Reply-To: <200408280354.i7S3slb21543@candle.pha.pa.us>
References: <200408280354.i7S3slb21543@candle.pha.pa.us>
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Don't you think this will permit also to avoid extra disk seek and cache
> > invalidation? If you are updating the row (0,1) I think is less expensive
> > put the new version in (0,2) instead of thousand line far from that point.
Well if the other buffer "a thousand lines far from that point" is already in
ram, then no, there's no penalty at the time for storing it there.
However it destroys the clustering, which was the original point.
> It would, but does that outweigh the decreased I/O by having things more
> densely packed? I would think not.
Well the dense packing is worth something. But so is the clustering. There's
definitely a trade-off.
I always found my largest tables are almost always insert-only tables anyways.
So in Oracle I would have pctused 100 pctfree 0 on them and get the
performance gain.
The tables that would benefit from this would be tables always accessed by
indexes in index scans of more than one record. The better the clustering the
fewer pages the index scan would have to read in. If the data took 10% more
space but the index scan only needs 1/4 as many buffers it could be a big net
win.
--
greg
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: gsstark@mit.edu, pgman@candle.pha.pa.us, mendola@bigfoot.com, tgl@sss.pgh.pa.us
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
In-Reply-To: <87k6vjllve.fsf@stark.xeocode.com>
* 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