如何創建要由查詢選擇的索引

我一個接一個地創建了多個索引，但我的查詢不想選擇它們:(

CREATE INDEX x 
 ON gan.paid (project_id, country_iso_code, source, date(created_at));
CREATE INDEX x 
 ON gan.paid (project_id, country_iso_code, text(source), date(created_at));
CREATE INDEX x 
 ON gan.paid (project_id, text(country_iso_code), text(source), date(created_at));

查詢計劃：

WindowAgg  (cost=46024.07..52771.64 rows=24252 width=63) (actual time=3038.104..3072.217 rows=99983 loops=1)
->  GroupAggregate  (cost=46024.07..52468.49 rows=24252 width=63) (actual time=2284.136..2982.067 rows=99983 loops=1)
   Group Key: key
   ->  Sort  (cost=46024.07..46535.85 rows=204709 width=63) (actual time=2284.109..2805.815 rows=198575 loops=1)
         Sort Key: key
         Sort Method: external merge  Disk: 13960kB
         ->  Seq Scan on paid gap  (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1)
               Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at <= (now())::date) AND (created_at >= ((now())::date - 100)))
               Rows Removed by Filter: 214895
Planning time: 0.233 ms
Execution time: 3082.612 ms

詢問：

SELECT sum(views) vies
    , sum(likes) likes
    , key
    , count(*) OVER() full_count
FROM gan.paid
WHERE project_id = 1 
   AND country_iso_code = 'gb' 
   AND source = 'website' 
   AND created_at::date >= now()::date - 100 
   AND created_at::date <= now()::date
GROUP BY key;

表定義：

CREATE TABLE gan.paid (
   id               integer pk
   key              text
   project_id       integer
   source           varchar
   country_iso_code varchar
   visits           integer
   likes            integer
   created_at       date
);

我怎樣才能創建一個索引來加速這個查詢？

這是您期望使用索引的執行計劃的一部分：

     ->  Seq Scan on paid gap  (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1)
           Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at <= (now())::date) AND (created_at >= ((now())::date - 100)))
           Rows Removed by Filter: 214895

我們要查看的是該節點返回的行數（198575）和不返回的行數（Rows Removed by Filter: 214895）。這意味著您的WHERE子句中的條件並不是真正有選擇性的——它們指定了整個表的近一半。

這是索引很少有幫助的情況——當返回的行的百分比估計低於 10%（其他人說更少，比如 5%）時，通常會說索引會起作用。

您可以使用更具選擇性的一組條件嘗試相同的查詢（實際上，設置更窄的日期範圍）。在這種情況下，索引可能會有所幫助 - 例如，您在上面列出的第一個。根據數據的分佈（ 、 和 的不同值project_id，source以及country_iso_code具有特定值的所有行的比例），列的不同順序可能更可取（例如，created_at::date作為第一列）。如果其他列在任何情況下都沒有足夠的選擇性（例如，所有行的項目 ID 為 1），則可能created_at::date只是您需要。

同時，您的問題似乎不是（缺少）索引，而是查詢對太多數據進行了排序。這個節點：

->  Sort  (cost=46024.07..46535.85 rows=204709 width=63) (actual time=2284.109..2805.815 rows=198575 loops=1)
     Sort Key: key
     Sort Method: external merge  Disk: 13960kB

顯示work_mem設置太低 - 查詢需要至少 13960 kB 更多的記憶體來進行這種排序。做一個

SET work_mem TO xMB;

其中 x 是 {您的目前設置} + 16 MB。這將為您節省大約 2 秒的時間。

筆記：

• 轉換varchar為text對您的索引沒有幫助。
• 這個查詢只是更大圖景的一部分嗎？如果不是，我會多考慮一下我打算如何處理返回的近 100k 行。這可能意味著對您的邏輯進行（輕微）重新設計。

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