From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: maxim.boguk@gmail.com
Subject: BUG #17871: JIT during postgresql_fdw remote_estimates EXPLAIN have very negatively effect on planning time
Date: Mon, 27 Mar 2023 08:04:59 +0000
Message-ID: <17871-16521a70c16cb83c@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17871
Logged by: Maxim Boguk
Email address: maxim.boguk@gmail.com
PostgreSQL version: 13.9
Operating system: Linux
Description:
Issue:
postgresql_fdw remote estimated explain calls could trigger JIT compilation
on the remote side (why explain without analyze trying use JIT at all???),
and with partitioned tables it will lead to very slow planning.
In my case simple query over FDW table have planning time over 150ms with
jit=on on remote side:
explain analyze select * from cold_replica_fdw.interview_review_info_archive
order by topic_id limit 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on interview_review_info_archive (cost=14.10..14.14 rows=1
width=40) (actual time=3.942..4.526 rows=1 loops=1)
Planning Time: 162.721 ms
Execution Time: 5.226 ms
And only 15ms with jit=off on remote side:
explain analyze select * from cold_replica_fdw.interview_review_info_archive
order by topic_id limit 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on interview_review_info_archive (cost=14.10..14.14 rows=1
width=40) (actual time=3.724..4.381 rows=1 loops=1)
Planning Time: 14.655 ms
Execution Time: 5.048 ms
Checking what's going on the remote side leads to the following results:
remote estimate explain calls from fdw with jit=on
[EXPLAIN] LOG: duration: 97.050 ms statement: EXPLAIN SELECT topic_id,
review_id, move_to_invitation_state_time, no_interview_reply_time,
review_suggestion_chat_message_creation_time FROM
public.interview_review_info_archive
vs
remote estimate explain calls from fdw with jit=off
[EXPLAIN] LOG: duration: 3.343 ms statement: EXPLAIN SELECT topic_id,
review_id, move_to_invitation_state_time, no_interview_reply_time,
review_suggestion_chat_message_creation_time FROM
public.interview_review_info_archive
Reason with jit=on the database spent a lot of time on the:
JIT:
Functions: 200
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Possible solutions:
band aid: postgresql fdw should invoke set jit to
'off'; when doing remote estimates via explain calls.
probably more correct: explain (without analyze) should not invoke JIT code
path at all (because the database not going to execute query anyway, so
there are no profit from JIT could be gained).
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-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, maxim.boguk@gmail.com
Subject: Re: BUG #17871: JIT during postgresql_fdw remote_estimates EXPLAIN have very negatively effect on planning time
In-Reply-To: <17871-16521a70c16cb83c@postgresql.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