兩台伺服器上的Postgresql查詢計劃不同

November 29, 2019

我在 prod 和 dev 之間執行相同的查詢並收到一些不同的查詢計劃時遇到了一些奇怪的問題，我不知道為什麼。我會盡我所能，但如果我忘記了一些重要的事情，請告訴我。

PostgreSQL：11.6

GCE 產品規格：

中央處理器：6
記憶體：64g
儲存：固態硬碟

GCE 開發規範：

中央處理器：4
記憶體：24g
儲存：硬碟

產品配置：

shared_buffers = 16GB
seq_page_cost = 1.0
random_page_cost = 3.0
effective_cache_size = 48GB
work_mem = 512MB
maintenance_work_mem = 2GB
effective_io_concurrency = 200

開發配置：

shared_buffers = 3840MB
seq_page_cost = 1.0
random_page_cost = 4.0
effective_cache_size = 11520MB
work_mem = 512MB
maintenance_work_mem = 960MB
effective_io_concurrency = 4

表定義

                                               Table "api.ms1_peak"
       Column         |       Type       | Collation | Nullable |                      Default                      
-----------------------+------------------+-----------+----------+---------------------------------------------------
ms1_peak_id           | integer          |           | not null | nextval('api.ms1_peak_ms1_peak_id_seq'::regclass)
mzml_id               | integer          |           |          | 
charge                | integer          |           |          | 
mz                    | double precision |           |          | 
adduct_m              | double precision |           |          | 
rt_corrected          | double precision |           |          | 
Indexes:
   "ms1_peak_pkey" PRIMARY KEY, btree (ms1_peak_id)
   "ms1_peak_ms1_peak_id_idx" btree (ms1_peak_id) INCLUDE (mz, rt_corrected)
   "ms1_peak_mz_rt_ms1_peak_id_idx" btree (mz, rt_corrected) INCLUDE (ms1_peak_id, charge)
   "ms1_peak_mzml_idx" btree (mzml_id)
Foreign-key constraints:
   "ms1_peak_mzml_id_fkey" FOREIGN KEY (mzml_id) REFERENCES api.mzml(mzml_id) ON DELETE CASCADE

                                                     Table "api.molecular_structure"
        Column         |       Type       | Collation | Nullable |                                 Default
------------------------+------------------+-----------+----------+-------------------------------------------------------------------------
molecular_structure_id | integer          |           | not null | nextval('api.molecular_structure_molecular_structure_id_seq'::regclass)
monoisotopic_mass      | double precision |           |          |
mol                    | mol              |           | not null |
Indexes:
   "molecular_structure_pkey" PRIMARY KEY, btree (molecular_structure_id)
   "molecular_structure_unique_idx" UNIQUE, btree (md5(mol::text))
   "molecular_structure_monoisotopic_mass_idx" btree (monoisotopic_mass, molecular_structure_id)
Referenced by:
   TABLE "api.smallmolecule_fragment" CONSTRAINT "smallmolecule_molecular_structure_id_fkey" FOREIGN KEY (molecular_structure_id) REFERENCES api.molecular_structure(molecular_structure_id) ON DELETE CASCADE
   TABLE "api.smallmolecule" CONSTRAINT "smallmolecule_molecular_structure_id_fkey" FOREIGN KEY (molecular_structure_id) REFERENCES api.molecular_structure(molecular_structure_id) ON DELETE SET NULL

                                                      Table "api.smallmolecule"
        Column         |           Type           | Collation | Nullable |                           Default
------------------------+--------------------------+-----------+----------+-------------------------------------------------------------
smallmolecule_id       | integer                  |           | not null | nextval('api.smallmolecule_smallmolecule_id_seq'::regclass)
created_date           | timestamp with time zone |           |          | now()
original_smiles        | text                     |           |          |
reference              | json                     |           |          |
names                  | jsonb                    |           |          |
cas                    | text                     |           |          |
names_tsvector         | tsvector                 |           |          |
molecular_structure_id | integer                  |           |          |
Indexes:
   "smallmolecule_pkey" PRIMARY KEY, btree (smallmolecule_id)
   "smallmolecule_molecular_structure_id_idx" btree (molecular_structure_id, smallmolecule_id)
   "smallmolecule_names_idx" gist (names_tsvector)
