From: Nikolas Everett <nik9000@gmail.com>
To: PFC <lists@peufeu.com>
Cc: Rainer Mager <rainer@vanten.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: difficulties with time based queries
Date: Tue, 14 Apr 2009 08:56:01 -0400
Message-ID: <d4e11e980904140556g40455a9akf12885cd48b33a73@mail.gmail.com> (raw)
In-Reply-To: <op.usddg5sycigqcu@soyouz>
References: <003001c9b645$e8a7cec0$b9f76c40$@com>
<e7f9235d0904051646j7e8374afncf79e3708ae40c20@mail.gmail.com>
<003501c9b656$a9c73fe0$fd55bfa0$@com>
<7371.1238981597@sss.pgh.pa.us>
<000501c9b89c$4cb4b510$e61e1f30$@com>
<op.usddg5sycigqcu@soyouz>
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nik9000@gmail.com, lists@peufeu.com, rainer@vanten.com
Subject: Re: difficulties with time based queries
In-Reply-To: <d4e11e980904140556g40455a9akf12885cd48b33a73@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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