Postgresql

PostgreSQL 9.1 - 對 VIEWS 的查詢花費大量時間

  • March 13, 2016

使用 PostgreSQL 9.1，我們在 PostgreSQL 的視圖上執行查詢時遇到問題。以下是情況：

我們有一個分區表“buz_scdr”，我們在其上建構了一個視圖“Swiss_client_wise_minutes_and_profit”。此 VIEW 的目的是連接來自不同表（包括“buz_scdr”表）的數據以進行高效查詢。這個策略一直很好，直到表“buz_scdr”變得巨大（所有分區中的整體記錄變得巨大。表是根據日期分區的）。

在這個 VIEW 上執行的查詢開始需要很長時間（大約 5 到 10 分鐘）。為了弄清楚為什麼這個查詢需要這麼長時間來執行，我們使用 EXPLAIN 命令來顯示它的執行計劃。我們使用的查詢如下：

EXPLAIN SELECT * from  "Swiss_client_wise_minutes_and_profit" where start_time = '2012-7-22 08:00';

它的結果在 explain.depesz.com 上或如下：

  Subquery Scan on "Swiss_client_wise_minutes_and_profit"  (cost=2127919.71..94874537.55 rows=40474 width=677)
  Filter: ("Swiss_client_wise_minutes_and_profit".start_time = '2012-07-22 08:00:00+00'::timestamp with time zone)
  ->  WindowAgg  (cost=2127919.71..94773352.06 rows=8094839 width=148)
        ->  Sort  (cost=2127919.71..2148156.81 rows=8094839 width=148)
              Sort Key: cc.name, rdga.group_id
              ->  Hash Left Join  (cost=1661.50..604234.77 rows=8094839 width=148)
                    Hash Cond: (((cc.company_id)::text = (rdga.company_id)::text) AND ((cs.c_prefix_id)::text = (rdga.dest_id)::text))
                    ->  Hash Left Join  (cost=7.88..460615.39 rows=8094839 width=123)
                          Hash Cond: ((cs.client_name_id)::text = (cc."Alias_name")::text)
                          ->  Append  (cost=0.00..349303.48 rows=8094839 width=111)
                                ->  Seq Scan on "Swiss_buz_scdr" cs  (cost=0.00..1.06 rows=1 width=610)
                                      Filter: ((customer_name)::text = 'SSP Root'::text)
                                ->  Seq Scan on scdr_buz__2012_07_11 cs  (cost=0.00..349302.41 rows=8094838 width=111)
                                      Filter: ((customer_name)::text = 'SSP Root'::text)
                          ->  Hash  (cost=5.17..5.17 rows=217 width=24)
                                ->  Seq Scan on "Corporate_companyalias" cc  (cost=0.00..5.17 rows=217 width=24)
                    ->  Hash  (cost=1334.42..1334.42 rows=21280 width=50)
                          ->  Hash Join  (cost=169.56..1334.42 rows=21280 width=50)
                                Hash Cond: ((rdga.company_id)::text = (c.name)::text)
                                ->  Hash Join  (cost=162.68..1034.93 rows=21280 width=50)
                                      Hash Cond: ((rdga.group_id)::text = (rdg.name)::text)
                                      ->  Seq Scan on "RateManagement_destgroupassign" rdga  (cost=0.00..497.35 rows=25935 width=40)
                                      ->  Hash  (cost=123.64..123.64 rows=3123 width=32)
                                            ->  Hash Join  (cost=13.08..123.64 rows=3123 width=32)
                                                  Hash Cond: (rdg.country_id = cc.id)
                                                  ->  Seq Scan on "RateManagement_destinationgroup" rdg  (cost=0.00..65.06 rows=3806 width=26)
                                                  ->  Hash  (cost=7.48..7.48 rows=448 width=14)
                                                        ->  Seq Scan on "Corporate_country" cc  (cost=0.00..7.48 rows=448 width=14)
                                ->  Hash  (cost=4.17..4.17 rows=217 width=16)
                                      ->  Seq Scan on "Corporate_company" c  (cost=0.00..4.17 rows=217 width=16)
        SubPlan 1
          ->  Seq Scan on "Corporate_companyalias" cc  (cost=0.00..5.71 rows=1 width=12)
                Filter: (("Alias_name")::text = (cs.client_name_id)::text)
        SubPlan 2
          ->  Seq Scan on "Corporate_companyalias" cc  (cost=0.00..5.71 rows=1 width=12)
                Filter: (("Alias_name")::text = (cs.vendor_name_id)::text)
(36 rows)

上面的 EXPLAIN 命令的結果表明我們的查詢正在按順序掃描包含總共 8094838 條記錄的“buz_scdr”表（如上所示）。VIEW 上的查詢不遵循“buz_scdr”的分區約束（日期），這導致它掃描整個表。

出於實驗目的，我們直接使用 WHERE 語句以及 date 和 time 對“buz_scdr”表執行查詢，它適當地尊重分區約束並且沒有掃描整個表。這表明直接在分區表上執行的查詢按預期工作，但基於它建構的 VIEW 存在問題。

這是 PostgreSQL 視圖的全球性問題還是我錯過了什麼？

編輯：以下是 VIEW “Swiss_client_wise_minutes_and_profit” 的 DDL