Foreign-key constraints:
   "smallmolecule_molecular_structure_id_fkey" FOREIGN KEY (molecular_structure_id) REFERENCES api.molecular_structure(molecular_structure_id) ON DELETE SET NULL
   "smallmolecule_organization_id_fkey" FOREIGN KEY (organization_id) REFERENCES api.organization(organization_id) ON DELETE SET NULL

有問題的查詢

select p.ms1_peak_id,
 (
   select coalesce(json_agg(sm.smallmolecule_id), '[]') from api.molecular_structure as ms
   join api.smallmolecule sm using(molecular_structure_id)
   where ms.monoisotopic_mass BETWEEN  p.adduct_m - public.mz_tol(p.adduct_m, 5) AND
           p.adduct_m + public.mz_tol(p.adduct_m, 5)
 ) as smallmolecules
from api.ms1_peak as p
where p.mzml_id = 20634
and adduct_m is not null
order by ms1_peak_id asc;

在開發伺服器上，這是查詢計劃

Sort  (cost=94236607.88..94236610.32 rows=973 width=36) (actual time=173.075..173.144 rows=1082 loops=1)
  Output: p.ms1_peak_id, ((SubPlan 1))
  Sort Key: p.ms1_peak_id
  Sort Method: quicksort  Memory: 106kB
  Buffers: shared hit=58597
  -&gt;  Bitmap Heap Scan on api.ms1_peak p  (cost=39.56..94236559.59 rows=973 width=36) (actual time=17.735..172.456 rows=1082 loops=1)
        Output: p.ms1_peak_id, (SubPlan 1)
        Recheck Cond: (p.mzml_id = 20634)
        Filter: (p.adduct_m IS NOT NULL)
        Rows Removed by Filter: 1318
        Heap Blocks: exact=399
        Buffers: shared hit=58597
        -&gt;  Bitmap Index Scan on ms1_peak_mzml_idx  (cost=0.00..39.32 rows=1967 width=0) (actual time=0.180..0.180 rows=2400 loops=1)
              Index Cond: (p.mzml_id = 20634)
              Buffers: shared hit=12
        SubPlan 1
          -&gt;  Aggregate  (cost=96843.52..96843.53 rows=1 width=32) (actual time=0.157..0.157 rows=1 loops=1082)
                Output: COALESCE(json_agg(sm.smallmolecule_id), '[]'::json)
                Buffers: shared hit=58186
                -&gt;  Hash Join  (cost=6062.96..96842.90 rows=246 width=4) (actual time=0.136..0.156 rows=1 loops=1082)
                      Output: sm.smallmolecule_id
                      Hash Cond: (ms.molecular_structure_id = sm.molecular_structure_id)
                      Buffers: shared hit=58186
                      -&gt;  Bitmap Heap Scan on api.molecular_structure ms  (cost=816.07..91096.68 rows=26500 width=4) (actual time=0.019..0.111 rows=89 loops=1082)
                            Output: ms.molecular_structure_id, ms.molecular_weight, ms.monoisotopic_mass, ms.molecular_formula, ms.flat_smiles, ms.mol, ms.ecfp6, ms.fcfp6
                            Recheck Cond: ((ms.monoisotopic_mass &gt;= (p.adduct_m - (('5'::double precision * p.adduct_m) / '1000000'::double precision))) AND (ms.monoisotopic_mass &lt;= (p.adduct_m + (('5'::double precision * p.adduct_m) / '1000000'::double precision))))
                            Heap Blocks: exact=54237
                            Buffers: shared hit=57961
                            -&gt;  Bitmap Index Scan on molecular_structure_monoisotopic_mass_idx  (cost=0.00..809.45 rows=26500 width=0) (actual time=0.012..0.012 rows=89 loops=1082)
                                  Index Cond: ((ms.monoisotopic_mass &gt;= (p.adduct_m - (('5'::double precision * p.adduct_m) / '1000000'::double precision))) AND (ms.monoisotopic_mass &lt;= (p.adduct_m + (('5'::double precision * p.adduct_m) / '1000000'::double precision))))
                                  Buffers: shared hit=3724
                      -&gt;  Hash  (cost=4633.03..4633.03 rows=49108 width=8) (actual time=16.781..16.782 rows=49102 loops=1)
                            Output: sm.smallmolecule_id, sm.molecular_structure_id
                            Buckets: 65536  Batches: 1  Memory Usage: 2431kB
                            Buffers: shared hit=225
                            -&gt;  Index Only Scan using smallmolecule_molecular_structure_id_idx on api.smallmolecule sm  (cost=0.41..4633.03 rows=49108 width=8) (actual time=0.030..7.495 rows=49108 loops=1)
                                  Output: sm.smallmolecule_id, sm.molecular_structure_id
                                  Heap Fetches: 0
                                  Buffers: shared hit=225
