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