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

On Tue, Mar 28, 2023 at 5:21 PM Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:

>
>
> On Tue, Mar 28, 2023 at 5:13 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
>> On Tue, 28 Mar 2023 at 17:54, Maxim Boguk <maxim(dot)boguk(at)gmail(dot)com> wrote:
>> > 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
>>
>> It could be something like a dropped column forcing projection of the
>> SELECT *. You might want to try without SELECT * using some subset of
>> columns instead.
>>
>> Check if columns have been dropped with:
>>
>> select attnum,attname from pg_Attribute where
>> attrelid='interview_review_info_archive_p028'::Regclass;
>>
>> David
>>
>
> Hi David,
>
> You 100% right
> negotiation_chat_archive=# explain select
> topic_id,review_id,move_to_invitation_state_time,no_interview_reply_time,review_suggestion_chat_message_creation_time
> 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)
>
> Exceptionally curious case when select * not equivalent select
> list_all_columns from table...
> Thank you.
>

The small update - previous explain was wrong (I forgot enable jit)
but general explanation correct
dropping column from test table forcing jit during explain:

set JIT to on;
alter table tables_to_drop.test drop column move_to_invitation_state_time;
vacuum ANALYZE tables_to_drop.test;

negotiation_chat_archive=# explain select * from tables_to_drop.test;
QUERY PLAN

---------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..361966.20 rows=34567620 width=32)
JIT:
Functions: 2
Options: Inlining false, Optimization false, Expressions true, Deforming
true
(4 rows)

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

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