Index
努力為我的 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);