Performance with many updates

From: Tasdassa Asdasda <tajnymail2(at)yahoo(dot)pl>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Performance with many updates
Date: 2011-08-29 09:13:39
Message-ID: 1314609219.14319.YahooMailNeo@web132406.mail.ird.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi. I have a table called work (id bigserial, userid int4, kind1 enum, kind2 enum, kind3 enim, value bigint, modified timestamp)
Table will have about 2*10^6 rows (at same time - overall it can have higher IDs but old records are eventually deleted (moved to separate archive table) therefore the IDs can grow huge). After insert on table work, every row will be updated (value will be reduced, till value = 0 (work is finished)). For each row there will be from 1 to
maybe 10 updates on two cells (value, modified). After work is completed (value = 0) it's record will be moved to archive table.
kind1 is an enum with two values (a and b)
i'm using:
- alter table work set fillfactor 50
- btree index on value, fillfactor 50
- btree index on kind1, fillfactor 50

my question:
1. what can i do to perform this selects faster:
SELECT id, value FROM work WHERE value>=$1 AND kind1=$2 AND kind2=$3 AND kind3=$4 FOR UPDATE;
SELECT id, value FROM work WHERE userid=$1 AND kind1=$1 AND kind2=$3 AND kind3=$4 FOR UPDATE;
2. How about inheriting and partitioning? I'm thinking about creating two tables, one for kind1(a) and second for kind1(b), will it help in performance?
3. Is btree best for index on enum?
4. How about creating index on complex keys like (user_id,kind1,kind2,kind3) and (price,kind1,kind2,kind3)?

I have PostgreSQL 9.0.4.
Thanks in advance

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tomas Vondra 2011-08-29 20:38:06 Re: Performance with many updates
Previous Message Kevin Grittner 2011-08-25 14:43:46 Re: reltuples value less than rows in the table.