On Thu, Mar 13, 2025 at 11:48 PM Durgamahesh Manne < maheshpostgr...@gmail.com> wrote:
> On Fri, 14 Mar, 2025, 09:11 Ron Johnson, <ronljohnso...@gmail.com> wrote: > >> On Thu, Mar 13, 2025 at 11:25 PM Durgamahesh Manne < >> maheshpostgr...@gmail.com> wrote: >> >>> On Fri, Mar 14, 2025 at 8:19 AM Ron Johnson <ronljohnso...@gmail.com> >>> wrote: >>> >>>> On Thu, Mar 13, 2025 at 10:16 PM Durgamahesh Manne < >>>> maheshpostgr...@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-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? >>>> >>>> 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? >> > > 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!
