Re: Table partitioning

Thanks. Assuming there is an index on the time_arrived column, and that there are about 10.5 million records in each child table, how bad will performance be if the query actually accesses all the 12 tables? Will it be as bad as using the full 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 the idea 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 worth every 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 the april one?
>>
>> Second, when I delete (not drop!) from the mother table, are records deleted automatically from the child tables or do I 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 for the 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 "delete from <table>" versus "delete from only <table>)
>