From: Lending, Rune <rune.lending@inpoc.com>
To: 'pgsql-performance@postgresql.org' <pgsql-performance@postgresql.org>
Subject: queries on huge tables
Date: Thu, 17 Mar 2005 15:01:43 +0100
Message-ID: <A177ACC01D78D4118FC100508BAE3F7A0207CE10@TELECOMMAIL> (raw)
Hello all.
I am having a couple of tables with couple of hundre millions records in
them. The tables contains a timestamp column.
I am almost always interested in getting datas from a specific day or month.
Each day contains aprox. 400.000 entries.
When I do such queries as " select ... from archive where m_date between
'2005-01-01' and '2005-02-01' group by ... " and so on.
It takes very long. I am having indexes that kicks in, but still it takes
sometime.
I have splitted the archive table in smaller monthly tables, it then goes a
lot faster, but not fast enough.
I know simular systems that uses Oracle and gains a lot on performance
because of the partioning. That kind of anoyes me a bit :)
Does anyone of you have some good ideas on how speed up such queries on
huge tables?
regards
rune
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: rune.lending@inpoc.com
Subject: Re: queries on huge tables
In-Reply-To: <A177ACC01D78D4118FC100508BAE3F7A0207CE10@TELECOMMAIL>
References: <A177ACC01D78D4118FC100508BAE3F7A0207CE10@TELECOMMAIL>
* 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