From: Jean-Christophe Boggio <postgresql@thefreecat.org>
To: pgsql-performance@lists.postgresql.org
Subject: Re: I don't understand that EXPLAIN PLAN timings
Date: Fri, 26 Jan 2024 05:22:58 +0100
Message-ID: <a09afdae-c2c2-48b3-982f-1e44f2bb31cd@thefreecat.org> (raw)
In-Reply-To: <61fab101-c7f1-4a88-8099-5f68bfd4f99d@thefreecat.org>
References: <61e642df-5f48-4e4e-b4c3-58936f90ddaa@thefreecat.org>
<7c265c1a-2b87-4dc6-8d83-8c7d98e11a2c@thefreecat.org>
<CAApHDvqX2qTihLG2K4OqzeRneg3h+bwdgL000SjYHPFKiU7eAA@mail.gmail.com>
<0f1acdd6-1d6a-44bc-80a1-fde4c55c5263@thefreecat.org>
<CAApHDvqoP04PS3g6+Wc4LvMEcvbre+iRiVmyph1U2jVJtTJBng@mail.gmail.com>
<61fab101-c7f1-4a88-8099-5f68bfd4f99d@thefreecat.org>
Hello,
In case it might be useful, I made some more tests.
On my dev computer (a notebook) I installed:
PostgreSQL 15.5 (Ubuntu 15.5-1.pgdg23.10+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 13.2.0-4ubuntu3) 13.2.0, 64-bit
and
PostgreSQL 16.1 (Ubuntu 16.1-1.pgdg23.10+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 13.2.0-4ubuntu3) 13.2.0, 64-bit
I adjusted work_mem to 1GB and disabled JIT, restored the same DB, did
VACUUM ANALYZE and ran the query several times to lower I/O interference.
Execution time is about the same on PG 14, 15 and 16, around ~120ms
I noticed that planning time, whatever the version, is very variable
between executions (ranging from 0.120ms to 0.400ms), probably due to
other programs activity and <1ms measurements imprecision. So the
results I gave you in my previous email are probably irrelevant.
On our production server, which is running PG 14.10 on Debian 11, same
work_mem, execution time is ~45ms but planning time is much more
consistent at ~0.110ms
Interestingly though, lowering work_mem to 32MB gives 22ms execution
time but planning time rises consistently at ~0.7ms
On my notebook with work_mem=32MB, execution time is also 22ms but
planning time is lower at ~0.4ms (?!?)
Let me know if I can do anything to provide you with more useful
benchmark. The DB is still very small so it is easy to do tests.
JC
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: postgresql@thefreecat.org, pgsql-performance@lists.postgresql.org
Subject: Re: I don't understand that EXPLAIN PLAN timings
In-Reply-To: <a09afdae-c2c2-48b3-982f-1e44f2bb31cd@thefreecat.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox