On Sun, May 27, 2018 at 5:24 PM, Peter Geoghegan <p...@bowt.ie> wrote: > On Sun, May 27, 2018 at 5:10 PM, Tom Lane <t...@sss.pgh.pa.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@~[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@~[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
