Postgres-如何查找未在不同的多個表中使用的ID（非活動ID）-查詢撰寫錯誤

我有主桌的桌鎮。該表包含太多行并且變得如此“臟”（有人插入了 5 百萬行）以至于我想擺脫未使用的城鎮。

有 3 個參考表使用我的 town_id 作為城鎮的參考。

而且我知道有許多城鎮未在此表中使用，只有town_id在這 3 個表中都沒有找到時，我才認為它是不活動的，我想洗掉該城鎮（因為它未被使用）。

我的故事的列印螢屏：

如您所見，這兩個不同的表中使用了城鎮：

• 雇員
• 辦公室

對于 table * vendors 有vendor_idtable towns因為一個 vendor 可以有多個城鎮。

因此，如果vendor_idin towns 為 null 并且在這兩個表中的任何一個中都找不到 town_id，則可以安全地洗掉它 :)

我創建了一個可能有效的查詢，但執行起來花費了太多時間，它看起來像這樣：

select count(*) 
from towns
where vendor_id is null 
    and id not in (select town_id from banks) 
    and id not in (select town_id from employees)

所以基本上我說，如果vendor_is為空，則意味著這個城鎮肯定與供應商無關，同時如果同一個城鎮不在銀行和員工中，那么洗掉它是安全的..但查詢時間太長，并且從未成功執行。..因為城鎮有 500 萬行，這就是它如此臟的原因..

面對我無法執行給定的查詢，因為服務器例外終止..

這是完整的錯誤訊息：

ERROR: server closed the connection unexpectedly 這可能意味著服務器在處理請求之前或期間例外終止。

任何形式的幫助都會很棒謝謝！

uj5u.com熱心網友回復：

您可以使用 join 表LEFT JOIN來識別表和子句中town_id沒有行的表：banksemployeeWHERE

WITH list AS
( SELECT t.town_id
    FROM towns AS t
    LEFT JOIN tbl.banks AS b ON b.town_id = t.town_id
    LEFT JOIN tbl.employees AS e ON e.town_id = t.town_id
   WHERE t.vendor_id IS NULL
     AND b.town_id IS NULL
     AND e.town_id IS NULL
   LIMIT 1000
)
DELETE FROM tbl.towns AS t
  USING list AS l
 WHERE t.town_id = l.town_id ;

在啟動 DELETE 之前，您可以檢查表上的索引。如下添加索引可能很有用：

CREATE INDEX town_id_nulls ON towns (town_id NULLS FIRST) ;

最后但同樣重要的是，您可以在中添加一個LIMIT子句，cte以便在執行 DELETE 時限制洗掉的行數并避免意外終止。因此，您將不得不多次重新啟動 DELETE，直到沒有更多行要洗掉為止。

uj5u.com熱心網友回復：

你可以在大表上嘗試 JOIN 它會比兩個更快IN

您也可以嘗試UNION ALL使用重復項，因為它更快UNION

最后，您可以對 id 和 vendor_id 使用組合索引，以加快查詢速度

CREATE TABLe towns (id int , vendor_id int)

CREATE TABLE

CREATE tABLE banks (town_id int)

CREATE TABLE

CREATE tABLE employees (town_id int)

CREATE TABLE

select count(*) 
from towns t1 JOIN (select town_id from banks UNION select town_id from employees) t2 on t1.id <> t2.town_id
where vendor_id is null

SELECT 1

小提琴

uj5u.com熱心網友回復：

訣竅是首先列出您要保留的所有 town_id，然后開始洗掉那些不存在的。通過查看 2 個表，您會使服務器的作業變得更加困難，所以讓我們先創建一個串列。

-- build empty temp-table
CREATE TEMPORARY TABLE TEMP_must_keep 
AS
SELECT town_id       
  FROM tbl.towns
 WHERE 1 = 2;
 
-- get id's from first table
INSERT TEMP_must_keep (town_id)
SELECT DISTINCT town_id 
  FROM tbl.banks;
  
-- add index to speed up the EXCEPT below
CREATE UNIQUE INDEX idx_uq_must_keep_town_id ON TEMP_must_keep (town_id);

-- add new ones from second table
INSERT TEMP_must_keep (town_id)
SELECT town_id 
  FROM tbl.employees
 
EXCEPT -- auto-distincts

SELECT town_id 
  FROM TEMP_must_keep;
  
-- rebuild index simply to ensure little fragmentation
REINDEX TABLE TEMP_must_keep;

-- optional, but might help: create a temporary index on the towns table to speed up the delete
CREATE INDEX idx_towns_town_id_where_vendor_null ON tbl.towns (town_id) WHERE vendor IS NULL;

-- Now do actual delete
-- You can do a `SELECT COUNT(*)` rather than a `DELETE` first if you feel like it, both will probably take some time depending on your hardware.
DELETE 
  FROM tbl.towns as del      
 WHERE vendor_id is null 
   AND NOT EXISTS ( SELECT * 
                      FROM TEMP_must_keep mk
                     WHERE mk.town_id = del.town_id);
                     
                     
-- cleanup
DROP INDEX tbl.idx_towns_town_id_where_vendor_null;
DROP TABLE TEMP_must_keep;

                     

這idx_towns_town_id_where_vendor_null是可選的，我不確定它是否會真正減少總時間，但恕我直言，它會幫助DELETE操作，因為索引應該讓查詢優化器更好地了解預期的數量。

標籤：数据库postgresql加入查询优化注意

上一篇：選擇加入同一張表

下一篇：sql通過id匹配不同記錄的表