pg.ddx.io pgsql-admin@postgresql.org mailing list archive help / color / mirror / Atom feed From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS
Date: Mon, 22 May 2023 02:07:20 -0500
Message-ID: <d0a5571b-b157-5477-98d5-4300c2ee9e18@gmail.com> (raw )
In-Reply-To: <VI1PR10MB7671180739AB51DF1455A1A48D439@VI1PR10MB7671.EURPRD10.PROD.OUTLOOK.COM >
References: <VI1PR10MB76711B1B17D38E3CFF4E73008D439@VI1PR10MB7671.EURPRD10.PROD.OUTLOOK.COM >
<VI1PR10MB7671180739AB51DF1455A1A48D439@VI1PR10MB7671.EURPRD10.PROD.OUTLOOK.COM >
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.somayajula@pragmaticplay.com>
> *Sent:* Monday, May 22, 2023 12:24 PM
> *To:* pgsql-admin@lists.postgresql.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.
view thread (18+ messages) latest in thread
Message-ID: <d0a5571b-b157-5477-98d5-4300c2ee9e18@gmail.com>
Permalink: ../d0a5571b-b157-5477-98d5-4300c2ee9e18@gmail.com/
Also on: postgresql.org/message-id/d0a5571b-b157-5477-98d5-4300c2ee9e18@gmail.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Partition By Range Without PrimaryKey : Postgresql Version 12.8 on AWS RDS
In-Reply-To: <d0a5571b-b157-5477-98d5-4300c2ee9e18@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox