Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id AF180632D6D for ; Tue, 14 Apr 2009 13:00:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 08281-10 for ; Tue, 14 Apr 2009 13:00:32 -0300 (ADT) Received: from mx1.hub.org (mx1.hub.org [200.46.208.106]) by mail.postgresql.org (Postfix) with ESMTP id 6C6BE63217B for ; Tue, 14 Apr 2009 13:00:26 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.25]) by mx1.hub.org (Postfix) with ESMTP id A1732125A9E2 for ; Tue, 14 Apr 2009 09:56:19 -0300 (ADT) Received: by qw-out-2122.google.com with SMTP id 8so2547997qwh.61 for ; Tue, 14 Apr 2009 05:56:01 -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=FJCRbyi44zojagDjLRSfJUCPpeihcnjoy1uQJMCQ42c=; b=sHtrJCN3sz2idbBvrOoY90+HZvCyJXOzYTn8Jfc9GlSkWUVENIv9QNO0hwDCufPKD1 2LT3qKd3scuJ7sSGawu4CPaDIreBBoVm/Kqs7kYyIfzUfMmE8z5O437yenqGW+AC1K15 fgFoQmIw+gJk3RlYX6+7GCs8PTBxiOCMvzmYk= 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=vUDkLTtJNI1kVvcNCwM80hz1B7QodNzRL3tzw9u6ZUDP9UIWyvGfmU1gOoD7GaW1TF LNviLh4X8IszKU3r0zOAw4PiKkyFPqFIB6h5ui4G7lmb6O7lhe2fVgdHgu5hYAQ53V+N XMUE0VQoYzmOg4rK3hzdX1R/wnnVIrNgQJECc= MIME-Version: 1.0 Received: by 10.224.60.204 with SMTP id q12mr7233407qah.241.1239713761213; Tue, 14 Apr 2009 05:56:01 -0700 (PDT) In-Reply-To: References: <003001c9b645$e8a7cec0$b9f76c40$@com> <003501c9b656$a9c73fe0$fd55bfa0$@com> <7371.1238981597@sss.pgh.pa.us> <000501c9b89c$4cb4b510$e61e1f30$@com> Date: Tue, 14 Apr 2009 08:56:01 -0400 Message-ID: Subject: Re: difficulties with time based queries From: Nikolas Everett To: PFC Cc: Rainer Mager , pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0015175cdd665c3d1d0467835a65 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: 200904/236 X-Sequence-Number: 33603 --0015175cdd665c3d1d0467835a65 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit > > If you often do range queries on date, consider partitioning your > table by date (something like 1 partition per month). > Of course, if you also often do range queries on something other > than date, and uncorrelated, forget it. If you pick your partition to line up with your queries than you can probably do away with the date index. Even if it doesn't always line up perfectly its worth considering. > > If you make a lot of big aggregate queries, consider materialized > views : > > Like "how many games player X won this week", etc > > - create "helper" tables which contain the query results > - every night, recompute the results taking into account the most > recent data > - don't recompute results based on old data that never changes > > This is only interesting if the aggregation reduces the data volume > by "an appreciable amount". For instance, if you run a supermarket with 1000 > distinct products in stock and you sell 100.000 items a day, keeping a cache > of "count of product X sold each day" will reduce your data load by about > 100 on the query "count of product X sold this month". This obviously creates some administration overhead. So long as this is manageable for you this is a great solution. You might also want to look at Mondrian at http://mondrian.pentaho.org/ . It takes some tinkering but buys you some neat views into your data and automatically uses those aggregate tables. Nik Everett --0015175cdd665c3d1d0467835a65 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
=A0 =A0 =A0=A0 If you often do range queries on date, consider pa= rtitioning your table by date (something like 1 partition per month).
=A0 =A0 =A0 =A0Of course, if you also often do range queries on something = other than date, and uncorrelated, forget it.

If you p= ick your partition to line up with your queries than you can probably do aw= ay with the date index.=A0 Even if it doesn't always line up perfectly = its worth considering.



=A0 =A0 =A0 =A0If you make a lot of big aggregate queries, consider materi= alized views :

=A0 =A0 =A0 =A0Like "how many games player X won this week", etc=

=A0 =A0 =A0 =A0- create "helper" tables which contain the query = results
=A0 =A0 =A0 =A0- every night, recompute the results taking into account th= e most recent data
=A0 =A0 =A0 =A0- don't recompute results based on old data that never = changes

=A0 =A0 =A0 =A0This is only interesting if the aggregation reduces the dat= a volume by "an appreciable amount". For instance, if you run a s= upermarket with 1000 distinct products in stock and you sell 100.000 items = a day, keeping a cache of "count of product X sold each day" will= reduce your data load by about 100 on the query "count of product X s= old this month".

This obviously creates some administration overhead.=A0 So long as= this is manageable for you this is a great solution.=A0 You might also wan= t to look at Mondrian at http://mo= ndrian.pentaho.org/ .=A0 It takes some tinkering but buys you some neat= views into your data and automatically uses those aggregate tables.
=A0

Nik Everett

--0015175cdd665c3d1d0467835a65--