DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=i/8FklqdDsOfyQenLesN7rulCtDyjbvfFV93JZXUNoFkgnlZ0s9f8WAHvXsAgQp2Ag
	ok7TuSoIFJmtkCIL2j4G8Q6+t08p01cYtAhopMKT0vzbUOyhrsu1EgAxlCQiE2y8d3/x
	BAgBStBJ1J07+klgXvwrUb2S2zPgr85Ozgkgc=
MIME-Version: 1.0
Date: Sun, 17 May 2009 22:04:46 -0600
Message-ID: <5c4ddc540905172104t1a91b829tc2848798a31df886@mail.gmail.com>
Subject: Extrapolating performance expectation
From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=0015175ce0c617ade0046a27e5f1

--0015175ce0c617ade0046a27e5f1
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Can one extrapolate future performance expectations for ever-growing tables
from a given (non-trivial) data set, and if so with what curve?  Corollary:
what would one expect a performance curve to approximate in terms of query
execution time v. number of data rows (hardware, load staying constant).

I have user and group information on system usage.  I would like to be able
to do year-to-date counts per user given a single group id but in the data
for one business quarter the query is taking in between 10 and 60+  seconds
depending on both on the size of the group and the group's total usage.
Groups typically have 10-100 users and consume 20K - 80K records in a 9M
record data set.  Group id column is indexed, but it is not the primary
index.  (Sad note: two pseudo groups account for 50 percent of the total
records IIRC (and will never be used for the usage-by-group query below)

This is a single table query:

select user_id, element_type, count(*)
from dataset
where group_id = N
group by user_id, element_type
order by user_id, element_type

Is this the sort of situation which might benefit from increasing the number
of histogram bins (alter table alter column statistics (N>10))?

Any and all pointers appreciated,

--0015175ce0c617ade0046a27e5f1
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Can one extrapolate future performance expectations for ever-growing tables=
 from a given (non-trivial) data set, and if so with what curve?=A0 Corolla=
ry: what would one expect a performance curve to approximate in terms of qu=
ery execution time v. number of data rows (hardware, load staying constant)=
.<br>
<br>I have user and group information on system usage.=A0 I would like to b=
e able to do year-to-date counts per user given a single group id but in th=
e data for one business quarter the query is taking in between 10 and 60+=
=A0 seconds depending on both on the size of the group and the group&#39;s =
total usage.=A0 Groups typically have 10-100 users and consume 20K - 80K re=
cords in a 9M record data set.=A0 Group id column is indexed, but it is not=
 the primary index.=A0 (Sad note: two pseudo groups account for 50 percent =
of the total records IIRC (and will never be used for the usage-by-group qu=
ery below)<br>
<br>This is a single table query:<br><br>select user_id, element_type, coun=
t(*) <br>from dataset <br>where group_id =3D N<br>group by user_id, element=
_type<br>order by user_id, element_type <br><br>Is this the sort of situati=
on which might benefit from increasing the number of histogram bins (alter =
table alter column statistics (N&gt;10))?<br>
<br>Any and all pointers appreciated,<br><br><br>

--0015175ce0c617ade0046a27e5f1--