Re: SP-GiST failing to complete SP-GiST index build

On Sun, May 27, 2018 at 12:45 PM, Jonathan S. Katz <jkatz(at)postgresql(dot)org> wrote:
> Next, see bad.sql. 1.2MM sparsely clustered rows inserted, GiST indexes
> builds in about 30s on my machine. SP-GiST does not build at all, or at
> least I have been composing this email for about 10 minutes since I kicked
> off my latest and it has yet to terminate.
>
> I can understand this being an extreme case for SP-GiST as it’s better
> for data set that’s more densely clustered, but I wanted to provide
> this info to rule out whether or not this is a bug.

While I'm no SP-GiST expert, I strongly suspect that you've identified
a real bug here. Your test case has been running on my development
machine for 20 minutes now (this is server class hardware).

I ran perf with your testcase, and I see that the majority of
instructions are executed within these functions:

22.88% postgres postgres [.] spgdoinsert
12.98% postgres postgres [.] range_deserialize
11.44% postgres postgres [.] FunctionCall2Coll
10.40% postgres postgres [.] heap_tuple_untoast_attr
8.62% postgres postgres [.] spgExtractNodeLabels
5.92% postgres postgres [.] getQuadrant
4.90% postgres postgres [.] AllocSetAlloc

spgdoinsert() contains the following comment:

/*
* Bail out if query cancel is pending. We must have this somewhere
* in the loop since a broken opclass could produce an infinite
* picksplit loop.
*/
CHECK_FOR_INTERRUPTS();

Perhaps the problem is in the range type SP-GiST opclass support code
- it could have this exact picksplit infinite loop problem. That's
perfectly consistent with what we see here.

--
Peter Geoghegan