Re: GiST index performance

Yeb Havinga wrote:
>
> Since the gistpagesize is derived from the database blocksize, it
> might be wise to set the blocksize low for this case, I'm going to
> play with this a bit more.
Ok, one last mail before it turns into spam: with a 1KB database
blocksize, the query now runs in 30 seconds (original 70 on my machine,
shared buffers 240MB).
The per inner loop access time now 24 microsecs compared to on my
machine original 74 microsecs with 8KB size and 8 for the btree scan.
Not a bad speedup with such a simple parameter :-)

postgres=# EXPLAIN ANALYSE SELECT * FROM a, b WHERE a.a BETWEEN b.b AND
b.b + 2;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..4169159462.20 rows=111109777668 width=8)
(actual time=0.184..29540.355 rows=2999997 loops=1)
-> Seq Scan on b (cost=0.00..47037.62 rows=999962 width=4) (actual
time=0.024..1783.484 rows=1000000 loops=1)
-> Index Scan using a_a on a (cost=0.00..2224.78 rows=111114
width=4) (actual time=0.021..0.024 rows=3 loops=1000000)
Index Cond: ((a.a >= b.b) AND (a.a <= (b.b + 2)))
Total runtime: 30483.303 ms
(5 rows)

postgres=# select gist_stat('a_a');
gist_stat
-------------------------------------------
Number of levels: 5 +
Number of pages: 47618 +
Number of leaf pages: 45454 +
Number of tuples: 1047617 +
Number of invalid tuples: 0 +
Number of leaf tuples: 1000000 +
Total size of tuples: 21523756 bytes+
Total size of leaf tuples: 20545448 bytes+
Total size of index: 48760832 bytes+

(1 row)