Re: Gist fastbuild and performances

Ben:

On Fri, 8 Oct 2021 at 13:14, talk to ben <blo(dot)talkto(at)gmail(dot)com> wrote:
> I am playing around with the gist fast build and comparing the result between v13 and v14.
...
> 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'm not knowlegeable enough in gist or low level stuff, but I would
suggest you run the same scripts in both, just in case the copy/create
is influencing anything. I mean, once you have the scripts, do ..

> \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
..
> COPY gist_fastbuild TO '/tmp/gist_fastbuild.copy';

On a scratch database, and then run the same script:

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

a couple times or more on each server to rule artifacts out.

( testing speed in general, which such fast times, I myself would
doubt comparisons of anything but exact runs )

It may give the same results, but then you know it does.

Francisco Olarte.