Re: Fix for seg picksplit function

On 2010-11-20 13:36, Yeb Havinga wrote:
> On 2010-11-20 04:46, Robert Haas wrote:
>> Well, the problem with just comparing on< is that it takes very
>> little account of the upper bounds. I think the cases where a simple
>> split would hurt you the most are those where examining the upper
>> bound is necessary to to get a good split.
> With the current 8K default blocksize, I put my money on the sorting
> algorithm for any seg case. The r-tree algorithm's performance is
> probably more influenced by large buckets->low tree depth->generic
> keys on non leaf nodes.
To test this conjecture I compared a default 9.0.1 postgres (with
debugging) to exactly the same postgres but with an 1K blocksize, with
the test that Alexander posted upthread.

8K blocksize:
postgres=# create index seg_test_idx on seg_test using gist (a);
CREATE INDEX
Time: 99613.308 ms
SELECT
Total runtime: 81.482 ms

1K blocksize:
CREATE INDEX
Time: 40113.252 ms
SELECT
Total runtime: 3.363 ms

Details of explain analyze are below. The rowcount results are not
exactly the same because I forgot to backup the first test, so created
new random data.
Though I didn't compare the sorting picksplit this way, I suspect that
that algorithm won't be effected so much by the difference in blocksize.

regards,
Yeb Havinga

************** 8K test ********
ostgres=# \timing
Timing is on.
postgres=# create index seg_test_idx on seg_test using gist (a);
CREATE INDEX
Time: 99613.308 ms
postgres=# show block_size ;
block_size
------------
8192
(1 row)

Time: 0.313 ms
postgres=# explain (buffers, analyze) select * from seg_test where a @>
'0.5 .. 0.5'::seg;
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------
-----
Bitmap Heap Scan on seg_test (cost=44.32..2589.66 rows=1000 width=12)
(actual time=91.061..91.304 rows=27 loops=1)
Recheck Cond: (a @> '0.5'::seg)
Buffers: shared hit=581 read=1729 written=298
-> Bitmap Index Scan on seg_test_idx (cost=0.00..44.07 rows=1000
width=0) (actual time=91.029..91.029 rows=27 loop
s=1)
Index Cond: (a @> '0.5'::seg)
Buffers: shared hit=581 read=1702 written=297
Total runtime: 91.792 ms
(7 rows)

Time: 309.687 ms
postgres=# explain (buffers, analyze) select * from seg_test where a @>
'0.5 .. 0.5'::seg;
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------
-----
Bitmap Heap Scan on seg_test (cost=44.32..2589.66 rows=1000 width=12)
(actual time=81.357..81.405 rows=27 loops=1)
Recheck Cond: (a @> '0.5'::seg)
Buffers: shared hit=1231 read=1079
-> Bitmap Index Scan on seg_test_idx (cost=0.00..44.07 rows=1000
width=0) (actual time=81.337..81.337 rows=27 loop
s=1)
Index Cond: (a @> '0.5'::seg)
Buffers: shared hit=1204 read=1079
Total runtime: 81.482 ms
(7 rows)

Time: 82.291 ms

************** 1K test ********
postgres=# \timing
Timing is on.
postgres=# create index seg_test_idx on seg_test using gist (a);
CREATE INDEX
Time: 40113.252 ms
postgres=# explain (buffers, analyze) select * from seg_test where a @>
'0.5 .. 0.5'::seg;
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------
----
Bitmap Heap Scan on seg_test (cost=278.66..3812.85 rows=1000
width=12) (actual time=4.649..4.839 rows=34 loops=1)
Recheck Cond: (a @> '0.5'::seg)
Buffers: shared hit=221 read=385
-> Bitmap Index Scan on seg_test_idx (cost=0.00..278.41 rows=1000
width=0) (actual time=4.620..4.620 rows=34 loops
=1)
Index Cond: (a @> '0.5'::seg)
Buffers: shared hit=221 read=351
Total runtime: 4.979 ms
(7 rows)

Time: 6.217 ms
postgres=# explain (buffers, analyze) select * from seg_test where a @>
'0.5 .. 0.5'::seg;
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------
----
Bitmap Heap Scan on seg_test (cost=278.66..3812.85 rows=1000
width=12) (actual time=3.239..3.310 rows=34 loops=1)
Recheck Cond: (a @> '0.5'::seg)
Buffers: shared hit=606
-> Bitmap Index Scan on seg_test_idx (cost=0.00..278.41 rows=1000
width=0) (actual time=3.219..3.219 rows=34 loops
=1)
Index Cond: (a @> '0.5'::seg)
Buffers: shared hit=572
Total runtime: 3.363 ms
(7 rows)

Time: 4.063 ms
postgres=# show block_size;
block_size
------------
1024
(1 row)

Time: 0.300 ms