|
Hi All, I’ve a table ermabet having 80mil records. My business need is to check the bet placed by a player(users), which queries the table, and enable streaming for the player only
if he has placed his bet in the last 24 hrs( granular to the millisecond). I can’t create a partition by range(date) and the query is taking more than 5 mins, whereas we’re expecting the query to run less than 300ms.
My Desired Partitions:
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 (betid, brandid) ) PARTITION BY RANGE (placedon); Detail: PRIMARY KEY constraint on table "ermabet_part_test" lacks column "placedon" which is part of the partition key. Error position: CREATE TABLE bets.ermabet ( 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 (betid, brandid, placedon) ) PARTITION BY RANGE (placedon); -- Step 2: Create child tables for each partition CREATE TABLE bets.ermabet_2022 PARTITION OF bets.ermabet FOR VALUES FROM ('2022-01-01') TO ('2023-01-01'); -- Add more child tables for other date ranges as needed -- Step 3: Create indexes on child tables CREATE INDEX ermabet_bstatus_idx ON bets.ermabet_2022 USING btree (betstatus); CREATE INDEX ermabet_mktids_idx ON bets.ermabet_2022 USING gin (marketids); CREATE INDEX ermabet_tgs_idx ON bets.ermabet_2022 USING gin (tags); CREATE INDEX idx_ermabet_stdon ON bets.ermabet_2022 USING btree (settledon); CREATE INDEX idx_ermabetbet_pcdon ON bets.ermabet_2022 USING btree (placedon); -- Repeat the index creation for other child tables as needed But I don’t want
PLACEDON column part of the primary key. Any suggestions? Regards, Phani Pratz PPBET-DBA |