Planning Time: 0.440 ms
Execution Time: 173.291 ms

在生產中

Sort  (cost=3158406.45..3158408.86 rows=965 width=36) (actual time=10773.364..10773.433 rows=1082 loops=1)
  Output: p.ms1_peak_id, ((SubPlan 1))
  Sort Key: p.ms1_peak_id
  Sort Method: quicksort  Memory: 106kB
  Buffers: shared hit=358573
  -&gt;  Index Scan using ms1_peak_mzml_idx on api.ms1_peak p  (cost=0.56..3158358.61 rows=965 width=36) (actual time=14.920..10770.308 rows=1082 loops=1)
        Output: p.ms1_peak_id, (SubPlan 1)
        Index Cond: (p.mzml_id = 20634)
        Filter: (p.adduct_m IS NOT NULL)
        Rows Removed by Filter: 1318
        Buffers: shared hit=358573
        SubPlan 1
          -&gt;  Aggregate  (cost=3266.75..3266.76 rows=1 width=32) (actual time=9.924..9.924 rows=1 loops=1082)
                Output: COALESCE(json_agg(sm.smallmolecule_id), '[]'::json)
                Buffers: shared hit=357961
                -&gt;  Hash Join  (cost=1164.60..3266.13 rows=246 width=4) (actual time=8.142..9.913 rows=1 loops=1082)
                      Output: sm.smallmolecule_id
                      Inner Unique: true
                      Hash Cond: (sm.molecular_structure_id = ms.molecular_structure_id)
                      Buffers: shared hit=357961
                      -&gt;  Index Only Scan using smallmolecule_molecular_structure_id_idx on api.smallmolecule sm  (cost=0.41..1973.03 rows=49108 width=8) (actual time=0.009..5.957 rows=49108 loops=1017)
                            Output: sm.molecular_structure_id, sm.smallmolecule_id
                            Heap Fetches: 0
                            Buffers: shared hit=322390
                      -&gt;  Hash  (cost=834.29..834.29 rows=26392 width=4) (actual time=0.087..0.087 rows=89 loops=1082)
                            Output: ms.molecular_structure_id
                            Buckets: 32768  Batches: 1  Memory Usage: 262kB
                            Buffers: shared hit=35571
                            -&gt;  Index Only Scan using molecular_structure_monoisotopic_mass_idx on api.molecular_structure ms  (cost=0.45..834.29 rows=26392 width=4) (actual time=0.032..0.071 rows=89 loops=1082)
                                  Output: ms.molecular_structure_id
                                  Index Cond: ((ms.monoisotopic_mass &gt;= (p.adduct_m - (('5'::double precision * p.adduct_m) / '1000000'::double precision))) AND (ms.monoisotopic_mass &lt;= (p.adduct_m + (('5'::double precision * p.adduct_m) / '1000000'::double precision))))
                                  Heap Fetches: 0
                                  Buffers: shared hit=35571
