主页 / dba / 问题 / 293768
Accepted
Gabriel Valvin
Asked: 2021-06-05 02:36:24 +0800 CST

递归视图的优化

  • 772

在 PostgreSQL 上使用递归视图时出现优化问题。当我使用这个视图执行一个简单的查询时，执行时间异常长。

为了暴露我的问题，这是一个数据库，其中包含导致问题的视图和查询：http ://sqlfiddle.com/#!17/9d39e/13

主表是v_univ_st，视图被调用v_univ_bf。

解释

我尝试了 PostgreSQL 的 EXPLAIN 方法，这是我得到的结果（我正在处理的表比小提琴表大得多）：

"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"

如https://explain.depesz.com/所示：

解释结果

使用了索引，我已经尝试过：

SET enable_seqscan = OFF
SET enable_nestloop = OFF

但它并没有改善，结果更糟。

这是 dbfiddle.uk 上的表格。我使用的是 9.6 版。

在这个小型数据库上，结果并不慢。在 PostgreSQL 上，该表是 261MB 和大约 279,000 行。

v_univ_st是表本身，它确实没有主键，但我处理的表不一定是“关系”，而是数据提取。创建的视图v_univ_bf是在创建过程中，我想创建列，为我提供成分的“深度”级别。在简化的示例中，我递归地遍历表格以获取此信息。

postgresql query-performance

2 个回答

  1. Best Answer

    通过使用物化视图和EXISTS，我将请求的执行时间降低了大约 96%。我改变了 ：

    CREATE OR REPLACE VIEW public.v_univ_bf

    至

    CREATE MATERIALIZED VIEW public.v_univ_bf

    使用它的每个查询都不会刷新物化视图，如果对表进行了更改，则必须刷新它。

    对于我的问题，我每天都在我的 ETL 中使用查询来刷新物化视图，但这里有一个主题，介绍了刷新物化视图的所有方法。

    • 3

  2. 我看到 OP 已经用物化视图解决了他的问题。

    无论如何，我看到了替换(SELECT COUNT(1) ...)=0为的优化NOT EXISTS：

    
CREATE OR REPLACE VIEW public.v_univ_bf AS 
 WITH RECURSIVE recipes_flat AS (
         SELECT v_univ_st.PRODUCT_ID,

            v_univ_st.NAME_PRODUCT,
            v_univ_st.INGREDIENT_ID,
            v_univ_st.PRODUCT_ID_COMP,
          /*
            (( SELECT count(1) AS count
                   FROM v_univ_st sr
                  WHERE sr.INGREDIENT_ID::text = v_univ_st.PRODUCT_ID_COMP::text)) = 0 AS last_comp_blend,
           */
           NOT EXISTS(
               SELECT 1 FROM v_univ_st sr
                 WHERE sr.INGREDIENT_ID::text = v_univ_st.PRODUCT_ID_COMP::text
             ) AS last_comp_blend,           
           1 AS level_blend
           FROM v_univ_st
        UNION ALL
         SELECT 
            e.PRODUCT_ID,
            s.NAME_PRODUCT,
            s.INGREDIENT_ID,
            e.PRODUCT_ID_COMP,   
             NOT EXISTS(
               SELECT 1 
                   FROM v_univ_st sr
                  WHERE sr.INGREDIENT_ID::text = e.PRODUCT_ID_COMP::text               
             ) AS last_comp_blend,            
/*
            (( SELECT count(1) AS count
                   FROM v_univ_st sr
                  WHERE sr.INGREDIENT_ID::text = e.PRODUCT_ID_COMP::text)) = 0 AS last_comp_blend,
*/                       
            s.level_blend + 1
           FROM v_univ_st e
             JOIN recipes_flat s ON s.PRODUCT_ID_COMP::text = e.INGREDIENT_ID::text
        )
 SELECT recipes_flat.PRODUCT_ID AS id_product,
    recipes_flat.NAME_PRODUCT AS name_product,
    recipes_flat.INGREDIENT_ID AS ingredient_id,
    recipes_flat.PRODUCT_ID_COMP AS product_id_comp,
    recipes_flat.level_blend,
    recipes_flat.last_comp_blend,
        CASE
            WHEN recipes_flat.last_comp_blend AND recipes_flat.level_blend = 1 THEN 'CF'::text
            WHEN recipes_flat.last_comp_blend AND recipes_flat.level_blend > 0 THEN 'F'::text
            WHEN NOT recipes_flat.last_comp_blend AND recipes_flat.level_blend = 1 THEN 'C'::text
            ELSE ''::text
        END AS compact_flat_view
   FROM recipes_flat;
   
 -- EXPLAIN ANALYZE  
 select * from v_univ_bf;

    http://sqlfiddle.com/#!17/9d39e/45

    我很好奇它在多大程度上加快了原始视图或REFRESH MATERIALIZED VIEW真实数据的速度。

    • 0

相关问题