Re: Re: Need help in reclaiming disk space by deleting the selected records

On 09/28/12 12:36 AM, Albe Laurenz wrote:
> Yelai, Ramkumar wrote:
> >7 base tables X 120 months = 840 child tables. As per your statement, If I create these many table then it will
affectthe performance. But as per the document (http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html)
constraint_exclusionwill improve query performance. Please clarify me here how query planning will be expensive? 
> The planner will have to decide which of the 840 tables
> to access.
>


well, really, which of the 120 tables for a given base table.  he's got
7 different base tables.   120 partitions is still too many.

if I was partioning a table for 2 year retention, I'd probably do it by
month, or even quarter, so there would be 24 or 8 child tables for 2
years.    we use week tables for 6 months, but almost all of our
activity is to the latest week and the one before that, its quite rare
we need to dig back to older records..



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast