Gist fastbuild and performances

From: talk to ben <blo(dot)talkto(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Gist fastbuild and performances
Date: 2021-10-08 11:14:23
Message-ID: CAPE8EZ44YNsT52dJwrqjfXLLaK-3LeZOozZ4vJ1pCNcf9O_0Ow@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Olarte 2021-10-08 11:42:02 Re: Gist fastbuild and performances
Previous Message huangning290@yahoo.com 2021-10-08 01:04:43 Re: create a new GIN index for my own type