Re: [RFC] Minmax indexes - Mailing list pgsql-hackers

Alvaro,

This sounds really interesting, and I can see the possibilities.
However ...

> Value changes in columns that are part of a minmax index, and tuple insertion
> in summarized pages, would invalidate the stored min/max values.  To support
> this, each minmax index has a validity map; a range can only be considered in a
> scan if it hasn't been invalidated by such changes (A range "not considered" in
> the scan needs to be returned in whole regardless of the stored min/max values,
> that is, it cannot be pruned per query quals).  The validity map is very
> similar to the visibility map in terms of performance characteristics: quick
> enough that it's not contentious, allowing updates and insertions to proceed
> even when data values violate the minmax index conditions.  An invalidated
> range can be made valid by re-summarization (see below).

This begins to sound like these indexes are only useful on append-only
tables.  Not that there aren't plenty of those, but ...

> Re-summarization is relatively expensive, because the complete page range has
> to be scanned.

Why?  Why can't we just update the affected pages in the index?

>  To avoid this, a table having a minmax index would be
> configured so that inserts only go to the page(s) at the end of the table; this
> avoids frequent invalidation of ranges in the middle of the table.  We provide
> a table reloption that tweaks the FSM behavior, so that summarized pages are
> not candidates for insertion.

We haven't had an index type which modifies table insertion behavior
before, and I'm not keen to start now; imagine having two indexes on the
same table each with their own, conflicting, requirements.  This is
sounding a lot more like a candidate for our prospective pluggable
storage manager.  Also, the above doesn't help us at all with UPDATEs.

If we're going to start adding reloptions for specific table behavior,
I'd rather think of all of the optimizations we might have for a
prospective "append-only table" and bundle those, rather than tying it
to whether a certain index exists or not.

Also, I hate the name ... if this feature goes ahead, I'm going to be
lobbying to change it.  But that's pretty minor compared to the update
issues.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com