Gist fastbuild and performances

[talk to ben <blo.talkto@xxxxxxxxx>]

Hi,

I am playing around with the gist fast build and comparing the result between v13 and v14.

The space gain and speed increase are really awesome.

When I compare the performance with the following script, I get a lot more data read into the buffers in v14 and a little slower query.

Is it expected ? (is the test dumb / too artificial ?)

(I found some discussion about the buffer usage but don't quite understand the outcome 

https://www.postgresql.org/message-id/08173bd0-488d-da76-a904-912c35da446b%40iki.fi)

The script :

--pg14

\timing on
CREATE TABLE gist_fastbuild AS SELECT point(random(),random()) as pt FROM  generate_series(1,10000000,1);
CREATE INDEX ON gist_fastbuild USING gist (pt);
VACUUM ANALYZE gist_fastbuild;
\di+ gist_fastbuild_pt_idx
EXPLAIN (ANALYZE, BUFFERS) SELECT pt FROM gist_fastbuild WHERE pt <@ box(point(.5,.5), point(.75,.75));

COPY gist_fastbuild TO '/tmp/gist_fastbuild.copy';

--pg13
\timing on
CREATE TABLE gist_fastbuild(pt point);
COPY gist_fastbuild FROM '/tmp/gist_fastbuild.copy';
CREATE INDEX ON gist_fastbuild USING gist (pt);
VACUUM ANALYZE gist_fastbuild;
\di+ gist_fastbuild_pt_idx

EXPLAIN (ANALYZE, BUFFERS) SELECT pt FROM gist_fastbuild WHERE pt <@ box(point(.5,.5), point(.75,.75));

The explains :

V14# EXPLAIN (ANALYZE, BUFFERS) SELECT pt FROM gist_fastbuild WHERE pt <@ box(point(.5,.5), point(.75,.75));
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using gist_fastbuild_pt_idx on gist_fastbuild  (cost=0.42..419.42 rows=10000 width=16) (actual time=0.350..129.309 rows=626005 loops=1)
   Index Cond: (pt <@ '(0.75,0.75),(0.5,0.5)'::box)
   Heap Fetches: 0
   Buffers: shared hit=303083
 Planning:
   Buffers: shared hit=13
 Planning Time: 0.454 ms
 Execution Time: 148.611 ms
(8 rows)

V13# EXPLAIN (ANALYZE, BUFFERS) SELECT pt FROM gist_fastbuild WHERE pt <@ box(point(.5,.5), point(.75,.75));
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using gist_fastbuild_pt_idx on gist_fastbuild  (cost=0.42..539.42 rows=10000 width=16) (actual time=0.523..107.393 rows=626005 loops=1)
   Index Cond: (pt <@ '(0.75,0.75),(0.5,0.5)'::box)
   Heap Fetches: 0
   Buffers: shared hit=17334
 Planning:
   Buffers: shared hit=13
 Planning Time: 0.396 ms
 Execution Time: 126.713 ms
(8 rows)