pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
queries on huge tables
2+ messages / 2 participants
[nested] [flat]

* queries on huge tables
@ 2005-03-17 14:01 Lending, Rune <rune.lending@inpoc.com>
  2005-03-19 13:16 ` Re: queries on huge tables Mike Rylander <mrylander@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Lending, Rune @ 2005-03-17 14:01 UTC (permalink / raw)
  To: pgsql-performance

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
 
 
 
 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: queries on huge tables
  2005-03-17 14:01 queries on huge tables Lending, Rune <rune.lending@inpoc.com>
@ 2005-03-19 13:16 ` Mike Rylander <mrylander@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Mike Rylander @ 2005-03-19 13:16 UTC (permalink / raw)
  To: Lending, Rune <rune.lending@inpoc.com>; pgsql-performance

The most recent version of this thread starts here: 
http://archives.postgresql.org/pgsql-general/2005-03/msg00321.php . 
Search the archives for "table partition", "union view" and "partition
inherits" and you should find most relevant discussions.

Hope that helps!

On Thu, 17 Mar 2005 15:01:43 +0100, Lending, Rune
<rune.lending@inpoc.com> wrote:
>  
> 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 
>   
>   
>   
>   


-- 
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-03-19 13:16 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-17 14:01 queries on huge tables Lending, Rune <rune.lending@inpoc.com>
2005-03-19 13:16 ` Mike Rylander <mrylander@gmail.com>

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