| 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
| 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 |
| 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 |