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