On Jun 27, 2013 12:24 PM, "Nicolas Barbier" <nicolas.barb...@gmail.com> wrote: > > 2013/6/27 Nicolas Barbier <nicolas.barb...@gmail.com>: > > > When each index requires one extra I/O (because each index is > > one level taller), that is 50 extra I/Os. In the partitioned case, > > each index would require the normal smaller amount of I/Os. > > [..] > > > Using those other indexes (both for look-ups and > > updates) in the non-partitioned case, will therefore pull a huge > > portion of each index into cache (because of the “random distribution” > > of the non-date data). In the partitioned case, more cache can be > > spent on the indexes that correspond to the “hot partitions.” > > It seems that the system described by Claudio fixes this problem another way: > > Claudio wrote: > > > Now I just have two indices. One that indexes only hot tuples, it's > > very heavily queried and works blazingly fast, and one that indexes by > > (hotness, key).
This is not really related to hash partitioning, but you can also do index partitioning while having the tables unpartitioned. If the hotness field is a timestamp like it often is, you can create a predicate index on (key, tstamp) where tstamp > [some date in recent past], and replace the index with a newer one every so often to keep the size small. This way you can have a non-partitioned index for batch queries and a small one for the OLTP workload. If we added the option to build indexes using an index only scan, building the replacement index would be quite cheap. Regards, Ants Aasma
