0

I'm running a query on a dataset that's got 30 million rows in and will only continue to grow, the table is customer_actions (Table size is 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
);

I've tried a large variety of indexes, but looking at the exaplin of the query, nothing is being hit

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

Indexes I've tried, some of them I know wouldn't have worked but I was grasping at straws, all the ones with a condition on the end have also been tried without the condition. Don't suppose anyone can point me in the right direction, I'm pretty new to PSQL and don't yet have a deep understanding of indexes.

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)

The exaplin of the query is

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)))

enter image description here

0
0

The best index for a query will group all the wanted records together

Immagine if you said ORDER BY action,item_type, create_date

CREATE INDEX IF NOT EXISTS ca_aid on customer_actions (action, item_type, create_date);
1
  • Ahh okay, this index worked thank you. I was searching for a create date that spanned the entire database. Doing an 18 month used the index!
    – Shane
    Aug 27 at 10:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.