Fix for seg picksplit function

From: Alexander Korotkov <aekorotkov(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Fix for seg picksplit function
Date: 2010-11-03 21:23:41
Message-ID: AANLkTimeWpY8smv1RnO9ByPjgx-1utq=PWC=-g1e_V28@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Attachment Content-Type Size
seg_picksplit_fix-0.1.patch text/x-patch 6.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dimitri Fontaine 2010-11-03 21:32:25 Re: ALTER OBJECT any_name SET SCHEMA name
Previous Message Alvaro Herrera 2010-11-03 21:18:32 Re: ALTER OBJECT any_name SET SCHEMA name