Re: Unusable SP-GiST index

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Vik Fearing <vik(at)2ndquadrant(dot)fr>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Unusable SP-GiST index
Date: 2016-12-31 01:04:37
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Vik Fearing <vik(at)2ndquadrant(dot)fr> writes:
> While trying to find a case where spgist wins over btree for text, I
> came across the following behavior which I would consider a bug:

> CREATE TABLE texts (value text);
> INSERT INTO texts SELECT repeat('a', (2^20)::integer);
> CREATE INDEX ON texts USING spgist (value);
> SET enable_seqscan = off;
> TABLE texts;

> That produces:
> ERROR: index row requires 12024 bytes, maximum size is 8191

Hmm ... it's not really SP-GiST's fault. This query is trying to do
an index-only scan, and the API defined for that requires the index
to hand back an IndexTuple, which is of (very) limited size.
SP-GiST is capable of dealing with values much larger than one page,
but there's no way to hand them back through that API.

Maybe we should redefine the API as involving a TupleTableSlot that
the AM is supposed to fill --- basically, moving StoreIndexTuple
out of the common code in nodeIndexonlyscan.c and requiring the AM
to do that work. The possible breakage of third-party code is a
bit annoying, but there can't be all that many third-party AMs
out there yet.

regards, tom lane

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2016-12-31 02:13:16 Re: Unusable SP-GiST index
Previous Message Michael Paquier 2016-12-31 00:23:37 Re: Potential data loss of 2PC files