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.
This is confusing. Is your main question about performance, or about designing a primary key, or about designing partitioning? They are pretty different things. If you fix the performance problem the right way, do you need partitioning at all? An 80 million row table is not all that large, I would normally not think partitioning it would be very important.
-- Step 3: Create indexes on child tables
CREATE INDEX ermabet_bstatus_idx ON bets.ermabet_2022 USING btree (betstatus);
If you create indexes on the parent table, they will automatically be created on each child. Only if you want different children to have different indexes should you create them separately on the children.
Based on your plan shared in a different email, you need an index on (brandid, playerid, placedon). With the right index, I doubt partitioning would be needed.