Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS

Where's the playerid index?

On 5/22/23 01:55, Phani Prathyush Somayajula wrote:
>
> Furthermore, the explain plan(without partition) looks like this :
>
> Finalize Aggregate  (cost=20494220.72..20494220.75 rows=1 width=8)
>
>   ->  Gather (cost=20494220.67..20494220.70 rows=6 width=8)
>
>         Workers Planned: 6
>
>         ->  Partial Aggregate (cost=20494210.67..20494210.70 rows=1 width=8)
>
>               ->  Parallel Bitmap Heap Scan on ermabet e 
> (cost=420824.55..20494210.60 rows=26 width=0)
>
>                     Recheck Cond: ((placedon >= '2023-03-28
> 08:20:23.927+05:30'::timestamp with time zone) AND (placedon <=
> '2023-03-29 08:19:23.927+05:30'::timestamp with time zone))
>
>                     Filter: (((brandid)::text = 'pp'::text) AND
> ((playerid)::text = 'Periša_80'::text))
>
>                     ->  Bitmap Index Scan on idx_ermabetbet_pcdon 
> (cost=0.00..420824.51 rows=27478794 width=0)
>
>                           Index Cond: ((placedon >= '2023-03-28
> 08:20:23.927+05:30'::timestamp with time zone) AND (placedon <=
> '2023-03-29 08:19:23.927+05:30'::timestamp with time zone))
>
> JIT:
>
>   Functions: 9
>
>   Options: Inlining true, Optimization true, Expressions true, Deforming true
>
> Regards,
>
> Phani Pratz
>
> PPBET-DBA
>
> *From:*Phani Prathyush Somayajula <phani(dot)somayajula(at)pragmaticplay(dot)com>
> *Sent:* Monday, May 22, 2023 12:24 PM
> *To:* pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* Partition By Range Without PrimaryKey : Postgresql Version 12.8
> on AWS RDS
>
> 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:
>
> CREATE TABLE bets.ermabet_partition (
>
>     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);
>
> for which I get the error :
> SQL Error [0A000]: ERROR: unique constraint on partitioned table must
> include all partitioning columns
>
>   Detail: PRIMARY KEY constraint on table "ermabet_part_test" lacks column
> "placedon" which is part of the partition key.
>
> Error position:
>
>
>
> error less partition creation is :
>
> -- Step 1: Create the parent table
>
> 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
>

--
Born in Arizona, moved to Babylonia.