如何提高大銷售表中不同供應商的查詢性能？

我有兩張表，一張有儲存數據：

             Table "public.store"
 Column   |          Type          | Modifiers
-----------+------------------------+-----------
id        | integer                | not null
hul_code  | character varying      |
latitude  | numeric                |
longitude | numeric                |
name      | character varying(100) |
Indexes:
   "store_pkey" PRIMARY KEY, btree (id)
   "store-hlcode-idx" btree (hul_code)

另一個帶有銷售數據：

        Table "public.sale_data"
 Column   |       Type        | Modifiers
-----------+-------------------+-----------
id        | integer           | not null
hul_code  | character varying |
partyname | character varying |
rscode    | character varying |
rsname    | character varying |
cluster   | character varying |
channel   | character varying |
basepack  | character varying |
total_16  | numeric           |
total_17  | numeric           |
total_18  | numeric           |
Indexes:
   "sale_data_pkey" PRIMARY KEY, btree (id)
   "hul-code-idx" btree (hul_code)
   "sd-bp-idx" btree (basepack)
   "sd-rscd-idx" btree (rscode)

第二個表有超過 1100 萬行。

我正在嘗試使用以下查詢獲取所有商店的列表，這些商店的商品來自特定區域供應商（由 標識rscode）：

Select s.hul_code, s.name, s.latitude, s.longitude
from store s 
Where hul_code in 
 (Select Distinct(hul_code) as hulcode
  from sale_data
  where rscode='133955')
AND s.latitude is not null;

此查詢平均需要 1.5 到 2 秒以上。

如何提高這個查詢的速度？

對於典型的數據分佈，模擬鬆散的索引掃描應該是你的魔術。

需要多列索引才能快速：

CREATE INDEX ON sale_data(rscode, hul_code);

對於簡單的情況，在 rCTE 中具有相關子查詢的變體應該是最快的：

WITH RECURSIVE cte AS (
  SELECT min(hul_code) AS hul_code
  FROM   sale_data
  WHERE  rscode = '133955'    -- provide rscode here

  UNION ALL
  SELECT (SELECT hul_code
          FROM   sale_data
          WHERE  rscode = '133955'     -- repeat rscode here
          AND    hul_code > c.hul_code
          ORDER  BY 1
          LIMIT  1
          )
  FROM   cte c
  )
SELECT s.hul_code, s.name, s.latitude, s.longitude
FROM   cte c
JOIN   store s USING (hul_code);

詳細解釋：

• 優化 GROUP BY 查詢以檢索每個使用者的最新記錄

對於特定的數據分佈，可能有更快的替代方案。

旁白：

DISTINCT (rscode, hul_code)對於每個不同的行很少（產生大量結果行）的數據分佈可能有意義。但是你所擁有的並沒有多大意義：

IN (Select Distinct(hul_code) as hulcode ... )

列別名 ( as hulcode) 是噪音，因為IN忽略了列名。

DISTINCT不需要括號。(hul_code)被解釋為 ROW 建構子，是ROW(hul_code). 在這種情況下，行包裝器恰好會為單列自動剝離。但是您可能會為具有多列的情況引入複雜性。

有DISTINCT ON ()需要括號的 Postgres 變體，但這裡沒有使用（也不需要）。有關的：

• 在每個 GROUP BY 組中選擇第一行？

但是對於一個IN你可能完全放棄的構造，這一切都沒有多大意義DISTINCT。更好的是，重寫為：

SELECT s.hul_code, s.name, s.latitude, s.longitude
FROM  (
  SELECT DISTINCT hul_code
  FROM   sale_data
  WHERE  rscode='133955'
  ) sd
JOIN  store s USING (hul_code);

..在不太可能的情況下，您應該有這樣的數據分佈。

而且您的列rscode似乎包含數字數據。如果是這樣，它應該是數字類型，而不是varchar.

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