On 2010-11-20 21:57, Yeb Havinga wrote: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.
Here are the results for a 1K blocksize (debug enabled) and Alexanders
latest (0.5) patch.
postgres=# create index seg_test_idx on seg_test using gist (a);
CREATE INDEX
Time: 37373.398 ms
postgres=# explain (buffers, analyze) select * from seg_test where a @>
'0.5 .. 0.5'::seg; QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
BitmapHeap Scan on seg_test (cost=209.97..3744.16 rows=1000
width=12) (actual time=0.091..0.283 rows=34 loops=1) Recheck Cond: (a @> '0.5'::seg) Buffers: shared hit=6 read=35
-> Bitmap Index Scan on seg_test_idx (cost=0.00..209.72 rows=1000
width=0) (actual time=0.071..0.071 rows=34 loops=1) Index Cond: (a @> '0.5'::seg) Buffers: shared hit=6
read=1Total runtime: 0.392 ms
(7 rows)
Time: 1.798 ms
postgres=# explain (buffers, analyze) select * from seg_test where a @>
'0.5 .. 0.5'::seg; QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
BitmapHeap Scan on seg_test (cost=209.97..3744.16 rows=1000
width=12) (actual time=0.087..0.160 rows=34 loops=1) Recheck Cond: (a @> '0.5'::seg) Buffers: shared hit=41 ->
BitmapIndex Scan on seg_test_idx (cost=0.00..209.72 rows=1000
width=0) (actual time=0.068..0.068 rows=34 loops=1) Index Cond: (a @> '0.5'::seg) Buffers: shared hit=7
Totalruntime: 0.213 ms
(7 rows)
Time: 0.827 ms