Re: Partial indexes instead of partitions

On 11 June 2010 13:00, Leonardo F <m_lists(at)yahoo(dot)it> wrote:
> a) create 480 partitions, 1 for each hour of the day. 2 indexes on each
> partition
> b) create 20 partitions, and create 24*2 partial indexes on the current
> partition; then the next day (overnight) create 2 global indexes for the
> table and drop the 24*2 indexes...
>
> I thought about option b) because I don't like the fact that the planner takes
> "ages" to plan a query in case there are 480 partitions; in option b) I would
> have:
>
> 19 partitions with 2 indexes each
> 1 partition (the "current day" one) with 24*2 partial indexes

Could you please explain the reason to do so many partitions?

In case b) you will face a huge overhead related to necessity of
checking all the data in the table every time new index is created
(doesn't matter it is partial).

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray(dot)ru(at)gmail(dot)com / Skype: gray-hemp / ICQ: 29353802