From: Tasdassa Asdasda <tajnymail2@yahoo.pl>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Performance with many updates
Date: Mon, 29 Aug 2011 10:13:39 +0100 (BST)
Message-ID: <1314609219.14319.YahooMailNeo@web132406.mail.ird.yahoo.com> (raw)
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
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: tajnymail2@yahoo.pl
Subject: Re: Performance with many updates
In-Reply-To: <1314609219.14319.YahooMailNeo@web132406.mail.ird.yahoo.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