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)
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 |