I have an optimization problem when using a recursive view on PostgreSQL. When I perform a trivial query using this view, the execution time is abnormally long.

To expose my problem, here is a database, with the view and the query that causes the problem: http://sqlfiddle.com/#!17/9d39e/13

The main table is v_univ_st and the view is called v_univ_bf.


I tried the EXPLAIN method of PostgreSQL, here is the result I get (the table I'm working on is much bigger than the fiddle one):

"Hash Right Join  (cost=4724036209.97..4757553915.67 rows=1510012 width=23746) (actual time=5172.917..24833.100 rows=1869 loops=1)"
"  Hash Cond: ((recipes_flat."PRODUCT_ID")::text = (u_sample_tasks."PRODUCT_ID")::text)"
"  ->  CTE Scan on recipes_flat  (cost=4723991547.19..4728513371.25 rows=164429966 width=23705) (actual time=0.197..19761.488 rows=367312 loops=1)"
"        CTE recipes_flat"
"          ->  Recursive Union  (cost=0.00..4723991547.19 rows=164429966 width=15645) (actual time=0.181..17845.438 rows=367312 loops=1)"
"                ->  Seq Scan on v_univ_st  (cost=0.00..8024046.44 rows=279636 width=1824) (actual time=0.171..3060.524 rows=279684 loops=1)"
"                      SubPlan 1"
"                        ->  Aggregate  (cost=28.54..28.55 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=279684)"
"                              ->  Index Only Scan using idx_recipe_blends_ingredient_id on v_univ_st sr  (cost=0.42..28.52 rows=6 width=0) (actual time=0.004..0.006 rows=0 loops=279684)"
"                                    Index Cond: ("INGREDIENT_ID" = (v_univ_st."PRODUCT_ID_COMP")::text)"
"                                    Heap Fetches: 68468"
"                ->  Nested Loop  (cost=0.42..471267890.14 rows=16415033 width=15645) (actual time=0.262..799.437 rows=9736 loops=9)"
"                      ->  WorkTable Scan on recipes_flat s  (cost=0.00..55927.20 rows=2796360 width=14718) (actual time=0.010..22.153 rows=40812 loops=9)"
"                      ->  Index Scan using idx_recipe_blends_ingredient_id on v_univ_st e  (cost=0.42..0.58 rows=6 width=999) (actual time=0.005..0.008 rows=0 loops=367312)"
"                            Index Cond: (("INGREDIENT_ID")::text = (s."PRODUCT_ID_COMP")::text)"
"                      SubPlan 2"
"                        ->  Aggregate  (cost=28.54..28.55 rows=1 width=0) (actual time=0.038..0.038 rows=1 loops=87628)"
"                              ->  Index Only Scan using idx_recipe_blends_ingredient_id on v_univ_st sr_1  (cost=0.42..28.52 rows=6 width=0) (actual time=0.007..0.037 rows=0 loops=87628)"
"                                    Index Cond: ("INGREDIENT_ID" = (e."PRODUCT_ID_COMP")::text)"
"                                    Heap Fetches: 19160"
"  ->  Hash  (cost=44661.15..44661.15 rows=131 width=9) (actual time=4983.487..4983.487 rows=129 loops=1)"
"        Buckets: 1024  Batches: 1  Memory Usage: 14kB"
"        ->  Seq Scan on u_sample_tasks  (cost=0.00..44661.15 rows=131 width=9) (actual time=954.011..4983.337 rows=129 loops=1)"
"              Filter: (("EXAMPLE")::text = 'EXAMPLE'::text)"
"              Rows Removed by Filter: 365517"
"Planning time: 193.957 ms"
"Execution time: 24903.973 ms"

As shown by https://explain.depesz.com/:

EXPLAIN result

Indexes are used, I already tried:

SET enable_seqscan = OFF
SET enable_nestloop = OFF

but it doesn't improve, the result is even worse.

Here is the table on dbfiddle.uk. I use version 9.6.

On this small database the results are not slow. On PostgreSQL the table is 261MB and about 279,000 rows.

v_univ_st is the table itself, which has no primary key indeed, but I work on tables that are not necessarily "relational" but rather data extraction. The view created is v_univ_bf and it is during this creation that I want to create columns that gives me the "depth" level of the ingredient. In the simplified example I recursively go through the table to get this information.

  • I've put some ORDER BYs in the fiddle. Could you explain (in the text of your question) how you get from your table to your view for the cakes 6A & 6B - it's not very clear! – Vérace Jun 4 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.