Re: I don't understand that EXPLAIN PLAN timings

On Fri, 26 Jan 2024 at 02:31, Jean-Christophe Boggio
<postgresql@thefreecat.org> wrote:
> You are absolutely correct : the EXPLAIN without ANALYZE gives about the same results. Also, minimizing the amount of
workmemin postgresql.conf changes drastically the timings. So that means memory allocation is eating up a lot of time
_PER_QUERY_? 

We do reuse pallocs to create memory context, but only for I believe
1k and 8k blocks.  That likely allows most small allocations in the
executor to be done without malloc.  Speaking in vague terms as I
don't have the exact numbers to hand, but larger allocations will go
directly to malloc.

There was a bug fixed in [1] that did cause behaviour like this, but
you seem to be on 14.10 which will have that fix.  Also, the 2nd plan
you sent has no Memoize nodes.

I do wonder now if it was a bad idea to make Memoize build the hash
table on plan startup rather than delaying that until we fetch the
first tuple. I see Hash Join only builds its table during executor
run.

> Since we have quite some RAM on our machines, I dedicated as much as possible to workmem (initially I was allocating
1GB)but this looks quite counterproductive (I didn't think that memory was allocated every time, I thought it was
"available"for the current query but not necessarily used). Is this an issue specific to that version of PostgreSQL? (I
guessno) Or can this be hardware-related? Or OS-related (both systems on which I have done tests are running Ubuntu, I
willtry on Debian)? 

It would be good to narrow down which plan node is causing this.  Can
you try disabling various planner enable_* GUCs before running EXPLAIN
(SUMMARY ON) <your query> with \timing on and see if you can find
which enable_* GUC causes the EXPLAIN to run more quickly?  Just watch
out for variations in the timing of "Planning Time:". You're still
looking for a large portion of time not accounted for by planning
time.

I'd start with:

SET enable_memoize=0;
EXPLAIN (SUMMARY ON) <your query>;
RESET enable_memoize;

SET enable_hashjoin=0;
EXPLAIN (SUMMARY ON) <your query>;
RESET enable_hashjoin;

The following will show others that you could try.
select name,setting from pg_settings where name like 'enable%';

David

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1e731ed12aa