Planning Time: 2.329 ms
Execution Time: 10773.584 ms

有趣的是在 dev 上我得到了一個不同的查詢計劃。它通常使用點陣圖掃描，而在生產中它使用僅索引/索引掃描。

現在，如果我禁用點陣圖掃描強制更多的僅索引路由，我可以讓開發伺服器輸出更接近的查詢計劃到 prod

這是設置後的開發輸出set enable_bitmapscan = off;

Sort  (cost=107687464.30..107687466.74 rows=973 width=36) (actual time=166.059..166.129 rows=1082 loops=1)
  Output: p.ms1_peak_id, ((SubPlan 1))
  Sort Key: p.ms1_peak_id
  Sort Method: quicksort  Memory: 106kB
  Buffers: shared hit=58988
  -&gt;  Index Scan using ms1_peak_mzml_idx on api.ms1_peak p  (cost=0.56..107687416.01 rows=973 width=36) (actual time=17.591..165.425 rows=1082 loops=1)
        Output: p.ms1_peak_id, (SubPlan 1)
        Index Cond: (p.mzml_id = 20634)
        Filter: (p.adduct_m IS NOT NULL)
        Rows Removed by Filter: 1318
        Buffers: shared hit=58988
        SubPlan 1
          -&gt;  Aggregate  (cost=110667.53..110667.54 rows=1 width=32) (actual time=0.150..0.150 rows=1 loops=1082)
                Output: COALESCE(json_agg(sm.smallmolecule_id), '[]'::json)
                Buffers: shared hit=58376
                -&gt;  Hash Join  (cost=5247.33..110666.91 rows=246 width=4) (actual time=0.123..0.149 rows=1 loops=1082)
                      Output: sm.smallmolecule_id
                      Hash Cond: (ms.molecular_structure_id = sm.molecular_structure_id)
                      Buffers: shared hit=58376
                      -&gt;  Index Only Scan using molecular_structure_monoisotopic_mass_idx on api.molecular_structure ms  (cost=0.45..104920.69 rows=26500 width=4) (actual time=0.010..0.106 rows=89 loops=1082)
                            Output: ms.monoisotopic_mass, ms.molecular_structure_id
                            Index Cond: ((ms.monoisotopic_mass &gt;= (p.adduct_m - (('5'::double precision * p.adduct_m) / '1000000'::double precision))) AND (ms.monoisotopic_mass &lt;= (p.adduct_m + (('5'::double precision * p.adduct_m) / '1000000'::double precision))))
                            Heap Fetches: 96251
                            Buffers: shared hit=58151
                      -&gt;  Hash  (cost=4633.03..4633.03 rows=49108 width=8) (actual time=16.941..16.941 rows=49102 loops=1)
                            Output: sm.smallmolecule_id, sm.molecular_structure_id
                            Buckets: 65536  Batches: 1  Memory Usage: 2431kB
                            Buffers: shared hit=225
                            -&gt;  Index Only Scan using smallmolecule_molecular_structure_id_idx on api.smallmolecule sm  (cost=0.41..4633.03 rows=49108 width=8) (actual time=0.027..7.519 rows=49108 loops=1)
                                  Output: sm.smallmolecule_id, sm.molecular_structure_id
                                  Heap Fetches: 0
                                  Buffers: shared hit=225

這個查詢更接近生產，而且速度更快（dev 上的兩種情況都比 prod 快）但是兩者之間的查詢計劃仍然略有不同。請注意，prod 查詢計劃使用內部唯一性：true while 將索引放在底部的“molecular_structure_monoisotopix_mass_idx”上並循環 1082 次，而在 dev 上，此索引使用率在解釋中較高，而不使用內部唯一性：true

