From: Gregory Stark <stark@enterprisedb.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: Jaime Casanova <systemguards@gmail.com>
Cc: Jim C. Nasby <decibel@decibel.org>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: PostgreSQL-patches <pgsql-patches@postgresql.org>
Subject: Re: Maintaining cluster order on insert
Date: Tue, 10 Jul 2007 12:23:23 +0100
Message-ID: <87odikzems.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <457.1184023772@sss.pgh.pa.us>
References: <200705152223.l4FMNJa17969@momjian.us>
<464A33AB.1050401@enterprisedb.com>
<20070516003810.GI20707@nasby.net>
<464B15EF.4000808@enterprisedb.com>
<c2d9e70e0705172153g3207adb6kd4abdfdf3a9215ff@mail.gmail.com>
<464D96D6.1030608@enterprisedb.com>
<457.1184023772@sss.pgh.pa.us>
"Tom Lane" <tgl@sss.pgh.pa.us> writes:
> * delete a random 2% of the table
> * vacuum to recover space
> * insert a random 2% of the table
> * select (about) 1000 consecutively-numbered rows
> * select all the rows (this is just a cross check that the
> number of rows isn't changing too much)
>
> What you would hope to see as the benefit of the patch is that the time
> for the range SELECT degrades more slowly as more of the table is
> replaced. Ignoring the first SELECT as being a startup transient, it
> looks like HEAD degrades from about 3 msec to 6 msec over 10 iterations
> (20% replacement of the table), whereas with the patch it's about 3 msec
> to about 4 and a half. However, the INSERT steps went from around 20
> sec each to about twice that.
I would suggest:
a) continuing the test until you have 100% replacement.
b) defining the tables with a fill-factor large enough to hold at least one
extra tuple
c) considering this patch alongside GII where it is especially important.
It's pretty serious what you're suggesting since it means that we'll basically
never have a real cluster feature. I would sure hope we're missing something
and there's a way to make this work usefully.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
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: stark@enterprisedb.com, tgl@sss.pgh.pa.us, heikki@enterprisedb.com, systemguards@gmail.com, decibel@decibel.org, bruce@momjian.us, pgsql-patches@postgresql.org
Subject: Re: Maintaining cluster order on insert
In-Reply-To: <87odikzems.fsf@oxford.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