Postgresql

Postgres優化嵌套存在

  • January 9, 2017

我有一個事件表，每個事件都在一個時間範圍內發生，我正在嘗試為查詢創建一個模板，該模板可以找到在指定時間段內發生的相關事件，這些事件又可以指定為發生在其他事件的時間段內。我還需要能夠將布爾表達式應用於這些約束。

我認為最好的方法是使用嵌套的存在語句，一個用於事件圖中的每個事件。這至少比使用DISTINCT.

我讀過 Postgres 優化器非常聰明，所以與其嘗試自己解析和優化布爾表達式，我希望能夠將所有約束放入嵌套查詢的底部並讓優化器完成大部分工作舉重。雖然簡單、不相交的邏輯很容易解析，但更複雜的邏輯基本上需要編寫一個小型優化器或在不同的邏輯形式和相應的查詢之間任意選擇。

不幸的是，優化器似乎並沒有按照我希望的方式進行優化。即使是最簡單的形式，有直接的AND陳述和相對較少的條件，條件也沒有得到提升。

這是我如何生成查詢和解釋的範例。CTE 為這個特定的查詢提供了顯著的性能提升，據我所知，對於這種類型的查詢，timerange 列上的索引無論如何都不太可能使用：

EXPLAIN ANALYZE
WITH event AS (
 SELECT *,
 int4range(
   lower(event.tickrange),
   upper(event.tickrange) + 500
 ) AS newrange
 FROM event
)
SELECT event_1.id AS event_1_id
FROM event AS event_1
WHERE EXISTS (
 SELECT 1
 FROM event AS event_2
 WHERE EXISTS (
   SELECT 1
   FROM event AS event_3
   WHERE event_1.a_id = 189
   AND event_3.tickrange <@ event_2.newrange
   AND event_1.id != event_3.id
   AND event_2.id != event_3.id
   AND event_2.tickrange <@ event_1.newrange
   AND event_1.id != event_2.id
 )
)

Nested Loop Semi Join  (cost=117.64..51683659.30 rows=2141 width=4) (actual time=0.075..11060.537 rows=158 loops=1)
 Join Filter: (SubPlan 2)
 Rows Removed by Join Filter: 17782972
 CTE event
   ->  Seq Scan on event  (cost=0.00..117.64 rows=4282 width=30) (actual time=0.033..9.446 rows=4282 loops=1)
 ->  CTE Scan on event event_1  (cost=0.00..85.64 rows=4282 width=40) (actual time=0.039..0.992 rows=4282 loops=1)
 ->  CTE Scan on event event_2  (cost=0.00..85.64 rows=4282 width=68) (actual time=0.000..0.541 rows=4153 loops=4282)
 SubPlan 2
   ->  Result  (cost=0.01..117.76 rows=21 width=0) (actual time=0.000..0.000 rows=0 loops=17783130)
         One-Time Filter: ((event_1.a_id = 189) AND (event_2.tickrange <@ event_1.newrange) AND (event_1.id <> event_2.id))
         ->  CTE Scan on event event_3  (cost=0.01..117.76 rows=21 width=0) (actual time=0.173..0.173 rows=1 loops=160)
               Filter: ((tickrange <@ event_2.newrange) AND (event_1.id <> id) AND (event_2.id <> id))
               Rows Removed by Filter: 830
Planning time: 0.322 ms
Execution time: 11060.848 ms

這是使用WHERE它們應該在的條件正確指定的相同查詢。您會注意到，除了巨大的性能差異外，過濾器按應有的順序應用。

EXPLAIN ANALYZE
WITH event AS (
 SELECT *,
   int4range(
     lower(event.tickrange),
     upper(event.tickrange) + 500
   ) AS newrange
 FROM event
)
SELECT event_1.id AS event_1_id
FROM event AS event_1
WHERE event_1.a_id = 189
AND EXISTS (
 SELECT 1
 FROM event AS event_2
 WHERE EXISTS (
   SELECT 1
   FROM event AS event_3
   WHERE (event_3.tickrange <@ event_2.newrange)
   AND event_1.id != event_3.id
   AND event_2.id != event_3.id
 )
 AND (event_2.tickrange <@ event_1.newrange)
 AND event_1.id != event_2.id
)


Nested Loop Semi Join  (cost=117.64..506774.59 rows=1 width=4) (actual time=0.070..111.936 rows=158 loops=1)
 Join Filter: ((event_2.tickrange <@ event_1.newrange) AND (event_1.id <> event_2.id) AND (SubPlan 2))
 Rows Removed by Join Filter: 136850
 CTE event
   ->  Seq Scan on event  (cost=0.00..117.64 rows=4282 width=30) (actual time=0.032..6.944 rows=4282 loops=1)
 ->  CTE Scan on event event_1  (cost=0.00..96.34 rows=21 width=36) (actual time=0.040..0.958 rows=161 loops=1)
       Filter: (a_id = 189)
       Rows Removed by Filter: 4121
 ->  CTE Scan on event event_2  (cost=0.00..85.64 rows=4282 width=68) (actual time=0.001..0.139 rows=851 loops=161)
 SubPlan 2
   ->  CTE Scan on event event_3  (cost=0.00..117.76 rows=21 width=0) (actual time=0.290..0.290 rows=1 loops=160)
         Filter: ((tickrange <@ event_2.newrange) AND (event_1.id <> id) AND (event_2.id <> id))
         Rows Removed by Filter: 830
