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? > > -- > 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 >
