With statement innerhalb eines create view statement zerschießt den Query Plan

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

The execution plan generated using explain (analyze, buffers) is typically more helpful to identify bottlenecks.

But to give you a proper answer, the real queries would be needed.

As written, the only answer I can give is: “then remove the useless CTE if it makes the query slow

1 „Gefällt mir“

Thank you for the advice with explain (analyze,buffers). I will send the results quite soon.
I just want to understand, what happens there (I’ve already implemented a workaround to get it fast). So the real queries are not necessary.

explain (analyze,buffers) gives for (a):

Finalize Aggregate  (cost=5411089.70..5411089.71 rows=1 width=8) (actual time=39655.062..39660.958 rows=1 loops=1)
  Buffers: shared hit=169 read=1124898
  ->  Gather  (cost=5411089.48..5411089.69 rows=2 width=8) (actual time=39654.902..39660.944 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=169 read=1124898
        ->  Partial Aggregate  (cost=5410089.48..5410089.49 rows=1 width=8) (actual time=39638.893..39638.896 rows=1 loops=3)
              Buffers: shared hit=169 read=1124898
              ->  Hash Semi Join  (cost=1.16..5408693.60 rows=558354 width=0) (actual time=34939.113..39637.700 rows=23980 loops=3)
                    Hash Cond: (source_table_test_performance.id_col = fttp.id_col)
                    Buffers: shared hit=169 read=1124898
                    ->  Parallel Seq Scan on source_table_test_performance  (cost=0.00..2184174.27 rows=105927727 width=8) (actual time=0.493..33138.556 rows=84742180 loops=3)
                          Buffers: shared read=1124897
                    ->  Hash  (cost=1.07..1.07 rows=7 width=8) (actual time=89.298..89.299 rows=7 loops=3)
                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
                          Buffers: shared hit=2 read=1
                          ->  Seq Scan on filter_table_test_performance fttp  (cost=0.00..1.07 rows=7 width=8) (actual time=0.163..0.169 rows=7 loops=3)
                                Buffers: shared hit=2 read=1
Planning:
  Buffers: shared hit=30 read=1
Planning Time: 2.019 ms
JIT:
  Functions: 32
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 2.408 ms, Inlining 147.581 ms, Optimization 75.263 ms, Emission 43.991 ms, Total 269.243 ms
Execution Time: 39681.463 ms

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.