使用來自許多表的列提高 order by 的性能

使用 PostgreSQL 8.4，我正在嘗試使用 order by 和兩個表的索引列來查閱具有 100 萬條記錄的兩個表，並且我正在失去性能（1 列需要 30 毫秒，兩列需要 5 分鐘）。例如：

select r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
from requests r
inner join samples s on (s.code = r.sample_code)
order by s.barcode  asc , r.code asc
limit 21;

表資訊：

CREATE TABLE public.samples (
 code BIGINT NOT NULL,
 barcode VARCHAR(40) NOT NULL,
 registry_date TIMESTAMP WITH TIME ZONE NOT NULL,
 CONSTRAINT samples_pkey PRIMARY KEY(code)
);

CREATE INDEX idx_samp_barcode ON public.samples (barcode);
CREATE INDEX idx_samp_barcode_code ON public.samples (barcode, code);
CREATE INDEX idx_samp_barcode_code_desc ON public.samples (barcode DESC, code DESC);
CREATE INDEX idx_test_identifier_desc ON public.samples (barcode DESC);


CREATE TABLE public.requests (
 code BIGINT NOT NULL,
 test_code INTEGER NOT NULL,
 sample_code BIGINT NOT NULL,
 CONSTRAINT requests_pkey PRIMARY KEY(code),
 CONSTRAINT "Requests_S_fk" FOREIGN KEY (sample_code)
   REFERENCES public.samples(code)
   ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE,
 CONSTRAINT "Requests_T_fk" FOREIGN KEY (test_code)
   REFERENCES public.tests(code)
   ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE
);

CREATE INDEX idx_req_test_code ON public.requests (test_code);
CREATE INDEX idx_req_test_code_desc ON public.requests (test_code DESC);
CREATE INDEX request_sample_code_index ON public.requests (sample_code);
CREATE INDEX requests_sample_code_desc_idx ON public.requests (sample_code DESC, code DESC);
CREATE INDEX requests_sample_code_idx ON public.requests (sample_code, code);

1. 每個表有 100 萬行。
2. 兩個表中的所有行都是不同的。
3. 如果我單獨按任一列排序，則執行時間約為 30 毫秒。
4. 沒有要求就沒有樣品。
5. 一個樣本可以有很多s.code個 smae s.barcode。

我怎樣才能提高性能？

問題

這是一個比一眼就能看出的更複雜的問題。您正在按兩列排序，每列來自不同的表，同時您連接其他兩列。這使得 Postgres 無法使用提供的索引，並且必須預設（非常）昂貴的順序掃描。這是 dba.SE 上的一個相關案例：

• 空間索引能否幫助“範圍-按-限制”查詢

索引

您需要兩個索引以獲得最佳性能，這兩個索引都已經存在：

CREATE INDEX idx_samp_barcode_code ON public.samples (barcode, code);
CREATE INDEX requests_sample_code_idx ON public.requests (sample_code, code);

但是您擁有的普通查詢無法使用它們，即使在 pg 9.4 中也是如此。

詢問

Postgres 8.4 是一個相當大的障礙。但我想我找到了一種遞歸 CTE 的方法：

WITH RECURSIVE cte AS (
  ( -- all parentheses are required
  SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
  FROM  (
     SELECT s.barcode
     FROM   samples s
     -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
     ORDER  BY s.barcode
     LIMIT  1  -- get smallest barcode to start with
     ) s0
  JOIN   samples  s USING (barcode)  -- join all samples with same barcode
  JOIN   requests r ON r.sample_code = s.code
  ORDER  BY r.code  -- start with ordered list
  )

  UNION ALL
  (
  SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
  FROM  (
     SELECT s.barcode
     FROM   cte c
     JOIN   samples s ON s.barcode > c.barcode 
     -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
     ORDER  BY s.barcode
     LIMIT  1  -- get next higher barcode
     ) s0
  JOIN   samples  s USING (barcode)  -- join all samples with same barcode
  JOIN   requests r ON r.sample_code = s.code
  ORDER  BY r.code  -- again, ordered list
  )
  )
TABLE cte
LIMIT 21;

在 pg 9.4 中測試並為我工作。它使用索引（部分用於 pg 9.2+ 中的僅索引掃描）。

