BUG #17871: JIT during postgresql_fdw remote_estimates EXPLAIN have very negatively effect on planning time

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: maxim(dot)boguk(at)gmail(dot)com
Subject: BUG #17871: JIT during postgresql_fdw remote_estimates EXPLAIN have very negatively effect on planning time
Date: 2023-03-27 08:04:59
Message-ID: 17871-16521a70c16cb83c@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17871
Logged by: Maxim Boguk
Email address: maxim(dot)boguk(at)gmail(dot)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).

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrey Lizenko 2023-03-27 09:17:46 Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
Previous Message PG Bug reporting form 2023-03-27 07:35:39 BUG #17870: Analyze on remote postgresql_fdw table never finish