Original-Subject: Re: I don't understand that EXPLAIN PLAN timings
Original-From: Jean-Christophe Boggio <postgresql@thefreecat.org>
Message-ID: <a09afdae-c2c2-48b3-982f-1e44f2bb31cd@thefreecat.org>
Date: Fri, 26 Jan 2024 05:22:58 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: I don't understand that EXPLAIN PLAN timings
Content-Language: fr-FR, en-US
To: pgsql-performance@lists.postgresql.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>
From: Jean-Christophe Boggio <postgresql@thefreecat.org>
In-Reply-To: <61fab101-c7f1-4a88-8099-5f68bfd4f99d@thefreecat.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/a09afdae-c2c2-48b3-982f-1e44f2bb31cd%40thefreecat.org>
Precedence: bulk

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