Postgres tuning a query with RECURSIVE CTE

Question

I use PostgreSQL 13.5. There is recursive cte query:

-- query1
with recursive cte AS
(
   SELECT * FROM ad_definition def WHERE def.ad_definition_id = 'BASIC'
   UNION ALL
   SELECT def.* FROM ad_definition def JOIN cte c ON c.ad_definition_id = def.ad_definition_parent_id
) SELECT * FROM cte

it returns 2 rows ('BASIC', 'EXTENDED')

when I use the result in the next query execution is fast

-- query2
explain 
SELECT * FROM ad_definition d
join ad on ad.ad_definition_id = d.ad_definition_id
WHERE d.ad_definition_name in ('BASIC', 'EXTENDED')

execution plan shows the Index Scan is used:

Nested Loop  (cost=0.43..225244.60 rows=1757853 width=241)
  ->  Seq Scan on ad_definition d  (cost=0.00..2.15 rows=2 width=76)
        Filter: ((ad_definition_name)::text = ANY ('{BASIC,EXTENDED}'::text[]))
  ->  Index Scan using i_ad_ad_definition_id on ad  (cost=0.43..91526.98 rows=2109424 width=165)
        Index Cond: (ad_definition_id = d.ad_definition_id)

but when I join both queries into one

-- query3
explain with recursive cte AS (
   SELECT * FROM ad_definition def WHERE def.ad_definition_id = 1000
   UNION ALL
   SELECT def.* FROM ad_definition def JOIN cte c ON c.ad_definition_id = def.ad_definition_parent_id
) SELECT * FROM cte 
join ad on ad.ad_definition_id = cte.ad_definition_id

results are equal. but the execution is much slower and I see in the execution plan a Seq Scan is used :-(

Hash Join  (cost=30.23..500069.30 rows=10547119 width=731)
  Hash Cond: (ad.ad_definition_id = cte.ad_definition_id)
  CTE cte
    ->  Recursive Union  (cost=0.00..28.57 rows=51 width=76)
        ...
  ->  Seq Scan on ad  (cost=0.00..355016.19 rows=10547119 width=165)
  ->  Hash  (cost=1.02..1.02 rows=51 width=566)
        ->  CTE Scan on cte  (cost=0.00..1.02 rows=51 width=566)

Is it possible to use the CTE and force the index scan at the same time?

The execution plans generated using explain (analyze, buffers, timing) would be more helpful — user330315
– user330315, Commented Oct 14, 2022 at 11:43
It didn't help. doesn't matter with recursive cte AS materialized ( or with recursive cte AS not materialized ( execution plan use Seq Scan — Jan Chmelensky
– Jan Chmelensky, Commented Oct 14, 2022 at 11:45

Ramin Faracov · Accepted Answer · 2022-10-14 20:20:14Z

0

Materialized or not materialized does not affect the use of these indexes. Materialized - just executes the request once, even if it is called multiple times. In your query, the CTE acts as a kind of subquery. You have written a join on table ad after CTE. That is, firstly running query inside the CTE. After then result CTE inserted into a temp table and after then the join is running, so you get seq scan. Probably also the result of CTE is big data. You would be better off putting the join ad on ad.ad_definition_id = cte.ad_definition_id part inside the CTE. If you do it, I think that index scan will be work.

answered Oct 14, 2022 at 20:20

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres tuning a query with RECURSIVE CTE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related