第 8.4 頁已經有遞歸 CTE。剩下的是基本的 SQL（甚至TABLE在 pg 8.4 中可用（並且可以替換為SELECT * FROM）。我希望沒有限制破壞聚會，我不再安裝 pg 8.4。

解釋

評論部分：

-- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)

如果可能有根本沒有請求的樣本，則需要，您在評論中排除了這一點。

該查詢依賴於遞歸 CTE 的這種記錄行為：

提示：遞歸查詢評估算法以 廣度優先搜尋順序生成其輸出。

大膽強調我的。在這方面，也是：

這是有效的，因為 PostgreSQL 的實現只評估查詢的行數**，與父查詢實際獲取的****行數一樣多WITH**。不建議在生產中使用此技巧，因為其他系統可能會以不同的方式工作。此外，如果您使外部查詢對遞歸查詢的結果進行排序或將它們連接到其他表，它通常不起作用。

大膽強調我的。因此，只有在您不在ORDER BY外部查詢中添加另一個時，才能保證在 PostgreSQL 中工作。

LIMIT這對於外部查詢中的（相對）較小的查詢來說很快。對於手頭的情況，應該很快。該查詢對於大型LIMIT.

PL/pgSQL 函式

我能想到的另一個選擇是使用 plpgsql 的程序解決方案。應該會快一點，尤其是對於重複呼叫：

CREATE OR REPLACE FUNCTION f_demo(_limit int)
 RETURNS TABLE (code int8, test_code int, sample_code int8
              , barcode varchar, registry_date timestamptz) AS
$func$
DECLARE
  _barcode text;
  _n       int;
  _rest    int := _limit;  -- init with _limit parameter
BEGIN
  FOR _barcode IN
     SELECT DISTINCT s.barcode
     FROM   samples s
     -- WHERE  EXISTS (SELECT 1 FROM requests WHERE r.sample_code = s.code)
     ORDER  BY s.barcode

  LOOP
     RETURN QUERY
     SELECT r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
     FROM   samples  s
     JOIN   requests r ON r.sample_code = s.code
     WHERE  s.barcode = _barcode
     ORDER  BY r.code
     LIMIT  _limit;

     GET DIAGNOSTICS _n = ROW_COUNT;
     _rest := _rest - _n;
     EXIT WHEN _rest < 1;
  END LOOP;
END
$func$ LANGUAGE plpgsql;

稱呼：

SELECT * FROM f_demo(21); 

MATERIALIZED VIEW

largeOFFSET具有與 large 類似的效果LIMIT。雖然所有跳過的行都不會添加到 I/O，但仍需要計算它們以確定偏移*後的第一行。*如果需要，請使用MATERIALIZED VIEW帶有添加行號和索引的 a。

Postgres 9.3+ 有一個內置功能，使用過時的軟體，您必須手動編寫解決方案。不過，這並不復雜。基本上是從預定義SELECT語句或VIEW類似的填充的快照表。基本上，將表創建為：

CREATE TABLE req_sample AS
SELECT row_number() OVER (ORDER BY s.barcode, r.code) AS rn
    , r.code, r.test_code, r.sample_code, s.barcode, s.registry_date
FROM   requests r
JOIN   samples s on (s.code = r.sample_code)
ORDER  by s.barcode, r.code;

ALTER TABLE req_sample ADD CONSTRAINT req_sample_rk PRIMARY KEY (code);
CREATE INDEX foo ON  req_sample (rn);

將該 SELECT 保存為函式、視圖或純查詢文本。在一筆交易中刷新（昂貴）：

BEGIN;
-- drop PK & index
TRUNCATE req_sample;
INSERT INTO req_sample SELECT ... ;
-- add PK & index
COMMIT;

冗餘barcode列

如果基礎表發生很大變化並且您需要“目前”結果，則 MV 會變得更加昂貴。還有一個更***便宜的選擇：*將 儲存barcode在requests表中冗餘。您可以將其作為 FK 約束中的第二列包含在samples( "Requests_S_fk") 中，並ON UPDATE CASCADE避免數據過時/衝突。然後您可以使用 in 上的(barcode, code)索引requests。

對於最後兩個選項，您可以使用直接適用的索引簡化分頁，並使用WHERE上一頁中的值的子句替換OFFSET. 考慮一下use-the-index-luke.com 上關於“以 PostgreSQL 方式完成分頁”的展示文稿。

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