pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExtrapolating performance expectation
2+ messages / 1 participants
[nested] [flat]
* Extrapolating performance expectation
@ 2009-05-18 04:04 Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Rob Sargent @ 2009-05-18 04:04 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Extrapolating performance expectation
@ 2009-05-18 15:22 Rob Sargent <robjsargent@gmail.com>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Rob Sargent @ 2009-05-18 15:22 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-sql
On Mon, May 18, 2009 at 6:47 AM, Kenneth Marshall <ktm@rice.edu> wrote:
> On Sun, May 17, 2009 at 10:04:46PM -0600, Rob Sargent wrote:
> > 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,
>
> Rob,
>
> What about partitioning the table based on the group. Then you could
> put the two pseudo groups in their own separate child table.
>
> Cheers,
> Ken
>
Ken,
Interesting point. I'm hoping the psuedo groups are on the verge of
extinction but will certainly investigate the possibility.
R.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-05-18 15:22 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-05-18 04:04 Extrapolating performance expectation Rob Sargent <robjsargent@gmail.com>
2009-05-18 15:22 ` Rob Sargent <robjsargent@gmail.com>
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