From: | Alexander Korotkov <aekorotkov(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Cc: | greg(at)turnstep(dot)com, Robert Haas <robertmhaas(at)gmail(dot)com> |
Subject: | Fix for cube picksplit function |
Date: | 2010-10-29 18:59:41 |
Message-ID: | AANLkTimC8W6guHpWJeWdjQA6WGoVH-7qG9Ar4pem2N2V@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hackers,
Gist picksplit method implementation in cube contrib module contains a bug
in Guttman's split algorithm implementation. It was mentioned before but it
is still not fixed yet. Current implementation doesn't cause incorrect
behavior, but index becomes very bad, because each index scan require to
read significant part of index.
test=# create table test (a cube);
test=# insert into test (select cube(array[random(),random(),random()]) from
generate_series(1,1000000));
test=# create index test_cube_idx on test using gist(a);
Before this patch, index scan of relatively small area requires read of 1235
index pages.
test=# explain (analyze,buffers) select * from test where a <@
cube(array[0.1,0.1,0.1],array[0.15,0.15,0.15]);
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=88.77..3046.68 rows=1000 width=56) (actual
time=17.497..18.188 rows=120 loops=1)
Recheck Cond: (a <@ '(0.1, 0.1, 0.1),(0.15, 0.15, 0.15)'::cube)
Buffers: shared hit=1354
-> Bitmap Index Scan on test_cube_idx (cost=0.00..88.52 rows=1000
width=0) (actual time=17.428..17.428 rows=120 loops=1)
Index Cond: (a <@ '(0.1, 0.1, 0.1),(0.15, 0.15, 0.15)'::cube)
Buffers: shared hit=1235
Total runtime: 18.407 ms
(7 rows)
Time: 19,501 ms
After this patch, index scan of same area requires read of only 44 index
pages.
test=# explain (analyze,buffers) select * from test where a <@
cube(array[0.1,0.1,0.1],array[0.15,0.15,0.15]);
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=76.66..3034.57 rows=1000 width=56) (actual
time=0.828..1.543 rows=120 loops=1)
Recheck Cond: (a <@ '(0.1, 0.1, 0.1),(0.15, 0.15, 0.15)'::cube)
Buffers: shared hit=163
-> Bitmap Index Scan on test_cube_idx (cost=0.00..76.41 rows=1000
width=0) (actual time=0.767..0.767 rows=120 loops=1)
Index Cond: (a <@ '(0.1, 0.1, 0.1),(0.15, 0.15, 0.15)'::cube)
Buffers: shared hit=44
Total runtime: 1.759 ms
(7 rows)
Seg contrib module and pg_temporal project contain same bug. But there are
another fix for them, because there is no need to use Guttman's split
algorithm at all in unidimensional case.
I'm going to add this patch to commitfest.
----
With best regards,
Alexander Korotkov.
Attachment | Content-Type | Size |
---|---|---|
cube_picksplit.patch | text/x-patch | 361 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2010-10-29 19:11:50 | Re: plan time of MASSIVE partitioning ... |
Previous Message | murphy pope | 2010-10-29 18:15:15 | Re: Madam I |