通過 explain.depesz 將這些查詢計劃分解並顯示生產查詢計劃程序在 api.smallmolecule 上僅掃描索引需要 6 秒，而在雜湊連接上需要 4 秒。

在 dev 上，僅掃描 api.smallmolecule 的索引為 8ms，hash join 為 28ms。

開發和生產上的分子結構和小分子數據是相同的。兩者都被抽真空和分析。所以這些表的數據集是相同的。

分子結構上約 520 萬行，小分子上約 49000 行。

通過調整配置，我可以設法讓開發伺服器執行並輸出完全相同的計劃，當我設置random_page_cost = 2.0. 這會產生完全相同的計劃。所以我開始認為這絕對是兩者之間的調整問題。在 prod 上使用 random_page_cost 進行調整似乎對此特定查詢沒有影響。

這些伺服器位於 Google Cloud Engine 上，prod 使用持久 SSD，因此我知道在開發人員使用 HDD 時，使用專用 SSD 設置調整某些值可能不准確。

關於如何在生產中調整或調試以獲得更好結果的任何想法都會很棒。任何我想念的東西都讓我知道。

編輯：

真空分析已在生產中執行，這是新數據，因此沒有新的插入/更新/刪除，所以我不認為索引碎片是問題所在。

在生產中，如果我禁用索引掃描set enable_indexscans = 'off' ，我會得到與開發機器性能匹配的點陣圖堆掃描計劃和 180 毫秒。

Sort  (cost=70758692.10..70758694.51 rows=966 width=36) (actual time=179.621..179.692 rows=1082 loops=1)
  Output: p.ms1_peak_id, ((SubPlan 1))
  Sort Key: p.ms1_peak_id
  Sort Method: quicksort  Memory: 106kB
  Buffers: shared hit=60536
  -&gt;  Bitmap Heap Scan on api.ms1_peak p  (cost=42.52..70758644.20 rows=966 width=36) (actual time=29.350..179.233 rows=1082 loops=1)
        Output: p.ms1_peak_id, (SubPlan 1)
        Recheck Cond: (p.mzml_id = 20634)
        Filter: (p.adduct_m IS NOT NULL)
        Rows Removed by Filter: 1318
        Heap Blocks: exact=399
        Buffers: shared hit=60536
        -&gt;  Bitmap Index Scan on ms1_peak_mzml_idx  (cost=0.00..42.28 rows=1962 width=0) (actual time=0.169..0.169 rows=2400 loops=1)
              Index Cond: (p.mzml_id = 20634)
              Buffers: shared hit=12
        SubPlan 1
          -&gt;  Aggregate  (cost=73243.01..73243.02 rows=1 width=32) (actual time=0.163..0.163 rows=1 loops=1082)
                Output: COALESCE(json_agg(sm.smallmolecule_id), '[]'::json)
                Buffers: shared hit=60125
                -&gt;  Hash Join  (cost=3970.76..73242.40 rows=246 width=4) (actual time=0.145..0.162 rows=1 loops=1082)
                      Output: sm.smallmolecule_id
                      Hash Cond: (ms.molecular_structure_id = sm.molecular_structure_id)
                      Buffers: shared hit=60125
                      -&gt;  Bitmap Heap Scan on api.molecular_structure ms  (cost=576.97..69450.26 rows=26392 width=4) (actual time=0.019..0.106 rows=89 loops=1082)
                            Output: ms.molecular_structure_id, ms.molecular_weight, ms.monoisotopic_mass, ms.molecular_formula, ms.flat_smiles, ms.mol, ms.ecfp6, ms.fcfp6
                            Recheck Cond: ((ms.monoisotopic_mass &gt;= (p.adduct_m - (('5'::double precision * p.adduct_m) / '1000000'::double precision))) AND (ms.monoisotopic_mass &lt;= (p.adduct_m + (('5'::double precision * p.adduct_m) / '1000000'::double precision))))
                            Heap Blocks: exact=54237
                            Buffers: shared hit=57836
                            -&gt;  Bitmap Index Scan on molecular_structure_monoisotopic_mass_idx  (cost=0.00..570.37 rows=26392 width=0) (actual time=0.012..0.012 rows=89 loops=1082)
                                  Index Cond: ((ms.monoisotopic_mass &gt;= (p.adduct_m - (('5'::double precision * p.adduct_m) / '1000000'::double precision))) AND (ms.monoisotopic_mass &lt;= (p.adduct_m + (('5'::double precision * p.adduct_m) / '1000000'::double precision))))
                                  Buffers: shared hit=3599
                      -&gt;  Hash  (cost=2780.02..2780.02 rows=49102 width=8) (actual time=28.821..28.822 rows=49102 loops=1)
                            Output: sm.smallmolecule_id, sm.molecular_structure_id
                            Buckets: 65536  Batches: 1  Memory Usage: 2431kB
                            Buffers: shared hit=2289
                            -&gt;  Seq Scan on api.smallmolecule sm  (cost=0.00..2780.02 rows=49102 width=8) (actual time=0.007..19.592 rows=49108 loops=1)
                                  Output: sm.smallmolecule_id, sm.molecular_structure_id
                                  Buffers: shared hit=2289
