X-Original-To: pgsql-patches-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 89F769FB30C for ; Wed, 9 Aug 2006 22:31:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 30538-03 for ; Wed, 9 Aug 2006 22:31:51 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.230]) by postgresql.org (Postfix) with ESMTP id 0891F9FB305 for ; Wed, 9 Aug 2006 22:31:51 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id t13so326219wxc for ; Wed, 09 Aug 2006 18:31:50 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=k9Miis8fPWCHF6naXRGJKuzHxCSiiSg/UycZVu5ZjfbUp2+fnztA5bHqM/PKzbdFiKtiqWfWj/Z2VCywKrzT3TS6y7RvzTROZlPn6qBSJb9LRFdFlw9IMREipLv0FMZGuqQpx0i5BEbheNP8xwvB5I7pTsVDx/zGRTLFgB6bFXE= Received: by 10.70.21.8 with SMTP id 8mr2404381wxu; Wed, 09 Aug 2006 18:31:49 -0700 (PDT) Received: by 10.65.115.12 with HTTP; Wed, 9 Aug 2006 18:31:49 -0700 (PDT) Message-ID: <430d92a20608091831w6e59da84k48a58110cb98e8d6@mail.gmail.com> Date: Wed, 9 Aug 2006 21:31:49 -0400 From: Gene Reply-To: genekhart@gmail.com To: "Jonah H. Harris" Subject: Re: [HACKERS] Maintaining cluster order on insert Cc: "Tom Lane" , pgsql-hackers@postgresql.org, "Heikki Linnakangas" , pgsql-patches@postgresql.org In-Reply-To: <36e682920608091347m4e075a8cv6a04e3c4d97ed6f4@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_18476_22942127.1155173509790" References: <44DA31B1.3090700@enterprisedb.com> <28914.1155154622@sss.pgh.pa.us> <36e682920608091347m4e075a8cv6a04e3c4d97ed6f4@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.574 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/134 X-Sequence-Number: 20650 ------=_Part_18476_22942127.1155173509790 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 wrote: > > On 8/9/06, Tom Lane 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 ------=_Part_18476_22942127.1155173509790 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_18476_22942127.1155173509790--