>> This begins to sound like these indexes are only useful on append-only
>> tables. Not that there aren't plenty of those, but ...
>
> But what?
... "but" the other comments further down in my email. Also, my
successive comments in other emails.
>> Why? Why can't we just update the affected pages in the index?
>
> The page range has to be scanned in order to find out the min/max values
> for the indexed columns on the range; and then, with these data, update
> the index.
Seems like you could incrementally update the range, at least for
inserts. If you insert a row which doesn't decrease the min or increase
the max, you can ignore it, and if it does increase/decrease, you can
change the min/max. No?
For updates, things are more complicated. If the row you're updating
was the min/max, in theory you should update it to adjust that, but you
can't verify that it was the ONLY min/max row without doing a full scan.My suggestion would be to add a "dirty" flag
whichwould indicate that
that block could use a rescan next VACUUM, and otherwise ignore changing
the min/max. After all, the only defect to having min to low or max too
high for a block would be scanning too many blocks. Which you'd do
anyway with it marked "invalid".
> This is not a requirement. It merely makes the index more effective.
Right. So I'm saying let's do this index without the FSM modifications,
and then consider those as their own, separate patch, if we even do them.
> Eh? Sure, my intention for this reloption is for the user to be able to
> state their intention for the table, and each feature that has
> append-only table optimization does its thing. I wasn't thinking in
> anything automatic.
99.7% of our users have no idea what to do with reloptions. We'd have
to expose it with an ALTER TABLE SET append_only=true.
>> Also, I hate the name ...
>
> Feel free to propose other names; that way I can hate your proposals
> too (or maybe not).
Well, my first thought was "block-range indexing", which I think is the
best description, but that isn't exactly an exciting name for a feature
which will likely be worthy of short-listing for 9.4. I'd prefer it
over minmax, which users will think only works on aggregates, but it's
still not a great name. "Summary Index" also comes to mind, but really
isn't a lot more exciting.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com