Re: [RFC] Minmax indexes

On 17 June 2013 02:05, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>>> I agree that the FSM behaviour shouldn't be linked to index existence.
>>> IMHO that should be a separate table parameter, WITH (fsm_mode = append)
>>> Index only scans would also benefit from that.
>>
>> -1 ... I cannot believe that such a parameter would ever get turned on
>> in production by anyone. If your table has a significant update rate,
>> the resulting table bloat would make such behavior completely
>> infeasible. If you have few enough updates to make such a behavior
>> practical, then you can live with the expensive index updates instead.
>
> I'm also thinking that if a table is really append-only, then there are
> never any middle-of-the-table pages in the FSM, no?
>
> Where this falls down is the table which is
> mostly-append-but-occasionally-needs-an-update-or-delete. I think the
> answer there is to look for a way to make updating the index block range
> faster, not ways to modify how we append to the heap. If we told users
> "tables with Minmax indexes will be very expensive to update" then I
> think they'd live with it; dropping and readding an index to enable fast
> updates is something which is already familiar.

This feature is using a similar technique to enhance SeqScans as we
already use on VACUUM. We don't really care about whether we have 100%
scan avoidance because we were likely to be using a WHERE clause that
doesn't give perfect constraint elimination anyway. So on a large
table we don't care about the fact that we still have to scan 1-5% of
the table - we are still 20 times faster than a full seqscan.

So there isn't a "fall down" thing here. We expect the recently
loaded/updated data to be scanned and that's OK.

Having the minmax index updated greedily is just adding extra work for
fast diminishing returns. We can always add that later if really
needed, but I doubt it will be needed - in just the same way as mat
views aren't greedily updated.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services