public inbox for [email protected]help / color / mirror / Atom feed
Re: Query optimization 7+ messages / 4 participants [nested] [flat]
* Re: Query optimization @ 2025-03-13 19:17 Adrian Klaver <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Adrian Klaver @ 2025-03-13 19:17 UTC (permalink / raw) To: Durgamahesh Manne <[email protected]>; pgsql-general <[email protected]> On 3/13/25 12:12, Durgamahesh Manne wrote: > Hi Team > > This query takes more time than usual for execution Define usual. > How to optimize it in best possible way Can't be answered without, to start: 1) Postgres version. 2) Complete(including indexes) table schema. 3) Output of EXPLAIN ANALYZE of query. 4) In what client is this being run? > > Columns used in this query >> composite index eventhough not running > optimally > > > > SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId > = $2 AND BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND > EventId = $6 AND MarketId = $7 LIMIT ?) > > Regards > Durga Mahesh -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization @ 2025-03-14 02:18 Durgamahesh Manne <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: Durgamahesh Manne @ 2025-03-14 02:18 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; pgsql-general <[email protected]>; [email protected] On Fri, Mar 14, 2025 at 12:47 AM Adrian Klaver <[email protected]> wrote: > On 3/13/25 12:12, Durgamahesh Manne wrote: > > Hi Team > > > > This query takes more time than usual for execution > > Define usual. > > > > How to optimize it in best possible way > > Can't be answered without, to start: > > 1) Postgres version. > > 2) Complete(including indexes) table schema. > > 3) Output of EXPLAIN ANALYZE of query. > > 4) In what client is this being run? > > > > > > Columns used in this query >> composite index eventhough not running > > optimally > > > > > > > > SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId > > = $2 AND BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND > > EventId = $6 AND MarketId = $7 LIMIT ?) > > > > Regards > > Durga Mahesh > > -- > Adrian Klaver > [email protected] Hi Adrian Klaver 1) Postgres version. select version(); version --------------------------------------------------------------------------------------------------------------- PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit 2) Complete(including indexes) table schema. Table "liveaggregations.cachekeys" Column | Type | Collation | Nullable | Default | Storage | Compression ---------------+------------------------+-----------+----------+---------+----------+------------ cachetype | character varying(255) | | | | extended | trsid | character varying(255) | | | | extended | brandid | character varying(255) | | | | extended | sportid | character varying(255) | | | | extended | competitionid | character varying(255) | | | | extended | eventid | character varying(255) | | | | extended | marketid | character varying(255) | | | | extended | selectionid | character varying(255) | | | | extended | keytype | character varying(255) | | | | extended | key | character varying(255) | | not null | | extended | Indexes: "cachekeys_key_pk" PRIMARY KEY, btree (key) "idx_cachekeys" btree (cachetype, trsid, brandid, sportid, competitionid, eventid, marketid) "idx_marketid" btree (marketid) 3) Output of EXPLAIN ANALYZE of query. Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1 loops=1) InitPlan 1 (returns $0) -> Index Only Scan using idx_cachekeys on cachekeys (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1) Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid = 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid = 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid = 'U-523596'::text)) Heap Fetches: 0 Planning Time: 0.221 ms Execution Time: 0.046 ms 4) In what client is this being run? betting application Regards, Durga Mahesh ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization @ 2025-03-14 02:34 Rob Sargent <[email protected]> parent: Durgamahesh Manne <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: Rob Sargent @ 2025-03-14 02:34 UTC (permalink / raw) To: Durgamahesh Manne <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general <[email protected]>; [email protected] > > 3) Output of EXPLAIN ANALYZE of query. > > Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1 loops=1) > InitPlan 1 (returns $0) > -> Index Only Scan using idx_cachekeys on cachekeys (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1) > Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid = 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid = 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid = 'U-523596'::text)) > Heap Fetches: 0 > Planning Time: 0.221 ms > Execution Time: 0.046 ms > And is the explain analyze output if from your original query: "SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId = $2 AND BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND EventId = $6 AND MarketId = $7 LIMIT ?)” Has there been any significant addition of rows recently? i.e. are the statistics up-to-date for that table? ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization @ 2025-03-14 02:39 Durgamahesh Manne <[email protected]> parent: Rob Sargent <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Durgamahesh Manne @ 2025-03-14 02:39 UTC (permalink / raw) To: Rob Sargent <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general <[email protected]>; [email protected] On Fri, 14 Mar, 2025, 08:04 Rob Sargent, <[email protected]> wrote: > > > > 3) Output of EXPLAIN ANALYZE of query. > > Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1 > loops=1) > InitPlan 1 (returns $0) > -> Index Only Scan using idx_cachekeys on cachekeys > (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1) > Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid = > 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid = > 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid = > 'U-523596'::text)) > Heap Fetches: 0 > Planning Time: 0.221 ms > Execution Time: 0.046 ms > > > > And is the explain analyze output if from your original query: > "SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType = $1 AND TrsId > = $2 AND BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND EventId = > $6 AND MarketId = $7 LIMIT ?)” > > Has there been any significant addition of rows recently? i.e. are the > statistics up-to-date for that table? > Hi Gave you plan with out limit . Stats up to date insert .....on conflict do nothing runs some times Regards Durga Mahesh > > > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization @ 2025-03-14 02:49 Rob Sargent <[email protected]> parent: Durgamahesh Manne <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Rob Sargent @ 2025-03-14 02:49 UTC (permalink / raw) To: Durgamahesh Manne <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-general <[email protected]>; [email protected] > On Mar 13, 2025, at 8:39 PM, Durgamahesh Manne <[email protected]> wrote: > > > > On Fri, 14 Mar, 2025, 08:04 Rob Sargent, <[email protected] <mailto:[email protected]>> wrote: > > Hi > > Gave you plan with out limit . Stats up to date insert .....on conflict do nothing runs some times > > Regards > Durga Mahesh > > But still using the “select exists”, thereby turning the result into a true/false value? I do not follow your point on the usage of “on conflict”. Is this also something whose behaviour has changed recently? I assume it references the same table as the original sql? ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization @ 2025-03-14 02:49 Ron Johnson <[email protected]> parent: Durgamahesh Manne <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: Ron Johnson @ 2025-03-14 02:49 UTC (permalink / raw) To: pgsql-general On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne < [email protected]> wrote: [snip] > Hi Adrian Klaver > > 1) Postgres version. > select version(); > version > > --------------------------------------------------------------------------------------------------------------- > PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) > 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit > > 2) Complete(including indexes) table schema. > > Table > "liveaggregations.cachekeys" > Column | Type | Collation | Nullable | Default | > Storage | Compression > > ---------------+------------------------+-----------+----------+---------+----------+------------ > cachetype | character varying(255) | | | | > extended | > trsid | character varying(255) | | | | > extended | > brandid | character varying(255) | | | | > extended | > sportid | character varying(255) | | | | > extended | > competitionid | character varying(255) | | | | > extended | > eventid | character varying(255) | | | | > extended | > marketid | character varying(255) | | | | > extended | > selectionid | character varying(255) | | | | > extended | > keytype | character varying(255) | | | | > extended | > key | character varying(255) | | not null | | > extended | > Indexes: > "cachekeys_key_pk" PRIMARY KEY, btree (key) > "idx_cachekeys" btree (cachetype, trsid, brandid, sportid, > competitionid, eventid, marketid) > "idx_marketid" btree (marketid) > > 3) Output of EXPLAIN ANALYZE of query. > > Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 rows=1 > loops=1) > InitPlan 1 (returns $0) > -> Index Only Scan using idx_cachekeys on cachekeys > (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1) > Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid = > 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid = > 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid = > 'U-523596'::text)) > Heap Fetches: 0 > Planning Time: 0.221 ms > Execution Time: 0.046 ms > That looks pretty reasonable. 1. Now show what happens with the LIMIT clause. 2. How many rows does it return? 3. Do you keep the table regularly vacuumed and analyzed? -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query optimization @ 2025-03-14 03:27 Durgamahesh Manne <[email protected]> parent: Ron Johnson <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Durgamahesh Manne @ 2025-03-14 03:27 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: pgsql-general; pgsql-general <[email protected]> On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <[email protected]> wrote: > On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne < > [email protected]> wrote: > [snip] > >> Hi Adrian Klaver >> >> 1) Postgres version. >> select version(); >> version >> >> --------------------------------------------------------------------------------------------------------------- >> PostgreSQL 14.12 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) >> 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bit >> >> 2) Complete(including indexes) table schema. >> >> Table >> "liveaggregations.cachekeys" >> Column | Type | Collation | Nullable | Default >> | Storage | Compression >> >> ---------------+------------------------+-----------+----------+---------+----------+------------ >> cachetype | character varying(255) | | | >> | extended | >> trsid | character varying(255) | | | >> | extended | >> brandid | character varying(255) | | | >> | extended | >> sportid | character varying(255) | | | >> | extended | >> competitionid | character varying(255) | | | >> | extended | >> eventid | character varying(255) | | | >> | extended | >> marketid | character varying(255) | | | >> | extended | >> selectionid | character varying(255) | | | >> | extended | >> keytype | character varying(255) | | | >> | extended | >> key | character varying(255) | | not null | >> | extended | >> Indexes: >> "cachekeys_key_pk" PRIMARY KEY, btree (key) >> "idx_cachekeys" btree (cachetype, trsid, brandid, sportid, >> competitionid, eventid, marketid) >> "idx_marketid" btree (marketid) >> >> 3) Output of EXPLAIN ANALYZE of query. >> >> Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.030..0.030 >> rows=1 loops=1) >> InitPlan 1 (returns $0) >> -> Index Only Scan using idx_cachekeys on cachekeys >> (cost=0.55..2.80 rows=1 width=0) (actual time=0.029..0.029 rows=1 loops=1) >> Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid = >> 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid = >> 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid = >> 'U-523596'::text)) >> Heap Fetches: 0 >> Planning Time: 0.221 ms >> Execution Time: 0.046 ms >> > > That looks pretty reasonable. > > 1. Now show what happens with the LIMIT clause. > 2. How many rows does it return? > 3. Do you keep the table regularly vacuumed and analyzed? > > -- > Death to <Redacted>, and butter sauce. > Don't boil me, I'm still alive. > <Redacted> lobster! > Hey Ron 1. Now show what happens with the LIMIT clause. and result set of query and *Size of the table 287MB* exists -------- t (1 row) -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result (cost=2.80..2.83 rows=1 width=1) (actual time=0.029..0.030 rows=1 loops=1) InitPlan 1 (returns $0) -> Index Only Scan using idx_cachekeys on cachekeys (cost=0.55..2.80 rows=1 width=0) (actual time=0.028..0.028 rows=1 loops=1) Index Cond: ((cachetype = 'BoMatrix'::text) AND (trsid = 'daznbetuk'::text) AND (brandid = 'daznbet'::text) AND (sportid = 'BOX'::text) AND (competitionid = 'U-1998'::text) AND (eventid = 'U-523596'::text)) Heap Fetches: 1 Planning Time: 0.084 ms Execution Time: 0.043 ms (7 rows) 2. How many rows does it return? One row exists -------- t (1 row) 3. Do you keep the table regularly vacuumed and analyzed? Auto vacuum already in place along with periodic maintenance activity such as vacuum and analyze runs daily once Regards, Durga Mahesh ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-03-14 03:27 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-03-13 19:17 Re: Query optimization Adrian Klaver <[email protected]> 2025-03-14 02:18 ` Durgamahesh Manne <[email protected]> 2025-03-14 02:34 ` Rob Sargent <[email protected]> 2025-03-14 02:39 ` Durgamahesh Manne <[email protected]> 2025-03-14 02:49 ` Rob Sargent <[email protected]> 2025-03-14 02:49 ` Ron Johnson <[email protected]> 2025-03-14 03:27 ` Durgamahesh Manne <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox