不同 Postgres 數據庫上相同查詢的不同執行時間
我有兩個相同的數據庫，開發和生活。
我對他們兩個都執行這個查詢….
select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device)在開發盒上查詢大約需要 100 毫秒，在現場大約需要一個小時。
這兩個表都有一個名為 的主鍵欄位
deviceId，兩個數據庫具有相同的結構，並且都具有非常相似的數據（沒有一個表包含超過百萬行）。兩個數據庫都定期清理/分析，都在過去 12 小時內完成。每個數據庫都在不同的電腦上，每個數據庫的硬體都不同，但在 99% 的時間裡，live box 的速度會慢 10%。
據我所知，兩個數據庫的配置相同。
我的猜測是每個數據庫上的數據分佈存在一些細微的差異，這會影響解釋計劃的選擇。
我的問題是：
- 反正有沒有強制數據庫使用特定的索引？
- 是否有不同形式的 SQL 查詢會產生相同的結果但速度更快？
解釋計劃開發- 不到一秒鐘：
Aggregate (cost=7806.68..7806.69 rows=1 width=8) -> Seq Scan on deviceb (cost=4624.86..7702.06 rows=41848 width=0) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 -> Unique (cost=0.29..4404.59 rows=88108 width=7) -> Index Only Scan using device_pkey on device (cost=0.29..4184.32 rows=88108 width=7)現場解釋計劃- 大約需要一個小時：
Finalize Aggregate (cost=747368287.67..747368287.68 rows=1 width=8) -> Gather (cost=747368287.46..747368287.67 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=747367287.46..747367287.47 rows=1 width=8) -> Parallel Seq Scan on deviceb (cost=0.42..747367217.98 rows=27789 width=0) Filter: (NOT (SubPlan 1)) SubPlan 1 -> Materialize (cost=0.42..26001.96 rows=156913 width=7) -> Unique (cost=0.42..22822.40 rows=156913 width=7) -> Index Only Scan using device_pkey on device (cost=0.42..21930.12 rows=156913 width=7)我已經使用 pgAdmin4 比較了表的開發/實時版本 - 只需突出顯示對象並選擇每個表的屬性及其索引和約束。
另外 - 我的測試（在兩個數據庫上）是在沒有連接其他使用者的情況下完成的。
DDL，根據要求：
CREATE TABLE public.device ( deviceid character(6) COLLATE pg_catalog."default" NOT NULL, recentfixtime timestamp without time zone, newfixes integer DEFAULT 0, lastfixid bigint DEFAULT 0, CONSTRAINT device_pkey PRIMARY KEY (deviceid) USING INDEX TABLESPACE fastspace ); CREATE TABLE public.deviceb ( deviceid character(6) COLLATE pg_catalog."default" NOT NULL, flightid integer DEFAULT 0, lastfirstfixtime timestamp without time zone, lastprocfixtime timestamp without time zone, lastprocfly boolean, priority integer DEFAULT 0, lastlandtime timestamp without time zone, CONSTRAINT deviceb_pkey PRIMARY KEY (deviceid) USING INDEX TABLESPACE fastspace )更新 - 已解決，感謝 Jjanes，建議的版本在兩個數據庫上都非常快。
select count(*) FROM DeviceB where NOT EXISTS ( SELECT 1 from Device where Device.deviceID=DeviceB.deviceID )我承認我仍然不確定根本原因是什麼，但為了完整起見，我將嘗試回答一些進一步的問題。自動分析仍在進行中（pgAdmin/statistics 顯示了今天早上早些時候的最後一次自動分析日期。兩個表（實時）的行數都少於 200,000，開發上的兩個表都少於 100,000。開發/實時盒子上的硬體不同（最大區別是 dev 有 32G 記憶體，live 只有 16G）。可能最顯著的區別是版本，dev 是 13.1，live 仍然是 10.4
我已經問了一系列問題來探究根本原因。但不管根本原因的具體細節如何，您都可以通過使用 NOT EXISTS 而不是 NOT IN 來將自己與問題隔離開來。
select count(*) FROM DeviceB where NOT EXISTS ( SELECT 1 from Device where Device.deviceID=DeviceB.deviceID )
反正有沒有強制數據庫使用特定的索引？
不，不是直接的。您可以“禁用”其他訪問方法以使 Postgres 選擇索引掃描或點陣圖索引掃描。看：
但是你不能直接指示它使用某個索引。無論如何，這不是問題所在。
是否有不同形式的 SQL 查詢會產生相同的結果但速度更快？
是的。掉落
DISTINCT：SELECT count(*) FROM deviceb WHERE deviceid NOT IN (SELECT deviceid FROM device);試圖從大集合中刪除重複項是昂貴的。因為
device.deviceid是PRIMARY KEY，所以不能有重複。如果可能涉及 NULL 值或重複項，我們會查看其他查詢樣式，但對於兩個主鍵，查詢應該是最佳的。
並行順序掃描
deviceb是問題所在，正如我們在這裡看到的：deviceb 上的並行 Seq 掃描（成本=0.42.. 747367217.98行=27789 寬度=0）
不涉及索引。那應該不會那麼貴。我也想知道為什麼我們看不到這樣的一行：
‘過濾器刪除的行：123456’
那是什麼版本的 Postgres？您是如何準確生成查詢計劃的？
要麼是表有問題：
- 比你想像的多很多？檢查：
SELECT count(*) FROM deviceb;
- 極端的表和索引膨脹？檢查：
SELECT pg_size_pretty(pg_table_size('deviceb')) , pg_size_pretty(pg_table_size('deviceb_pkey')); SELECT * FROM pg_stat_user_tables WHERE relname = 'deviceb';用另一個修復
VACUUM ANALYZE deviceb;，也許即使VACUUM FULL ANALYZE deviceb;沒有其他使用者連接（？）
- 獨占鎖？
那不可能。你說：
沒有其他使用者連接。
- 硬體問題？
或者並行性有問題。嘗試禁用它（用於調試！）並重新測試：
SET max_parallel_workers_per_gather = 0;還：
兩個數據庫都定期清理/分析，都在過去 12 小時內完成。
聽起來您正在執行手動
VACUUM/ANALYZE。你禁用了autovacuum嗎？（通常情況下，您不應該這樣做。）雖然必須研究令人痛苦的緩慢（並行）順序掃描，但我想知道為什麼我們一開始就沒有看到僅索引掃描
diviceb。有了新鮮VACUUM的桌子和一個TABLESPACE名字fastspace，我期望同樣多。您是否知道可以設置一些關鍵參數TABLESPACE？最重要的是seq_page_cost和random_page_cost。像：ALTER TABLESPACE fastspace SET (seq_page_cost = 0.5, random_page_cost = 1);適應您的“fastspace”的特點。