Hackers,
Seg contrib module contains the same bug in picksplit function as cube contrib module. Also, Guttman's split algorithm is not needed in unidimensional case, because sorting based algorithm is good in this case. I propose the patch which replace current picksplit implementation with sorting based algorithm.
test=# create table seg_test(a seg);
test=# insert into seg_test (select (a || ' .. ' || a + 0.00005*b)::seg from (select random() as a, random() as b from generate_series(1,1000000)) x);
Before the patch.
test=# create index seg_test_idx on seg_test using gist (a);
CREATE INDEX
Time: 263981,639 ms
test=# explain (buffers, analyze) select * from seg_test where a @> '0.5 .. 0.5'::seg;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on seg_test (cost=36.28..2508.41 rows=1000 width=12) (actual time=36.909..36.981 rows=23 loops=1)
Recheck Cond: (a @> '0.5'::seg)
Buffers: shared hit=1341
-> Bitmap Index Scan on seg_test_idx (cost=0.00..36.03 rows=1000 width=0) (actual time=36.889..36.889 rows=23 loops=1)
Index Cond: (a @> '0.5'::seg)
Buffers: shared hit=1318
Total runtime: 37.066 ms
(7 rows)
Time: 37,842 ms
After the patch.
test=# create index seg_test_idx on seg_test using gist (a);
CREATE INDEX
Time: 205476,854 ms
test=# explain (buffers, analyze) select * from seg_test where a @> '0.5 .. 0.5'::seg;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on seg_test (cost=28.18..2500.31 rows=1000 width=12) (actual time=0.283..0.397 rows=23 loops=1)
Recheck Cond: (a @> '0.5'::seg)
Buffers: shared hit=27
-> Bitmap Index Scan on seg_test_idx (cost=0.00..27.93 rows=1000 width=0) (actual time=0.261..0.261 rows=23 loops=1)
Index Cond: (a @> '0.5'::seg)
Buffers: shared hit=4
Total runtime: 0.503 ms
(7 rows)
Time: 1,530 ms
Number of pages, which was used for index scan, decreased from 1318 to 4.
I'm going to add this patch to commitfest.
Pg_temporal project contain same bug. If this patch will be accepted by community, then I'll prepare similar patch for pg_temporal.
----
With best regards,
Alexander Korotkov.
