Gist fastbuild and performances

Поиск
Список
Период
Сортировка
От talk to ben
Тема Gist fastbuild and performances
Дата
Msg-id CAPE8EZ44YNsT52dJwrqjfXLLaK-3LeZOozZ4vJ1pCNcf9O_0Ow@mail.gmail.com
обсуждение исходный текст
Ответы Re: Gist fastbuild and performances  (Francisco Olarte <folarte@peoplecall.com>)
Список 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 

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)

В списке pgsql-general по дате отправления:

Предыдущее
От: "huangning290@yahoo.com"
Дата:
Сообщение: Re: create a new GIN index for my own type
Следующее
От: Francisco Olarte
Дата:
Сообщение: Re: Gist fastbuild and performances