Postgresql

是否有更有效的方法來搜尋記錄,但前提是它具有關聯?

  • January 29, 2017

我正在使用 Postgres 9.5。我想選擇一些記錄,但前提是它們在另一個表中至少有一個關聯。我想出了這個查詢:

select distinct m.name, m.id 
from machines m, parts rt 
where m.id = rt.machine_id 
 and m.name like '%Name%';

但我認為這不是特別有效,因為如果有 50000 個關聯,我認為它們都已載入,然後“區分”通過,這似乎有點低效。是否有一種更有效的方法來檢查載入記錄,但前提是它具有一個或多個關聯?

您的查詢的結構方式,您可以使用EXISTS表達式編寫它:

SELECT
   m.name, m.id 
FROM
   machines m
WHERE
   m.name LIKE '%Name%'
   AND EXISTS (SELECT * 
                 FROM parts rt
                WHERE rt.machine_id = m.id
              ) ;

如果您希望查詢高效,請考慮到最困難的部分可能是檢查機器name。考慮machines.name使用trigram index. Trigram 索引允許快速查找LIKE '%something%'條件。

CREATE EXTENSION pg_trgm;  -- Only needed if extension not already installed
CREATE INDEX name_like_idx 
   ON machines 
   USING GIST (name gist_trgm_ops);

最有可能的是,數據庫規劃器會制定一個執行計劃,它首先會找到nameLIKE ‘%Name%’ 的機器(使用name_like_idx索引),然後,對於這個子集中的每台機器,它會檢查是否有與他們; 如果是這樣,它們將成為結果的一部分。

為了效率,還假設在 上也有一個索引parts.machine_id,因為這將是 PostgreSQL 將如何有效地檢查該部分是否存在的方式。如果machine_id外鍵,則始終建議對其進行索引。

此查詢不需要DISTINCT(假設ids 已經是唯一的)。

引用自:https://dba.stackexchange.com/questions/162582