添加where子句和order减慢视图速度

Question

我有这样的观点，那就是对另一个函数使用横向。查询运行良好且快速，但一旦添加条件，where子句和order。它会爬行。

CREATE OR REPLACE VIEW public.vw_top_info_v1_0
 AS
 SELECT pse.symbol,
    pse.order_book,
    pse.company_name,
    pse.logo_url,
    pse.display_logo,
    pse.base_url,
    stats.value::numeric(20,4) AS stock_value,
    stats.volume::numeric(20,0) AS volume,
    stats.last_trade_price,
    stats.stock_date AS last_trade_date
   FROM ( SELECT pse_1.symbol,
            pse_1.company_name,
            pse_1.order_book,
            pse_1.display_logo,
            pse_1.base_url,
            pse_1.logo_url
           FROM vw_pse_traded_companies pse_1
          WHERE pse_1.group_name::text = 'N'::text) pse,
    LATERAL iq_get_stats_security_for_top_data_v1_0(pse.order_book, (( SELECT date(d.added_date) AS date
           FROM prod_itchbbo_p_small_message d
          ORDER BY d.added_date DESC
         LIMIT 1))::timestamp without time zone) stats(value, volume, stock_date, last_trade_price)
  WHERE stats.value IS NOT NULL
  ORDER BY stats.value DESC;***

这是解释输出。

Subquery Scan on vw_top_info_v1_0  (cost=161022.59..165450.34 rows=354220 width=192)
  ->  Sort  (cost=161022.59..161908.14 rows=354220 width=200)
        Sort Key: stats.value DESC
        InitPlan 1 (returns $0)
          ->  Limit  (cost=49734.18..49734.18 rows=1 width=12)
                ->  Sort  (cost=49734.18..51793.06 rows=823553 width=12)
                      Sort Key: d.added_date DESC
                      ->  Seq Scan on prod_itchbbo_p_small_message d  (cost=0.00..45616.41 rows=823553 width=12)
        ->  Nested Loop  (cost=188.59..10837.44 rows=354220 width=200)
              ->  Sort  (cost=188.34..189.23 rows=356 width=2866)
                    Sort Key: info.order_book, listed.symbol
                    ->  Hash Join  (cost=18.19..173.25 rows=356 width=2866)
                          Hash Cond: ((info.symbol)::text = (listed.symbol)::text)
                          ->  Seq Scan on prod_stock_information info  (cost=0.00..151.85 rows=1220 width=12)
                                Filter: ((group_name)::text = 'N'::text)
                          ->  Hash  (cost=13.64..13.64 rows=364 width=128)
                                ->  Seq Scan on prod_pse_listed_companies listed  (cost=0.00..13.64 rows=364 width=128)
              ->  Function Scan on iq_get_stats_security_for_top_data_v1_0 stats  (cost=0.25..10.25 rows=995 width=32)
                    Filter: (value IS NOT NULL)

有改进查询的方法吗？

Answer 1

我不完全明白这是在做什么，但是从prod_itchbbo_p_small_message上的seq扫描到按日期排序以找到最大值是一个很大的成本。

您在添加排序时指出了成本更改，因此如果没有，我将在prod_itchbbo_p_small_message.added_date上添加一个b树索引。