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

Hi,

During an attempt to create a minimal test case I discovered an effect for which I have no explanation:

(all tests with jit=on):

Run EXPLAIN on the single partition, so far expected results...

explain select * from interview_review_info_archive_p028;
                                          QUERY PLAN                                          
-----------------------------------------------------------------------------------------------
 Seq Scan on interview_review_info_archive_p028  (cost=0.00..361819.08 rows=34546848 width=40)
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true

Prepare stand alone copy table with all data:

create table tables_to_drop.test as select * from interview_review_info_archive_p028;

vacuum ANALYZE tables_to_drop.test;

explain select * from tables_to_drop.test;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on test  (cost=0.00..361908.72 rows=34562312 width=40)

The same cost/same data/same query/same structure but no JIT triggered?

Add the same indexes as exists on interview_review_info_archive_p028:

alter table tables_to_drop.test add constraint test_pk primary key (topic_id);

create unique index test_uniq on tables_to_drop.test(review_id, topic_id) WHERE review_id IS NOT NULL);

vacuum ANALYZE tables_to_drop.test;

And still no JIT triggered during EXPLAIN.

Making sure that the both tables contain exactly the same data in exactly the same order:

cluster tables_to_drop.test using test_pk;

analyze verbose tables_to_drop.test;

cluster interview_review_info_archive_p028 using interview_review_info_archive_p028_pkey;

analyze verbose interview_review_info_archive_p028;

No changes:

negotiation_chat_archive=# explain select * from interview_review_info_archive_p028;
                                          QUERY PLAN                                          
-----------------------------------------------------------------------------------------------
 Seq Scan on interview_review_info_archive_p028  (cost=0.00..361921.00 rows=34563080 width=40)
 JIT:
   Functions: 2
   Options: Inlining true, Optimization true, Expressions true, Deforming true

negotiation_chat_archive=# explain select * from tables_to_drop.test;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on test  (cost=0.00..361922.08 rows=34563208 width=40)

Add check constraint on test table similar to exists on partition interview_review_info_archive_p028:

alter table tables_to_drop.test add constraint test_chk check ((topic_id IS NOT NULL) AND (topic_id >= '2700000000'::bigint) AND (topic_id < '2800000000'::bigint));

And still not JIT on standalone table.

Final structure of tables:

negotiation_chat_archive=# \d+ interview_review_info_archive_p028
                                                 Table "public.interview_review_info_archive_p028"
                    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 of: interview_review_info_archive FOR VALUES FROM ('2700000000') TO ('2800000000')
Partition constraint: ((topic_id IS NOT NULL) AND (topic_id >= '2700000000'::bigint) AND (topic_id < '2800000000'::bigint))
Indexes:
    "interview_review_info_archive_p028_pkey" PRIMARY KEY, btree (topic_id) CLUSTER
    "interview_review_info_archive_p028_review_id_topic_id_idx" UNIQUE, btree (review_id, topic_id) WHERE review_id IS NOT NULL
Access method: heap

vs

negotiation_chat_archive=# \d+  tables_to_drop.test
                                                            Table "tables_to_drop.test"
                    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   |              |
Indexes:
    "test_pk" PRIMARY KEY, btree (topic_id) CLUSTER
    "test_uniq" UNIQUE, btree (review_id, topic_id) WHERE review_id IS NOT NULL
Check constraints:
    "test_chk" CHECK (topic_id IS NOT NULL AND topic_id >= '2700000000'::bigint AND topic_id < '2800000000'::bigint)
Access method: heap

Only difference is

Partition of: interview_review_info_archive FOR VALUES FROM ('2700000000') TO ('2800000000')

Somehow being part of partition structure triggering JIT during EXPLAIN even if table used as standalone (e.g. called directly not via partition root) in query.