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,

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.

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

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