Re: Fast insertion indexes: why no developments

On 13 November 2013 11:54, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Wed, Nov 13, 2013 at 7:33 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On 13 November 2013 09:31, Leonardo Francalanci <m_lists(at)yahoo(dot)it> wrote:
>>> I would like to see some numbers.
>>
>> Alvaro has given me some results for his patch. The figures I have are
>> for a 2GB table.
>>
>> Index Build Time
>> MinMax 11 s
>> Btree 96s
>>
>> Index Size
>> MinMax 2 pages + metapage
>> Btree approx 200,000 pages + metapage
>>
>> Load time
>> MinMax no overhead, same as raw COPY
>> BTree - considerably slower
>>
>> Index SELECT
>> Slower for small groups of rows
>> Broadly same for large requests (more results required on that assessment)
>>
>> I expect to publish results against TPC-H cases in next few weeks.
>>
>> Additional tests are welcome for other use cases.
>
> Those are pretty exciting numbers. These days for analytics work I'm
> using mostly covering index type approaches.

If you're using index only scans then this will work for you as well,
hopefully better. Same principle wrt "all visible" page ranges.

> I bet the tiny index
> would more than offset the extra heap accesses.

That's the trade-off, yes. I was hoping that would lead to cases where
the min max is better than a btree, but not there yet.

> Can you CLUSTER
> against a minmax index?

Not in this release, at least in my understanding. It's not yet
possible to do an ordered fetch, so the cluster scan probably won't
work.

I was hoping to include some special Freespace Map modes that would help there.

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