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

On Sun, May 27, 2018 at 5:24 PM, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> On Sun, May 27, 2018 at 5:10 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Instrumenting the test case suggests that getQuadrant pretty much always
>> returns 1, resulting in a worst-case unbalanced SPGiST tree. I think this
>> is related to the fact that the test case inserts the values in increasing
>> order, so that new values are always greater than existing values in the
>> index.
>
> I suspected the same. It reminded me of the weird behavior that the
> Postgres qsort() sometimes exhibits.

I confirmed this by using CLUSTER on an index built against a new
column with no physical/logical correlation (a column containing
random() data). This resulted in a dramatically faster build for the
SP-GiST index:

pg(at)~[31121]=# CREATE INDEX logs2_log_time_spgist_idx ON logs2 USING
spgist (log_time);
DEBUG: building index "logs2_log_time_spgist_idx" on table "logs2" serially
CREATE INDEX
Time: 3961.815 ms (00:03.962)

Also, the final index is only 88 MB (not 122 MB).

As a point of comparison, this is how a REINDEX of the GiST index went
against the same (CLUSTERed) table:

pg(at)~[31121]=# REINDEX INDEX logs2_log_time_gist_idx;
DEBUG: building index "logs2_log_time_gist_idx" on table "logs2" serially
REINDEX
Time: 14652.058 ms (00:14.652)

--
Peter Geoghegan