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)
