Re: Monthly table partitioning for fast purges?

Did you think about cluster on index ?

psql-mail(at)freeuk(dot)com wrote:

>I am looking at ways to speed up queries, the most common way by for
>queries to be constrianed is by date range. I have indexed the date
>column. Queries are still slower than i would like.
>
>Would there be any performance increase for these types of queries if
>the tables were split by month as described by Shridhar (method1) so
>only the required tables were loaded from disk?
>
>Will there be any performance increase if table partitioning is
>implemented?
>
>If method1 is likely to yield a performance increase, will there be a
>large hit for other types
>of queries which require all the data?
>
>I'd be happy to post the EXPLAIN ANALYZE output for a typical query if
>more info is needed.
>
>The date column is of type timestamp (and so goes right down to seconds)
>, most user queries are only concerned about whole days without the
>times, (but hte time data is required for other queries) can i do
>something with an index of the timestamps cast to dates? and then cast
>the queries to dates too?
>
>Thanks!
>
>Ron Johnson Wrote:
>
>
>>On Sun, 2003-08-03 at 06:31, Shridhar Daithankar wrote:
>>
>>
>>>On 1 Aug 2003 at 13:46, Roger Hand wrote:
>>>
>>>
>>[snip]
>>
>>
>>>Here is what you can do.
>>>
>>>* Create a empty base table.
>>>* Create a trigger the directs the select on timestamps accordingly
>>>
>>>
>
>
>
>>>* Write a schedule to create a new table at the start of every
>>>
>>>
>month and update
>
>
>>>the trigger accordingly
>>>* Create and maintain rules to insert into base table depending
>>>
>>>
>upon the
>
>
>>>timestamp.
>>>
>>>This is just a rough idea.
>>>
>>>There might be fair amount of work to get this working but surely
>>>
>>>
>it is not
>
>
>>>imposible.
>>>
>>>
>>And you get a big performance hit when all those records are moved.
>>
>>Partitioning "should" be put on the TODO list soon after tablespaces
>>(or DBA-defined directories) is implemented.
>>
>>
>>
>
>
>