I encountered an issue at a certain arrangement of cte <-> view <-> select with where in.
PostgreSQL Version: PostgreSQL 13.3
I’ve created a minimal example to show the behaviour:
- a table source_table_test_performance containing one column with data type int8 (with index on that column) (size 10GB)
- a view on the table (the with-statement in the view is absolutely useless and not used in the view itself)
(1)
create view public.view_test_performance as (
with cte_in_view as (
select 1
)
select id_col from public.source_table_test_performance
);
- a table with a small subset of source_table_test_performance called filter_table_test_performance with only 7 entries
- a select statement
(2)
select count(*)
from public.view_test_performance
where id_col in (select id_col from public.filter_table_test_performance)
The strange behaviour is:
- (a) With the useless with statement in the create-view-statement (1) the query (2) takes 36s.
- (b) Without the with-statement in (1) it takes 0.047s (~ factor 1000 faster).
create view public.view_test_performance as (
select id_col from public.source_table_test_performance
);
- (c) With the useless with statement in the create-view-statement (1), but replacing where id_col in (select id_col from public.filter_table_test_performance) in (2) by the list of selected numbers where id_col in (34824,34823,34825,34819,34820,34821,34822) the query takes 0.0031s (~ factor 1000 faster)
The query plan for (a) is:
Finalize Aggregate (cost=5411092.65..5411092.66 rows=1 width=8)
-> Gather (cost=5411092.44..5411092.65 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=5410092.44..5410092.45 rows=1 width=8)
-> Hash Semi Join (cost=1.16..5408696.55 rows=558354 width=0)
Hash Cond: (source_table_test_performance.id_col = filter_table_test_performance.id_col)
-> Parallel Seq Scan on source_table_test_performance (cost=0.00..2184175.00 rows=105927800 width=8)
-> Hash (cost=1.07..1.07 rows=7 width=8)
-> Seq Scan on filter_table_test_performance (cost=0.00..1.07 rows=7 width=8)
JIT:
Functions: 12
Options: Inlining true, Optimization true, Expressions true, Deforming true
The query plan for (b) is:
Aggregate (cost=41451.90..41451.91 rows=1 width=8)
-> Nested Loop (cost=1.66..38101.78 rows=1340050 width=0)
-> HashAggregate (cost=1.09..1.16 rows=7 width=8)
Group Key: filter_table_test_performance.id_col
-> Seq Scan on filter_table_test_performance (cost=0.00..1.07 rows=7 width=8)
-> Index Only Scan using source_table_test_performance_id_col_idx on source_table_test_performance (cost=0.57..3528.59 rows=191436 width=8)
Index Cond: (id_col = filter_table_test_performance.id_col)
The query plan for (c) is:
Aggregate (cost=13879.69..13879.70 rows=1 width=8)
-> Index Only Scan using source_table_test_performance_id_col_idx on source_table_test_performance (cost=0.57..8274.60 rows=448407 width=8)
Index Cond: (id_col = ANY ('{34824,34823,34825,34819,34820,34821,34822}'::bigint[]))
Can someone help me to understand what is causing the difference in performance between (a) and (b) (and why it’s working fine at (c))?
Thanks in advance Andreas