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

On Tue, Mar 28, 2023 at 1:53 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > 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
>
> Do you see the same discrepancy when you execute EXPLAIN manually
> on the remote side? If so, I wouldn't blame postgres_fdw for it.
>
> I suppose interview_review_info_archive is a view not a plain table?
> In either case, could we see the DDL definition for it?
>
> regards, tom lane
>

Hi,

Yes interview_review_info_archive is not a normal table by natively
partitioned by range table with 100 partition:

negotiation_chat_archive=# \d+ interview_review_info_archive
Partitioned table
"public.interview_review_info_archive"
Column | Type
| Collation | Nullable | Default | Storage | Stats target | Description
----------------------------------------------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
topic_id | bigint
| | not null | | plain | |
review_id | bigint
| | | | plain | |
move_to_invitation_state_time | timestamp without time zone
| | | | plain | |
no_interview_reply_time | timestamp without time zone
| | | | plain | |
review_suggestion_chat_message_creation_time | timestamp without time zone
| | | | plain | |
Partition key: RANGE (topic_id)
Indexes:
"interview_review_info_archive_pkey" PRIMARY KEY, btree (topic_id)
Partitions: interview_review_info_archive_p001 FOR VALUES FROM ('0') TO
('100000000'),
...
interview_review_info_archive_p100 FOR VALUES FROM
('9900000000') TO ('10000000000')

When I run EXPLAIN locally there are the same differences.
Under normal circumstances this behavior is not an issue because EXPLAIN is
only executed by DBA, but with postgresql_fdw issues 2-3 EXPLAIN per each
basic fdw query this behavior (triggering JIT on explain) has a very
negative effect on performance.

Simplest case show huge difference in theEXPLAIN performance with and
without JIT:
negotiation_chat_archive=# set jit to on;
SET
negotiation_chat_archive=# explain select * FROM
public.interview_review_info_archive;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=100.00..4602434.79 rows=354847702 width=40)
Workers Planned: 7
-> Parallel Append (cost=0.00..1053857.77 rows=50692498 width=40)
-> Parallel Seq Scan on interview_review_info_archive_p030
interview_review_info_archive_30 (cost=0.00..200959.41 rows=15759281
width=40)
...
-> Parallel Seq Scan on interview_review_info_archive_p005
interview_review_info_archive_5 (cost=0.00..0.11 rows=1 width=40)
-> Parallel Seq Scan on interview_review_info_archive_p009
interview_review_info_archive_9 (cost=0.00..0.11 rows=1 width=40)
(103 rows)
JIT:
Functions: 200
Options: Inlining true, Optimization true, Expressions true, Deforming
true
(106 rows)
Time: 33.664 ms
PS: this sample contradicts Andreas' statement about "It should not trigger
all of JIT, just generating the bitcode, but not optimizing / emitting it."
(or I read EXPLAIN output wrong.)

vs
negotiation_chat_archive=# explain select * FROM
public.interview_review_info_archive;
...
-> Parallel Seq Scan on interview_review_info_archive_p005
interview_review_info_archive_5 (cost=0.00..0.11 rows=1 width=40)
-> Parallel Seq Scan on interview_review_info_archive_p009
interview_review_info_archive_9 (cost=0.00..0.11 rows=1 width=40)
(103 rows)
Time: 3.392 ms

--
Maxim Boguk
Senior Postgresql DBA
https://dataegret.com/

Phone UA: +380 99 143 0000
Phone AU: +61 45 218 5678