Planning Time: 0.461 ms
Execution Time: 179.824 ms
(40 rows)

我注意到的另一件事是，如果我刪除子查詢中的連接smallmolecule table，這個查詢會變得非常快。

編輯2：

我設法做一個 pg_basebackup 並在開發和生產之間完全同步數據。唯一多了幾百萬個插入的表是 ms1_peak 表，在這些結果期間，molecules_structure 和 smallmolecule 表在兩者之間從未改變。

ms1_peak 的結果集在過時的 dev 和 prod 之間仍然返回相同的結果（1082 個峰值）。

但無論如何，我仍然不相信大型峰值表是原因，因為該查詢非常快。但是現在 dev 和 prod 都產生了完全相同的查詢計劃和 10 秒的性能。為什麼加入小分子表超級慢。

問題來自對點陣圖索引掃描api.molecular_structure（26392 而不是實際的 89）的錯誤行計數估計，並且它必須是傾斜計劃的數據中的微小差異。
估計很難改進。
重寫查詢怎麼樣？
SELECT p.ms1_peak_id,
      coalesce(json_agg(sm.smallmolecule_id), '[]')
FROM api.ms1_peak AS p
  LEFT JOIN api.molecular_structure AS ms
     ON ms.monoisotopic_mass BETWEEN p.adduct_m - public.mz_tol(p.adduct_m, 5)
                                 AND p.adduct_m + public.mz_tol(p.adduct_m, 5)
  LEFT JOIN api.smallmolecule sm
     USING (molecular_structure_id)
WHERE p.mzml_id = 20634
 AND p.adduct_m IS NOT NULL
GROUP BY p.ms1_peak_id
ORDER BY p.ms1_peak_id ASC;
不確定這是否會提高速度，但值得一試。

由於您的產品上有 SSD，您是否嘗試過設置 seq_page_cost = random_page_cost = 1.0？有很多文章是這樣建議的。
我們觀察到了一個類似的問題，這是因為我們的開發環境在生產環境中數據量很小。有很多已終止的客戶端數據（未觸及的行）正在污染索引。因此，我們調整了索引以排除終止客戶的數據，然後在 prod/dev 上的性能大致相同。

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

兩台伺服器上的Postgresql查詢計劃不同

相關問答

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

執行 SQL 查詢時如何獲取更多物理細節？

PostgreSQL NOT IN 數組慢查詢

可怕的執行查詢，如何解決

為什麼有些計數查詢這麼慢？

為什麼儘管對列進行了索引排序，但查詢計劃仍然對錶進行排序？