Planning time: 0.446 ms
Execution time: 112.160 ms

所以我有兩個（如果我可能這麼大膽的話）問題：

  1. 除了嵌套的存在語句之外，還有更好的方法來編寫查詢嗎？
  2. 有沒有一種方法可以說服優化器完成我的工作，即提高條件以便盡快應用過濾器。

我正在使用 Postgres 9.5

編輯：我公認的長篇文章可能會使用一些澄清，所以這裡是摘要。

  • 我的目標是創建查詢，這樣我就不必解析邏輯並將其應用於語句的不同級別。我在想可能有一些我沒有做的事情會讓優化器做到這一點。
  • 第二個查詢是第一個查詢的版本，其中我解析了邏輯並將語句放在正確的級別。我將其展示為優化器如何處理更合適的查詢的比較。
  • 在我的範例中添加到 500tickrange代表我要執行的查詢類型。它的值將在查詢之間甚至在同一查詢中的不同事件之間發生變化。

您可能不知道 Postgres 中的 CTE 是優化圍欄。換句話說，它們被評估。這是查詢的問題 1。

問題2是內部子查詢中的某些條件可以拉到中間子查詢或主查詢中。雖然 Postgres 優化器很好，但為什麼要依賴它呢？（我認為它對嵌套派生表進行了這種優化，但我不確定嵌套EXISTS）。

問題 3 是比較基於計算值 (the +500)。沒有更多資訊 - 這 500 是否固定），我會留到以後。

如果我們刪除 CTE 並拉出可以拉取的條件，則查詢變為：

SELECT e1.id AS event_1_id
FROM event AS e1
WHERE e1.a_id = 189
 AND EXISTS (
   SELECT 
   FROM event AS e2
   WHERE e2.tickrange <@ int4range(lower(e1.tickrange), 
                                   upper(e1.tickrange) + 500)
     AND e2.id <> e1.id
     AND EXISTS (
       SELECT 
       FROM event AS e3
       WHERE e3.tickrange <@ int4range(lower(e2.tickrange), 
                                       upper(e2.tickrange) + 500)
         AND e3.id <> e2.id
         AND e3.id <> e1.id 
     )
 ) ;

我希望這比以前更有效，只是通過刪除 CTE。

如果這500是固定的，添加索引(int4range(lower(tickrange), upper(tickrange) + 500))也可能會有所幫助。

關於其他編寫方式，您可以使用連接，它允許關聯LATERAL中的派生表。需要與FROM等價：LIMIT 1``EXISTS

SELECT e1.id AS event_1_id
FROM event AS e1,
 LATERAL 
   ( SELECT 
     FROM event AS e2,
       LATERAL
         ( SELECT 
           FROM event AS e3
           WHERE e3.tickrange <@ int4range(lower(e2.tickrange), 
                                           upper(e2.tickrange) + 500)
             AND e3.id <> e2.id
             AND e3.id <> e1.id 
         ) 
     WHERE e2.tickrange <@ int4range(lower(e1.tickrange), 
                                     upper(e1.tickrange) + 500)
       AND e2.id <> e1.id
     LIMIT 1
   ) AS ex
WHERE e1.a_id = 189 ;

上述兩種方式仍然有嵌套的子查詢。嵌套雖然不需要EXISTS。您可以有一個EXISTS（或LATERAL）子查詢，如下所示：

SELECT e1.id AS event_1_id
FROM event AS e1
WHERE e1.a_id = 189
 AND EXISTS (
   SELECT
   FROM event AS e2,
        event AS e3
   WHERE e2.tickrange <@ int4range(lower(e1.tickrange), 
                                   upper(e1.tickrange) + 500)
     AND e2.id <> e1.id
     AND e3.tickrange <@ int4range(lower(e2.tickrange), 
                                       upper(e2.tickrange) + 500)
     AND e3.id <> e2.id
     AND e3.id <> e1.id 
 ) ;

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

相關問答

Postgresql

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

  • November 28, 2019
檢視問答
Postgresql

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

  • July 20, 2018
檢視問答
Postgresql

昂貴子查詢的優化

  • March 12, 2014
檢視問答
Postgresql

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

  • August 21, 2022
檢視問答
Postgresql

FROM 子句中的相關函式是否針對每一行執行？

  • July 10, 2022
檢視問答
Postgresql

優化對 690,000 行表的昂貴的 GROUP BY / ORDER BY 查詢

  • March 3, 2022
檢視問答