Обсуждение: queries on huge tables
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
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