pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
 
 
 
 

view thread (2+ messages)  latest in thread

Message-ID: <A177ACC01D78D4118FC100508BAE3F7A0207CE10@TELECOMMAIL>
Permalink:  ../A177ACC01D78D4118FC100508BAE3F7A0207CE10@TELECOMMAIL/
Also on:    postgresql.org/message-id/A177ACC01D78D4118FC100508BAE3F7A0207CE10@TELECOMMAIL

 · 

reply

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