Improve cube GiST page splits

From: Andrey Borodin <x4mmm(at)yandex-team(dot)ru>
To: Alexander Korotkov <aekorotkov(at)gmail(dot)com>, pgsql-hackers mailing list <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Improve cube GiST page splits
Date: 2026-08-09 07:09:34
Message-ID: 3F6C9E73-9A6C-409D-9734-3D7C4E7FEA81@yandex-team.ru
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

In 2016 I noticed that cube's Guttman polynomial split can produce
very unbalanced GiST trees [0], but never got around to fixing it. The
problem is a bit dramatic for sorted input: repeated splits make
insertion quadratic and produce an enormous index.

For example, I inserted sorted two-dimensional points with an O2 build
without assertions. These are medians of three runs:

rows | master time | patched time | master pages | patched pages
------+-------------+--------------+--------------+--------------
1000 | 5.91 s | 0.0119 s | 1757 | 9
2000 | 24.74 s | 0.0150 s | 7929 | 18
4000 | 110.63 s | 0.0219 s | 33712 | 36

The workload was:

CREATE TABLE t (id int, c cube);
CREATE INDEX ON t USING gist (c);
INSERT INTO t
SELECT g, cube(ARRAY[g::float8 / 4000, g::float8 / 4000 + 1])
FROM generate_series(1, 4000) g;

The patch generalizes the double-sorting split used by the built-in box
and point GiST operator classes to arbitrary cube dimensionality. The
first patch adds regression coverage. The second implements the new
split.

PFA the patch set.

Best regards, Andrey Borodin

[0] https://www.postgresql.org/message-id/CAJEAwVEVq9Ry7KxApHbFTB67E0ntn2EBTQiUXBG%3Dq5E4P7w1VQ%40mail.gmail.com

Attachment Content-Type Size
0001-Add-regression-coverage-for-cube-GiST-page-splits.patch application/octet-stream 7.0 KB
0002-Use-double-sorting-for-cube-GiST-page-splits.patch application/octet-stream 17.1 KB
unknown_filename text/plain 2 bytes

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2026-08-09 07:23:41 Re: Random 038_walsnd_shutdown_timeout.pl failure on goldfish
Previous Message Chengpeng Yan 2026-08-09 06:41:07 Re: arrays over initdb-created types are broken after pg_upgrade