> 30 秒慢查詢

我有一個非常慢的查詢，執行時間超過 30 秒：

SELECT DISTINCT id10
FROM
 (SELECT j.id AS id10,
         j.modified,
         j.n_type AS n_type5
  FROM note j
  WHERE j.modified_date >= '2016-10-01 23:12:34.000000'
    AND j.clientid = 16049
    AND j.n_type  IN ('n',
                          'n_1',
                          'n_custom',
                          'n_standard',
                          'n_status')
  ORDER BY j.id ASC) t2
ORDER BY id10 ASC LIMIT 20;

解釋分析：https ://explain.depesz.com/s/DU4

有沒有辦法改進這個查詢？

modified_date在和n_type列上創建了一個新索引：

CREATE INDEX ix_n_type_modified 
ON notes (n_type, timezone('Etc/UTC'::text, modified_date)) 
WHERE n_type IN ('n_1','n_custom','n_standard','n_status');

新的解釋分析：https ://explain.depesz.com/s/RsTr

查詢仍需 > 5 秒。

使用 Evan Carroll 提供的新查詢進行新的解釋分析：https ://explain.depesz.com/s/yP4S

詢問：

SELECT id AS id10
FROM
  FROM note j
  WHERE j.modified_date >= '2015-12-07 23:12:34.000000'
    AND j.clientid = 16049
    AND j.n_type  IN ('n',
                          'n_1',
                          'n_custom',
                          'n_standard',
                          'n_status')
ORDER BY id10 ASC
LIMIT 20;

查詢現在需要更長的時間。

note是一種看法。它join在工作表上有一個 ( note.jobid-> jobs.id)

我不能join在這些表之間做任何其他事情，因為它們之間沒有“連結”列。

\d+ note;:

表“public.note”
專欄 | 類型 | 修飾符 | 儲存 | 統計目標 | 描述 
----------------+--------------------------+-------------------------------------------------------------+----------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------
編號 | 大整數 | 不為空預設 "nextval"('"note_id_seq"'::"regclass") | 平原 | | 首要的關鍵
工作ID | 大整數 | 不為空 預設 0 | 平原 |
描述 | “文字” | 預設 ''::"文本" | 擴展 |
修改日期 | 帶時區的時間戳 | 預設 "statement_timestamp"() | 平原 | | 修改日期
n_type | "n_type" | | 平原 |

索引：
“note_pkey”主鍵，“btree”（“id”）
"ix_note_gsdi_pk" "btree" (("id"::"text"))
“ix_job_fk”“btree”（“jobid”）
“ix_job_n_type” “btree”（“n_type”）
"ix_note_jobid_type" "btree" ("jobid", "n_type")
"ix_note_jobid_type_2" "btree" ("jobid", "n_type", "timezone"('Etc/UTC'::"text", "modified_date"))

查看note模式note_user- 此視圖schema與表不同note：

SELECT r_30.id, r_30.jobid, 
  r_30.description,  
  timezone('Etc/UTC'::text, r_30.modified_date),  
  cj.clientid
 FROM public.note r_30
 JOIN public.jobs cj ON cj.id = r_30.jobid
 ;

表note：

CREATE TABLE public.note
(
 id bigint NOT NULL DEFAULT nextval('note_id_seq'::regclass), -- Primary key
 jobid bigint NOT NULL DEFAULT 0,
 description text DEFAULT ''::text,
n_type n_type,
 modified_date timestamp with time zone DEFAULT statement_timestamp(),
 CONSTRAINT note_pkey PRIMARY KEY (id)
 );

表jobs：

CREATE TABLE public.jobs
(
 id bigint NOT NULL DEFAULT nextval('jobs_id_seq'::regclass),
 clientid bigint NOT NULL DEFAULT 0,
 description text NOT NULL DEFAULT ''::text,
 modified_date timestamp without time zone DEFAULT statement_timestamp(),
 CONSTRAINT jobs_pkey PRIMARY KEY (id)
);

自定義n_type數據類型：

live_database=> \dT+ n_type
                                                    List of data types
Schema |      Name       |  Internal name  | Size |      Elements       | Access privileges |         Description          
--------+-----------------+-----------------+------+---------------------+-------------------+------------------------------
public | n_type          | n_type          | 4    | n_1                +|                   |
       |                 |                 |      | n_custom           +|                   | 
       |                 |                 |      | n_standard         +|                   | 
       |                 |                 |      | n_status           +|                   | 
(1 row)

而不是你所擁有的，刪除虛擬表並嘗試直接查詢。

SELECT DISTINCT id AS id10
FROM note j
WHERE j.modified_date >= '2016-10-01 23:12:34.000000'
 AND j.clientid = 16049
 AND j.n_type  IN ('n',
   'n_1',
   'n_custom',
   'n_standard',
   'n_status')
ORDER BY id10 ASC
LIMIT 20;

您有多個索引選項，很大程度上取決於您的每個WHERE條件的選擇性。您提到嘗試在 上添加索引n_type，但如果這與大多數（或大部分）記錄匹配，則不太可能改善情況。如果您有大量客戶，那麼您可以嘗試使用索引clientid，或者嘗試結合使用(clientid, modified_date)- 請記住，範圍術語必須在最後才有用。

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