From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Tobias Brox <tobias@nordicbet.com>
Cc: Peter Childs <peterachilds@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner statistics, correlations
Date: Fri, 12 Jan 2007 10:55:40 +0000
Message-ID: <45A7692C.2080106@enterprisedb.com> (raw)
In-Reply-To: <20070112104422.GD9703@oppetid.no>
References: <20070112081631.GA10636@oppetid.no>
<a2de01dd0701120056p37f1906excd71068468e5769a@mail.gmail.com>
<20070112090500.GB16243@oppetid.no>
<45A765DE.9020003@enterprisedb.com>
<20070112104422.GD9703@oppetid.no>
Tobias Brox wrote:
> [Heikki Linnakangas - Fri at 10:41:34AM +0000]
>> I thought about partitioning the table by state, putting rows with
>> state=4 into one partition, and all others to another partition.
>
> That sounds like a good idea - but wouldn't that be costly when changing state?
In PostgreSQL, UPDATE internally inserts a new row and marks the old one
as deleted, so there shouldn't be much of a performance difference.
I'm not very familiar with our partitioning support, so I'm not sure if
there's any problems with an update moving a row from one partition to
another. I think you'll have to create an INSTEAD OF UPDATE rule to do a
DELETE on one partition and an INSERT on the other partition. Depending
on your application, that might be a problem; UPDATE is different from
DELETE+INSERT from transaction isolation point of view.
--
Heikki Linnakangas
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-performance@postgresql.org
Cc: heikki@enterprisedb.com, tobias@nordicbet.com, peterachilds@gmail.com
Subject: Re: Planner statistics, correlations
In-Reply-To: <45A7692C.2080106@enterprisedb.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