Re: [RFC] Minmax indexes

Tom Lane wrote:

> We've talked a lot about index-organized tables in the past. How much
> of the use case for this would be subsumed by a feature like that?

IOTs are not flexible enough. You can only have one index that you
index-organize the table on; and you can search only based on a prefix
of the index key. If you want to change the key, ... um. I don't even
know what you'd do.

With minmax indexes, on the other hand, you can create one or several,
and they let you scan the table based on any of the indexed columns. So
you can create a minmax index on creation_date, insertion_date, ship_date;
and have it serve queries that use any of these columns. (You probably
don't add key column(s) to the minmax index because you already have
btrees on them.)

On the other hand, IOTs are expensive to insert into. For each tuple to
insert you need to start from the root and descend the tree, insert your
tuple, then propagate splits upwards. If you have a 10 TB table, you
cannot afford to have to do all that for each and every tuple.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services