努力為我的 PSQL 查詢創建索引

我正在對包含 3000 萬行並且只會繼續增長的數據集執行查詢，該表是 customer_actions（表大小為 2416 MB）

create table customer_actions
(
   id          bigint not null
       constraint customer_actions_pkey
           primary key,
   action      text,
   customer_id bigint,
   product_id  bigint,
   item_type   text,
   create_date timestamp
);

我嘗試了各種各樣的索引，但是查看查詢的 exaplin，沒有任何內容被擊中

SELECT customer_id, product_id, count(*) AS count
from customer_actions
WHERE action = 'a2b'
     AND item_type = 'wine'
     AND create_date BETWEEN current_timestamp - INTERVAL '2 Years' AND current_timestamp
GROUP BY customer_id, product_id

SELECT customer_id, product_id, count(*) AS count
from customer_actions
WHERE action = 'view'
     AND item_type = 'wine'
     AND create_date BETWEEN current_timestamp - INTERVAL '2 Years' AND current_timestamp
GROUP BY customer_id, product_id

SELECT customer_id, product_id, count(*) AS count
from customer_actions
WHERE action = 'buy'
     AND item_type = 'wine'
     AND create_date BETWEEN current_timestamp - INTERVAL '2 Years' AND current_timestamp
GROUP BY customer_id, product_id

我嘗試過的索引，其中一些我知道不會起作用但我抓住了稻草，所有最後有條件的索引也都嘗試過沒有條件。不要以為任何人都能為我指明正確的方向，我對 PSQL 還很陌生，對索引還沒有深入的了解。

CREATE  INDEX IF NOT EXISTS idx_14 on customer_actions (customer_id, product_id, 
create_date, action, item_type) where action = 'a2b'
CREATE  INDEX IF NOT EXISTS idx_15 on customer_actions (customer_id, product_id, action, item_type) where action = 'a2b'
CREATE  INDEX IF NOT EXISTS idx_16 on customer_actions (customer_id, product_id) where action = 'a2b'
CREATE INDEX IF NOT EXISTS idx_11 on customer_actions (item_type, action ) where item_type = 'wine' and action = 'a2b';
CREATE INDEX IF NOT EXISTS idx_12 on customer_actions (item_type, action ) where item_type = 'wine' and action = 'view' ;
CREATE INDEX IF NOT EXISTS idx_13 on customer_actions (item_type, action ) where item_type = 'wine' and action = 'buy' ;
CREATE INDEX idx_time on customer_actions using brin (create_date);
create index idx_actions_a2b on customer_actions (action) where action = 'a2b'
CREATE INDEX IF NOT EXISTS idx_customer_actions_action_product_cardinality_order on customer_actions (customer_id, product_id, action);
CREATE INDEX id_time_and_other on customer_actions (action, item_type, create_date DESC)
CREATE INDEX IF NOT EXISTS idx_customer_actions_product_and_customer on customer_actions (customer_id, product_id)
CREATE  INDEX IF NOT EXISTS idx_14 on customer_actions (customer_id, product_id, create_date, action, item_type)
CREATE  INDEX IF NOT EXISTS idx_14 on customer_actions (customer_id, product_id, create_date, action)
CREATE  INDEX IF NOT EXISTS idx_17 on customer_actions (customer_id, product_id)

查詢的解釋是

Finalize GroupAggregate  (cost=745877.49..1182094.60 rows=1527687 width=24)
"  Group Key: customer_id, product_id"
 ->  Gather Merge  (cost=745877.49..1143902.43 rows=3055374 width=24)
       Workers Planned: 2
       ->  Partial GroupAggregate  (cost=744877.46..790236.43 rows=1527687 width=24)
"              Group Key: customer_id, product_id"
             ->  Sort  (cost=744877.46..752397.99 rows=3008210 width=16)
"                    Sort Key: customer_id, product_id"
                   ->  Parallel Seq Scan on customer_actions  (cost=0.00..318363.94 rows=3008210 width=16)
                         Filter: ((action = 'a2b'::text) AND (item_type = 'wine'::text) AND (create_date <= CURRENT_TIMESTAMP) AND (create_date >= (CURRENT_TIMESTAMP - '2 years'::interval)))

查詢的最佳索引將所有想要的記錄組合在一起

想像一下，如果你說ORDER BY action,item_type, create_date

CREATE INDEX IF NOT EXISTS ca_aid on customer_actions (action, item_type, create_date);

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