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

Hi,

On 2023-03-28 17:28:04 +1100, Maxim Boguk wrote:
> 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)

That's not really related to JIT - when there are no dropped columns we return
"physical tuples" from e.g. a sequential scan. Physical tuples being tuples
just as they are in the table. But if there are dropped columns, we need to
project, to "filter out" the dropped column.

postgres[3877072][1]=# SHOW max_parallel_workers_per_gather ;
┌─────────────────────────────────┐
│ max_parallel_workers_per_gather │
├─────────────────────────────────┤
│ 0 │
└─────────────────────────────────┘
(1 row)

postgres[3877072][1]=# ;DROP TABLE large;CREATE TABLE large(id int8, dropme int default 0, data text);INSERT INTO large(id, data) SELECT g.i, g.i FROM generate_series(1, 5000000) g(i);

postgres[3877072][1]=# EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM large ;
┌───────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Aggregate (cost=76830.89..76830.90 rows=1 width=8) (actual rows=1 loops=1) │
│ -> Seq Scan on large (cost=0.00..67834.11 rows=3598711 width=0) (actual rows=5000000 loops=1) │
│ Planning Time: 0.076 ms │
│ Execution Time: 325.636 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────┘
(4 rows)
(repeat a couple times)

ALTER TABLE large DROP COLUMN dropme;

postgres[3877072][1]=# EXPLAIN (ANALYZE, TIMING OFF) SELECT count(*) FROM large ;
┌───────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Aggregate (cost=79617.50..79617.51 rows=1 width=8) (actual rows=1 loops=1) │
│ -> Seq Scan on large (cost=0.00..70063.40 rows=3821640 width=0) (actual rows=5000000 loops=1) │
│ Planning Time: 0.079 ms │
│ Execution Time: 365.530 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────┘
(4 rows)

After the DROP COLUMN the time is consistently higher.

JIT is used for projections. When we just use physical tuples, there's no
projection. Hence no JIT for a SELECT * FROM tbl; when there's no dropped
columns.

Greetings,

Andres Freund