Re: BUG #17871: JIT during postgresql_fdw remote_estimates EXPLAIN have very negatively effect on planning time - Mailing list pgsql-bugs

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.)

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