|
Apologies for not replying on the full thread. We did the 3rd suggestion
@Scott, it worked with great improvement. The query is now running less than 300ms when created a composite index on brandid,playerid,placedon - however, we had to change the application
code to run the query in the same order(which is ok for the app). However, since I was telling other ripple effects, we're trying to test creating subpartitions by placedon and partitions by brandid like below. The reason being : we'll have to archive or purge the data post 90 days.
CREATE TABLE bets.emb ( betid varchar(50) NOT NULL, brandid varchar(50) NOT NULL, channelid varchar(50) NULL, playerid varchar(50) NULL, bet jsonb NULL, posid varchar(50) NULL, agentid varchar(50) NULL, bettype varchar(50) NULL, betclass varchar(20) NULL, betstatus varchar(15) NULL, placedon timestamptz NULL, settledon timestamptz NULL, unitcount int4 NULL, unitstake float8 NULL, totalstake float8 NULL, potentialreturn float8 NULL, legcount int4 NULL, openlegcount int4 NULL, selectionids _text NULL, marketids _text NULL, eventids _text NULL, competitionids _text NULL, sportids _text NULL, createdon timestamptz NULL, marketselectionids _text NULL, originalreturn float8 NULL, changelog _jsonb NULL, tags jsonb NULL, CONSTRAINT pk_ermabet PRIMARY KEY (brandid,placedon) ) partition by list( brandid); bets=> CREATE TABLE bets.emb_01 PARTITION OF bets.emb FOR VALUES IN ('pp') PARTITION BY RANGE(placedon); CREATE TABLE CREATE TABLE bets.emb_12_p2022 PARTITION OF bets.emb_01 FOR VALUES FROM ('2022-12-01 05:30:00+05:30') TO ('2023-01-01 05:30:00+05:30'); CREATE TABLE bets.emb_01_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-01-01 05:30:00+05:30') TO ('2023-02-01 05:30:00+05:30'); CREATE TABLE bets.emb_02_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-02-01 05:30:00+05:30') TO ('2023-03-01 05:30:00+05:30'); CREATE TABLE bets.emb_03_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-03-01 05:30:00+05:30') TO ('2023-04-01 05:30:00+05:30'); CREATE TABLE bets.emb_04_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-04-01 05:30:00+05:30') TO ('2023-05-01 05:30:00+05:30'); CREATE TABLE bets.emb_05_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-05-01 05:30:00+05:30') TO ('2023-06-01 05:30:00+05:30'); CREATE TABLE bets.emb_06_p2023 PARTITION OF bets.emb_01 FOR VALUES FROM ('2023-06-01 05:30:00+05:30') TO ('2023-07-01 05:30:00+05:30'); bets=> CREATE TABLE bets.emb_02 PARTITION OF bets.emb FOR VALUES IN ('nitro') PARTITION BY RANGE(placedon); CREATE TABLE CREATE TABLE bets.nitro_12_p2022 PARTITION OF bets.emb_02 FOR VALUES FROM ('2022-12-01 05:30:00+05:30') TO ('2023-01-01 05:30:00+05:30'); CREATE TABLE bets.nitro_01_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-01-01 05:30:00+05:30') TO ('2023-02-01 05:30:00+05:30'); CREATE TABLE bets.nitro_02_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-02-01 05:30:00+05:30') TO ('2023-03-01 05:30:00+05:30'); CREATE TABLE bets.nitro_03_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-03-01 05:30:00+05:30') TO ('2023-04-01 05:30:00+05:30'); CREATE TABLE bets.nitro_04_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-04-01 05:30:00+05:30') TO ('2023-05-01 05:30:00+05:30'); CREATE TABLE bets.nitro_05_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-05-01 05:30:00+05:30') TO ('2023-06-01 05:30:00+05:30'); CREATE TABLE bets.nitro_06_p2023 PARTITION OF bets.emb_02 FOR VALUES FROM ('2023-06-01 05:30:00+05:30') TO ('2023-07-01 05:30:00+05:30'); bets=> CREATE TABLE bets.emb_03 PARTITION OF bets.emb FOR VALUES IN ('21com') PARTITION BY RANGE(placedon); CREATE TABLE bets.com21_12_p2022 PARTITION OF bets.emb_03 FOR VALUES FROM ('2022-12-01 05:30:00+05:30') TO ('2023-01-01 05:30:00+05:30'); CREATE TABLE bets.com21_01_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-01-01 05:30:00+05:30') TO ('2023-02-01 05:30:00+05:30'); CREATE TABLE bets.com21_02_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-02-01 05:30:00+05:30') TO ('2023-03-01 05:30:00+05:30'); CREATE TABLE bets.com21_03_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-03-01 05:30:00+05:30') TO ('2023-04-01 05:30:00+05:30'); CREATE TABLE bets.com21_04_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-04-01 05:30:00+05:30') TO ('2023-05-01 05:30:00+05:30'); CREATE TABLE bets.com21_05_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-05-01 05:30:00+05:30') TO ('2023-06-01 05:30:00+05:30'); CREATE TABLE bets.com21_06_p2023 PARTITION OF bets.emb_03 FOR VALUES FROM ('2023-06-01 05:30:00+05:30') TO ('2023-07-01 05:30:00+05:30'); Regards, Phani Pratz PPBET-DBA -----Original Message----- > On May 22, 2023, at 7:17 AM, Scott Ribe <scott_ribe@xxxxxxxxxxxxxxxx> wrote: > > 3) don't partition on it, index (brandid, playerid, placedon) Oops, given that you're looking for latest placedon, not a specific one, make that (placedon, playerid, brandid) |