CREATE VIEW "Swiss_client_wise_minutes_and_profit" 
   AS SELECT ROW_NUMBER() OVER (ORDER BY rp.country, rp.destination) 
   As id, (SELECT company_id FROM  "Corporate_companyalias" 
   AS cc WHERE cc."Alias_name" = client_name_id) 
   AS client_name, (SELECT company_id FROM  "Corporate_companyalias" 
   AS cc WHERE cc."Alias_name" = vendor_name_id) AS vendor_name, cs.c_prefix_id 
   AS c_prefix, cs.v_prefix_id AS v_prefix, rp.country, rp.destination, cs.c_total_calls, cs.v_total_calls, cs.successful_calls, cs.billed_duration, cs.v_billed_amount AS cost, cs.c_billed_amount 
   AS revenue, cs.c_pdd AS pdd, cs.profit, cs.start_time, cs.end_time, cs.switch_name FROM "Swiss_buz_scdr" 
   AS cs LEFT JOIN "Corporate_companyalias" AS cc ON cs.client_name_id = cc."Alias_name" LEFT JOIN "RateManagement_prefix_and_client_wise_destinationgroup" 
   AS rp ON rp.client_name = cc.company_id AND rp.prefix = cs.c_prefix_id WHERE cs.customer_name = 'SSP Root';

編輯2：這是“EXPLAIN ANALYZE”命令輸出的連結：

在 pastebin 上解釋分析輸出

它有助於正確格式化查詢以查看發生了什麼。我研究了你的查詢，發現了可疑的 SQL：


CREATE VIEW "Swiss_client_wise_minutes_and_profit" AS
SELECT ROW_NUMBER() OVER (ORDER BY rp.country, rp.destination) AS id
    **, (SELECT company_id
FROM "Corporate_companyalias" AS cc
WHERE cc."Alias_name" = client_name_id) AS client_name
, (SELECT company_id
FROM "Corporate_companyalias" AS cc
WHERE cc."Alias_name" = vendor_name_id) AS vendor_name**
    , cs.c_prefix_id AS c_prefix
    , cs.v_prefix_id AS v_prefix
    , rp.country
    , rp.destination
    , cs.c_total_calls
    , cs.v_total_calls
    , cs.successful_calls
    , cs.billed_duration
    , cs.v_billed_amount AS cost
    , cs.c_billed_amount AS revenue
    , cs.c_pdd AS pdd
    , cs.profit
    , cs.start_time
    , cs.end_time
    , cs.switch_name
FROM   "Swiss_buz_scdr" AS cs
LEFT   JOIN "Corporate_companyalias" AS cc ON cs.client_name_id = cc."Alias_name"
LEFT   JOIN "RateManagement_prefix_and_client_wise_destinationgroup" AS rp
        ON rp.client_name = cc.company_id AND rp.prefix = cs.c_prefix_id
WHERE  cs.customer_name = 'SSP Root';
  • 不要cc在外部和內部使用相同的表別名SELECT。雖然這並不違法，但它有助於混淆你。
  • 如果沒有表限定來引用外部查詢，我不確定列client_name_id和vendor_name_id綁定在哪裡。需要知道表定義，但我懷疑它會導致**CROSS JOINs** - 這可能不是您想要的，也是問題的根源。

我懷疑相關的子查詢可以重寫為普通表達式。也許它需要另一個JOIN. 這是我的 …

受過教育的猜測你真正想要什麼：

CREATE VIEW "Swiss_client_wise_minutes_and_profit" AS
SELECT ROW_NUMBER() OVER (ORDER BY r.country, r.destination) AS id
    , c.company_id AS client_name
    , v.company_id AS vendor_name
    , s.c_prefix_id AS c_prefix
    , s.v_prefix_id AS v_prefix
    , r.country
    , r.destination
    , s.c_total_calls
    , s.v_total_calls
    , s.successful_calls
    , s.billed_duration
    , s.v_billed_amount AS cost
    , s.c_billed_amount AS revenue
    , s.c_pdd AS pdd
    , s.profit
    , s.start_time
    , s.end_time
    , s.switch_name
FROM   "Swiss_buz_scdr" s
LEFT   JOIN "Corporate_companyalias" c ON c."Alias_name" = s.client_name_id
LEFT   JOIN "RateManagement_prefix_and_client_wise_destinationgroup" r
        ON r.client_name = c.company_id AND r.prefix = s.c_prefix_id
LEFT   JOIN "Corporate_companyalias" v ON v."Alias_name" = s.vendor_name_id
WHERE  s.customer_name = 'SSP Root';

旁白：我的目標是使用比"RateManagement_prefix_and_client_wise_destinationgroup". 最好是不需要雙引號的合法小寫名稱。

引用自：https://dba.stackexchange.com/questions/21389

相關問答

Postgresql

如果我將它分成 3 個較小的查詢，為什麼我的大 Postgres 查詢會更快？

  • August 21, 2022
檢視問答
Postgresql

基本子查詢評估

  • August 20, 2021
檢視問答
Postgresql

Postgresql 多次擴展視圖而不是執行一次

  • November 28, 2019
檢視問答
Postgresql

更新分區表時的次優查詢計劃

  • February 4, 2019
檢視問答
Postgresql

如何在不重複子查詢的情況下進行減法？

  • July 20, 2018
檢視問答
Postgresql

如何在不失去屬性的情況下有效地使用 GROUP BY？

  • July 20, 2018
檢視問答