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)?
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
