Re: Fast insertion indexes: why no developments

On 12 November 2013 19:54, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:

> On Tue, Nov 12, 2013 at 7:14 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> I still think we need to look at this from a query perspective though.
>> We need to check whether there is a class of real world queries that
>> are not well optimised by minmax indexes, or cannot be made to be in
>> future releases. For example, large DELETEs from a table are almost
>> trivially optimised for min max.
>
> Only if you don't have a PK (or other index).

Right. Min max indexes are optimised for large DELETEs, btrees are not
(yet), which is what we are discussing.

I believe it remains to be shown that a btree is actually desirable on
a very big table. So far the discussion has just assumed this is the
case, without looking at specific SQL. It might be better to look at
ways of avoiding a btree altogether than to spend time optimising
btrees for this case.

Perhaps we can enforce a PK constraint without using a btree, if one
is required. This might be guaranteed by using a sequence or other
mechanism.

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