From: Ben <midfield@gmail.com>
To: jd@commandprompt.com
Cc: pgsql-performance@postgresql.org
Subject: Re: partitioning question 1
Date: Thu, 28 Oct 2010 11:44:41 -0700
Message-ID: <E03148FE-26C6-47BA-9463-B97749BE3E8C@gmail.com> (raw)
In-Reply-To: <1288287092.22359.23.camel@jd-desktop>
References: <A9A748D8-A52F-4C16-A303-3A4AA1EC1BD2@gmail.com>
<1288287092.22359.23.camel@jd-desktop>
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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: midfield@gmail.com, jd@commandprompt.com
Subject: Re: partitioning question 1
In-Reply-To: <E03148FE-26C6-47BA-9463-B97749BE3E8C@gmail.com>
* 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