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
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap 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=1
Total 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
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap 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
-> Bitmap Index 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
Total runtime: 0.213 ms
(7 rows)
Time: 0.827 ms
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
