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

Re: partitioning question 1

From: Ben <midfield(at)gmail(dot)com>
To: jd(at)commandprompt(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: partitioning question 1
Date: 2010-10-28 18:44:41
Message-ID: E03148FE-26C6-47BA-9463-B97749BE3E8C@gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
thanks for the prompt response.  some comments / questions below :

On Oct 28, 2010, at 10:31 AM, Joshua D. Drake wrote:
>> ...constraint exclusion is able to eliminate table partitions.  the I/O advantages of having queries target small subtables are the same as the I/O advantages of clustering the index : result pages in a small range are very close to each other on disk.
> 
> Not entirely true. One a clustered index will not stay clustered if you
> are still updating data that is in the partition. You shouldn't
> underestimate the benefit of smaller relations in terms of maintenance
> either.

in my situation, the update come in-order (it is timeseries data and the clustered index is on time.)  so the table should remain relatively clustered.  updates also happen relatively infrequently (once a day in one batch.)  so it appears that we will continue to get the I/O benefits described above.

are there any other benefits which partitioning provides for query performance (as opposed to update performance) besides the ones which i have mentioned?


> Yes the constraints have to be static. Not sure about the operator
> question honestly.

this seems to severely restrict their usefulness -- our queries are data warehouse analytical -type  queries, so the constraints are usually data-driven (come from joining against other tables.)

>> is my intuition completely off on this?
> 
> You may actually want to look into expression indexes, not clustered
> ones.


what would expression indexes give me?

thanks and best regards, ben


In response to

Responses

pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2010-10-28 18:50:14
Subject: Re: partitioning question 1
Previous:From: Merlin MoncureDate: 2010-10-28 18:18:26
Subject: Re: how to get the total number of records in report

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