From: Gene <genekhart@gmail.com>
To: Jonah H. Harris <jonah.harris@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org, "Heikki Linnakangas" <heikki@enterprisedb.com>
Cc: pgsql-patches@postgresql.org
Subject: Re: [HACKERS] Maintaining cluster order on insert
Date: Wed, 9 Aug 2006 21:31:49 -0400
Message-ID: <430d92a20608091831w6e59da84k48a58110cb98e8d6@mail.gmail.com> (raw)
In-Reply-To: <36e682920608091347m4e075a8cv6a04e3c4d97ed6f4@mail.gmail.com>
References: <44DA31B1.3090700@enterprisedb.com>
<28914.1155154622@sss.pgh.pa.us>
<36e682920608091347m4e075a8cv6a04e3c4d97ed6f4@mail.gmail.com>
I have a table that inserts lots of rows (million+ per day) int8 as primary
key, and I cluster by a timestamp which is approximately the timestamp of
the insert beforehand and is therefore in increasing order and doesn't
change. Most of the rows are updated about 3 times over time roughly within
the next 30 minutes. Should I assume that that all of these updates will be
on separate pages unless I perform a cluster (which takes a long time) and
performance will suffer due to this? Is it possible to preallocate space on
the same page for future updates (whatever the average number of updates may
be per row) decreasing the number of page loads for querying?
Gene
On 8/9/06, Jonah H. Harris <jonah.harris@gmail.com> wrote:
>
> On 8/9/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > UPDATE tries to place the new tuple on the same page it's already
> > on.
>
> I think he meant for INSERT.
>
> --
> Jonah H. Harris, Software Architect | phone: 732.331.1300
> EnterpriseDB Corporation | fax: 732.331.1301
> 33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
> Iselin, New Jersey 08830 | http://www.enterprisedb.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
Eugene Hart
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: genekhart@gmail.com, jonah.harris@gmail.com, tgl@sss.pgh.pa.us, heikki@enterprisedb.com, pgsql-patches@postgresql.org
Subject: Re: [HACKERS] Maintaining cluster order on insert
In-Reply-To: <430d92a20608091831w6e59da84k48a58110cb98e8d6@mail.gmail.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