Thanks. Assuming there is an index on the time_arrived column, and that there are about 10.5 million records in each
childtable, how bad will performance be if the query actually accesses all the 12 tables? Will it be as bad as using
thefull table?
On 28/10/2013, at 18:31, Elliot wrote:
> On 2013-10-28 12:27, Herouth Maoz wrote:
>> I have a rather large and slow table in Postgresql 9.1. I'm thinking of partitioning it by months, but I don't like
theidea of creating and dropping tables all the time.
>>
>> I'm thinking of simply creating 12 child tables, in which the check condition will be, for example,
date_part('month'',time_arrived) = 1 (or 2 for February, 3 for March etc.).
>>
>> I'll just be deleting records rather than dropping tables, the same way I do in my current setup. I delete a week's
worthevery time.
>>
>> So, I have two questions.
>>
>> First, is constraint exclusion going to work with that kind of condition? I mean, if my WHERE clause says something
like"time_arrived >= '2013-04-05' and time_arrived < '2013-04-17'", will it be able to tell that
date_part("month",time_arrived)for all the records is 4, and therefore avoid selecting from any partitions other than
theapril one?
>>
>> Second, when I delete (not drop!) from the mother table, are records deleted automatically from the child tables or
doI need to create rules/triggers for that?
>>
>>
>> TIA,
>> Herouth
>>
> 1. No - you'd need a condition like "where date_part("month", time_arrived) = 1" in your select statements in order
forthe constraint exclusion to kick in
> 2. Yes - there is no need to create rules or triggers for deletes on the parent table (check out the syntax for
"deletefrom <table>" versus "delete from only <table>)
>