Re: Planner statistics, correlations

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