Skip site navigation (1) Skip section navigation (2)

Re: Question on partitioning

From: Mark Roberts <mailing_lists(at)pandapocket(dot)com>
To: Oliveiros Cristina <oliveiros(dot)cristina(at)marktest(dot)pt>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Question on partitioning
Date: 2008-08-21 18:00:41
Message-ID: 1219341641.6163.65.camel@localhost (view raw or flat)
Thread:
Lists: pgsql-sql
On Thu, 2008-08-21 at 15:25 +0100, Oliveiros Cristina wrote:
> Hello , All.
>  
> I am not sure if this is the right mailing list to place this
> question.
> If it doesn't, please kindly redirect me to the right list.
>  
> I have a giant table with about 10,6 million records.
>  
> Queries on it are usually slow, and if I try to do something more
> elaborate like an INNER JOIN with itself it becomes unnacceptably
> slow.
>  
> I am looking for a way to improve performance.
> One of the columns is of type date. Each "day" includes about a few
> tens of thousands records
> And the older  a date is the less likely I am to do queries on it.
>  
> The objective of the "self join" is to compare data from two different
> days, looking for diferences.
>  
> Ive read that one of the benefits of partitioning is to speed up
> queries by separating less used records.
>  
> My question is if partitioning can be a good way to make the queries
> faster (specially the self joins) or if it isn't worth trying because
> it doesn't help on my particular situation.
>  
> Please kindly advice me on this
>  
> Many thanks in advance for your kind help
>  
> Best,
> Oliveiros

I would expect partitioning to work.  I've heard tell that fine grained
partitioning coupled with check constraints can even eliminate the need
for certain indexes.  I do know that check constraints on the date will
help you tremendously if you decide to partition.

-Mark


In response to

pgsql-sql by date

Next:From: Allan KamauDate: 2008-08-21 18:58:20
Subject: Re: Re: Efficiently determining the number of bits set in the contents of, a VARBIT field
Previous:From: Scott MarloweDate: 2008-08-21 16:50:27
Subject: Re: Question on partitioning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group