DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.pl;
	h=X-YMail-OSG:Received:X-Mailer:Message-ID:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type;
	b=raCs9OT4bJodOS52xPM4+CcPUt/OiVo1EpSrInCx0qRnIsOVWVmS8AxPoZBsSjcOU2F/7nYSwTJhS3QFevzkb+FhnN8i0n4tnIzrQNaPX3uMdZqcbGFIHBZzgVJJ/GnjevniYs8E7em15JlDsg8HG7lbIbQTu5XYZcL3vtAfqbI=;
Message-ID: <1314609219.14319.YahooMailNeo@web132406.mail.ird.yahoo.com>
Date: Mon, 29 Aug 2011 10:13:39 +0100 (BST)
From: Tasdassa Asdasda <tajnymail2@yahoo.pl>
Reply-To: Tasdassa Asdasda <tajnymail2@yahoo.pl>
Subject: Performance with many updates
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-90869122-1314609219=:14319"

--0-90869122-1314609219=:14319
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

Hi. I have a table called work (id bigserial, userid int4, kind1 enum, kind=
2 enum, kind3 enim, value bigint, modified timestamp)=0ATable will have abo=
ut 2*10^6 rows (at same time - overall it can have higher IDs but old recor=
ds are eventually deleted (moved to separate archive table) therefore the I=
Ds can grow huge). After insert on table work, every row will be updated (v=
alue will be reduced, till value =3D 0 (work is finished)). For each row th=
ere will be from 1 to =0Amaybe 10 updates on two cells (value, modified). A=
fter work is completed (value =3D 0) it's record will be moved to archive t=
able.=0Akind1 is an enum with two values (a and b)=0Ai'm using:=0A- alter t=
able work set fillfactor 50=0A- btree index on value, fillfactor 50=0A- btr=
ee index on kind1, fillfactor 50=0A=0Amy question:=0A1. what can i do to pe=
rform this selects faster:=0ASELECT id, value FROM work WHERE value>=3D$1 A=
ND kind1=3D$2 AND kind2=3D$3 AND kind3=3D$4 FOR UPDATE;=0ASELECT id, value =
FROM work WHERE userid=3D$1 AND kind1=3D$1 AND kind2=3D$3 AND kind3=3D$4 FO=
R UPDATE;=0A2. How about inheriting and partitioning? I'm thinking about cr=
eating two tables, one for kind1(a) and second for kind1(b), will it help i=
n performance?=0A3. Is btree best for index on enum?=0A4. How about creatin=
g index on complex keys like (user_id,kind1,kind2,kind3)  and (price,kind1,=
kind2,kind3)? =0A=0A=0AI have PostgreSQL 9.0.4.=0AThanks in advance=0A
--0-90869122-1314609219=:14319
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"color:#000; background-color:#fff; font-family:ti=
mes new roman, new york, times, serif;font-size:12pt"><div>Hi. I have a tab=
le called work (id bigserial, userid int4, kind1 enum, kind2 enum, kind3 en=
im, value bigint, modified timestamp)<br>Table will have about 2*10^6 rows =
(at same time - overall it can have higher IDs but old records are eventual=
ly deleted (moved to separate archive table) therefore the IDs can grow hug=
e). After insert on table work, every row will be updated (value will be re=
duced, till value =3D 0 (work is finished)). For each row there will be fro=
m 1 to <br>maybe 10 updates on two cells (value, modified). After work is c=
ompleted (value =3D 0) it's record will be moved to archive table.<br>kind1=
 is an enum with two values (a and b)<br>i'm using:<br>- alter table work s=
et fillfactor 50<br>- btree index on value, fillfactor 50<br>- btree index =
on kind1, fillfactor 50<br><br>my question:<br>1. what can i do to perform
 this selects faster:<br>SELECT id, value FROM work WHERE value&gt;=3D$1 AN=
D kind1=3D$2 AND kind2=3D$3 AND kind3=3D$4 FOR UPDATE;<br>SELECT id, value =
FROM work WHERE userid=3D$1 AND kind1=3D$1 AND kind2=3D$3 AND kind3=3D$4 FO=
R UPDATE;<br>2. How about inheriting and partitioning? I'm thinking about c=
reating two tables, one for kind1(a) and second for kind1(b), will it help =
in performance?<br>3. Is btree best for index on enum?<br>4. How about crea=
ting index on complex keys like (user_id,kind1,kind2,kind3)  and (price,kin=
d1,kind2,kind3)? <br></div><div><br></div><div>I have PostgreSQL 9.0.4.</di=
v><div>Thanks in advance<br></div></div></body></html>
--0-90869122-1314609219=:14319--