Query optimization
Hi Team
This query takes more time than usual for execution
How to optimize it in best possible way
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
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
optimallySELECT 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
adrian.klaver@aklaver.com
On Fri, 2025-03-14 at 00:42 +0530, Durgamahesh Manne wrote:
This query takes more time than usual for execution
How to optimize it in best possible way
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 ?)
"The best possible way" is hard to say, because an index will have negative
effects on data modification statements.
But for the fastest execution of the query, simply create an index on all the
columns that occur in the WHERE clause. The order doesn't matter.
And remove that useless LIMIT clause.
Yours,
Laurenz Albe
On Fri, Mar 14, 2025 at 12:47 AM Adrian Klaver <adrian.klaver@aklaver.com>
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
optimallySELECT 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
adrian.klaver@aklaver.com
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
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?
On Fri, 14 Mar, 2025, 08:04 Rob Sargent, <robjsargent@gmail.com> 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 msAnd 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
Show quoted text
On Mar 13, 2025, at 8:39 PM, Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:
On Fri, 14 Mar, 2025, 08:04 Rob Sargent, <robjsargent@gmail.com <mailto:robjsargent@gmail.com>> 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?
On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> 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-bit2) 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!
On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> 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-bit2) 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 msThat 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
On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> wrote:
On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com>
wrote:On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> 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-bit2) 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 msThat 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?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
This might be due to caching. Run the query with LIMIT three times, and
then remove the LIMIT and run three times.
Honestly, though, the execution timings seem pretty good. What exactly is
the problem?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On Fri, 14 Mar, 2025, 09:11 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> wrote:On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com>
wrote:On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> 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-bit2) 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 msThat 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?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 msThis might be due to caching. Run the query with LIMIT three times, and
then remove the LIMIT and run three times.Honestly, though, the execution timings seem pretty good. What exactly is
the problem?--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Hi Team and Andrian
LIMIT is not necessary to use in select here in this case
To return one row takes 43ms is not optimal
Regards,
Durga Mahesh
Show quoted text
On Thursday, March 13, 2025, Durgamahesh Manne <maheshpostgres9@gmail.com>
wrote:
------------------------------------------------------------
------------------------------------------------------------
------------------------------------------------------------
--------------------------------------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 msTo return one row takes 43ms is not optimal
You are off by a factor of 1000 in your claimed performance. It’s 0.043ms
David J.
On Thu, Mar 13, 2025 at 11:48 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> wrote:
On Fri, 14 Mar, 2025, 09:11 Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> wrote:On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnsonjr@gmail.com>
wrote:On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> 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-bit2) 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 msThat 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?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 msThis might be due to caching. Run the query with LIMIT three times, and
then remove the LIMIT and run three times.Honestly, though, the execution timings seem pretty good. What exactly
is the problem?Hi Team and Andrian
LIMIT is not necessary to use in select here in this case
To return one row takes 43ms is not optimal
What did it used to take?
Planning takes 2x as long as execution. What if you just run "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" and
change app so that "returns one or more rows means true"?
This is also a valid method:
SELECT COUNT(*) FROM CACHEKEYS WHERE CacheType = $1 AND TrsId = $2 AND
BrandId = $3 AND SportId = $4 AND CompetitionId = $5 AND EventId = $6 AND
MarketId = $7
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On Thu, Mar 13, 2025 at 11:49 PM Durgamahesh Manne <
maheshpostgres9@gmail.com> wrote:
To return one row takes 43ms is not optimal
It's actually much faster than that, but even 43ms is overall good. The
query is already pretty optimal, as it uses a single index only scan. There
are a few tricks left to make this potentially faster, but you may want to
look into using some sort of in-memory caching system if your application
is that sensitive to timings. Or find us a version in which the execution
time is not 1/20th of a millisecond and we can work on that one.
Also take a look at pg_stat_statements so you can see how fast the query is
on average.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support