From: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
---|---|
To: | Tobias Brox <tobias(at)nordicbet(dot)com> |
Cc: | Peter Childs <peterachilds(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Planner statistics, correlations |
Date: | 2007-01-12 10:55:40 |
Message-ID: | 45A7692C.2080106@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gauri Kanekar | 2007-01-12 11:40:04 | Partitioning |
Previous Message | Tobias Brox | 2007-01-12 10:44:22 | Re: Planner statistics, correlations |