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