Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C2CCF634367 for ; Mon, 18 May 2009 12:29:45 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 39996-03 for ; Mon, 18 May 2009 12:29:44 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f123.google.com (mail-qy0-f123.google.com [209.85.221.123]) by mail.postgresql.org (Postfix) with ESMTP id 72219634168 for ; Mon, 18 May 2009 12:29:44 -0300 (ADT) Received: by qyk29 with SMTP id 29so5509790qyk.15 for ; Mon, 18 May 2009 08:29:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=YvWVK+HhU57cYn9xXE8hl0goO4eC2rwC6HJkUo/BXAk=; b=FGFRENPyM/9dD7MIAq3jmqb2HfOiOT8/4IWUhV8ZelWvVdRLqbQLai9THZxYgoAJSJ 7rFohdEVnSc29PNH6bJmPLPYXQ95vLPDHT8mQXcdAglCOhhFjO0GKI2nQi9w3E5dU+de vIEG6t0sThQ23dyxtJF9JaGGMAK8iZSA9+bhI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=wUSJWvSOU0VfqFeMfdjCWOt282fHgJN1c9bTjWknoaSuKcZRZ56W7uY4iWRmC8QDFJ QzSTQNjyZdARR40vMmCJnltUQyKjptngl2zZbcdushwF81vvoZsbMBRXaObQ1WB2obiN SlM2GrDUoLYotohRlXpy7pWhI0tKhzkzWMy7E= MIME-Version: 1.0 Received: by 10.224.37.134 with SMTP id x6mr6434952qad.154.1242660174669; Mon, 18 May 2009 08:22:54 -0700 (PDT) In-Reply-To: <20090518124701.GP28923@it.is.rice.edu> References: <5c4ddc540905172104t1a91b829tc2848798a31df886@mail.gmail.com> <20090518124701.GP28923@it.is.rice.edu> Date: Mon, 18 May 2009 09:22:54 -0600 Message-ID: <5c4ddc540905180822v398cd4bgd4bbbeb9456e022b@mail.gmail.com> Subject: Re: Extrapolating performance expectation From: Rob Sargent To: Kenneth Marshall Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0015175cdb8c49a92f046a315e1c X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200905/84 X-Sequence-Number: 32576 --0015175cdb8c49a92f046a315e1c Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On Mon, May 18, 2009 at 6:47 AM, Kenneth Marshall 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. --0015175cdb8c49a92f046a315e1c Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Mon, May 18, 2009 at 6:47 AM, Kenneth= Marshall <ktm@rice.ed= u> wrote:
On Sun, May 17, 2009 at 10:04:46PM -0600,= Rob Sargent wrote:
> Can one extrapolate future performance expectations for ever-growing t= ables
> from a given (non-trivial) data set, and if so with what curve? =A0Cor= ollary:
> what would one expect a performance curve to approximate in terms of q= uery
> execution time v. number of data rows (hardware, load staying constant= ).
>
> I have user and group information on system usage. =A0I 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+ =A0= seconds
> depending on both on the size of the group and the group's total u= sage.
> Groups typically have 10-100 users and consume 20K - 80K records in a = 9M
> record data set. =A0Group id column is indexed, but it is not the prim= ary
> index. =A0(Sad note: two pseudo groups account for 50 percent of the t= otal
> records IIRC (and will never be used for the usage-by-group query belo= w)
>
> This is a single table query:
>
> select user_id, element_type, count(*)
> from dataset
> where group_id =3D 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.=A0 I'm hoping the= psuedo groups are on the verge of extinction but will certainly investigat= e the possibility.

R.
--0015175cdb8c49a92f046a315e1c--