Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tsw2X-006vTE-Mu for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 03:48:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tsw2W-00Dtnq-AJ for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 03:48:52 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tsw2V-00DtnQ-N3 for pgsql-general@lists.postgresql.org; Fri, 14 Mar 2025 03:48:51 +0000 Received: from mail-ej1-x62a.google.com ([2a00:1450:4864:20::62a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tsw2T-002jDh-1W for pgsql-general@postgresql.org; Fri, 14 Mar 2025 03:48:50 +0000 Received: by mail-ej1-x62a.google.com with SMTP id a640c23a62f3a-ac2bfcd2a70so223690866b.0 for ; Thu, 13 Mar 2025 20:48:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741924128; x=1742528928; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=waVe2Z5tTMEauNpPsFprc2PpcsYI7xnw9MdkXSjA7OI=; b=DgXrEAMnFxnFGjOlMHklF6QjGHbl1nUWTeW3RKwHH5Lz82z+RsPVsOMdQnExzUGycU DFTkWIxT4eY4rLVseOIrq/516N1Of3kRRBifkIu9/y22Ouhfq7S1y5iqFksq1ZYq/v7d WVS/Jr3v+7LH3FWm2tCvN1VwKjNaUvr5FupCa5i2TfeDkzyJTrVqW98MDHFo3dqYPFhr 99p8FwCEtA6VU/+v3GypLOl2IYvL5gc3H+tcV98Joh3Bttd0lNnpO714hhhOxzs/0g5q r6pUKb2/ti1veUdb+t31CaR9DA9kRP5LsqvdPrP99Z5b/8DW5bTKRdoFyayNV41gwX69 OrCA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741924128; x=1742528928; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=waVe2Z5tTMEauNpPsFprc2PpcsYI7xnw9MdkXSjA7OI=; b=FUDhPqxfA9yTdmfv1T26If78czhM5vjCkBJKr8a5T4PGurZ2ZluHKvTftQBCR7YiRH 4rX+DDmy0HsZ4woVWx3NPJHPaTnebqt518gGnqwpQVbESIJgGfnRRyxHavOKAZq4XdeA 9YkXpExJQBGhbtp+x5Z9IhieByHoI+VJinjQBXQiEUO8GzJSJpPZRGOoJYpI5qP+fuft rVM+stlveX1BkrOJvYtbzZm4qEyHLoF32RvEVbt1IOshu5BxtavqnLOige3Vj6jWm1nt sD+bTL0ifk3+8idc/TCI6LQun926Tiew0rPz6BzhpBmpW2lBpz1g1suHGO/t8dZmZGoK 1pqg== X-Gm-Message-State: AOJu0YzKel92jBGEhzIgAu6l0yErOqHnnT30alwRXrlfqL6Vn8qofpYC CMRUik8ve0BSXYRnK6ym0g75uBhXptpHwrN/E1UfbZ4oGzdSqtniyFhIW0KQATT9P6NRxMLGlCn Fi7vAbL5SbyRNny96rYSL5kJSMUfMU7UC X-Gm-Gg: ASbGncu0q1j5hc3zy8MVMfJCVBRLSH4C8k8bt/ph3nZ+ggUU4BOhE+VtB9fOCTg5IcK Hof47r7/Ctg4+Rq27qM2J3LZkQ4tmXHFFr98L9/fUKODgHuytFJtB6UrhvLxcVgMP2DpK240z9P SQLLKgWIx1+gZ+FENf8BubSXsEPGsJqa1H1ET+ X-Google-Smtp-Source: AGHT+IGCwy9l3b7IPNYfIZe047tC+X6in64cGNAFs+kTV2HtKCFuOy+iVR9Hmk83Ss0R1x25ZpVYXDSb9HFzkQw26XA= X-Received: by 2002:a17:906:794d:b0:ac1:fea1:3b68 with SMTP id a640c23a62f3a-ac3303f75bamr86539166b.41.1741924127830; Thu, 13 Mar 2025 20:48:47 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Durgamahesh Manne Date: Fri, 14 Mar 2025 09:18:35 +0530 X-Gm-Features: AQ5f1Jrtz2EGZTf0ASBWMRh4GGGzpUW4DOUGYr33NTCtOBUtZpe0PQgm3Kcp0Pw Message-ID: Subject: Re: Query optimization To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000dd7be106304550a2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000dd7be106304550a2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, 14 Mar, 2025, 09:11 Ron Johnson, wrote: > On Thu, Mar 13, 2025 at 11:25=E2=80=AFPM Durgamahesh Manne < > maheshpostgres9@gmail.com> wrote: > >> On Fri, Mar 14, 2025 at 8:19=E2=80=AFAM Ron Johnson >> wrote: >> >>> On Thu, Mar 13, 2025 at 10:16=E2=80=AFPM 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-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=3D2.80..2.83 rows=3D1 width=3D1) (actual time=3D0.030..= 0.030 >>>> rows=3D1 loops=3D1) >>>> InitPlan 1 (returns $0) >>>> -> Index Only Scan using idx_cachekeys on cachekeys >>>> (cost=3D0.55..2.80 rows=3D1 width=3D0) (actual time=3D0.029..0.029 ro= ws=3D1 loops=3D1) >>>> Index Cond: ((cachetype =3D 'BoMatrix'::text) AND (trsid = =3D >>>> 'daznbetuk'::text) AND (brandid =3D 'daznbet'::text) AND (sportid =3D >>>> 'BOX'::text) AND (competitionid =3D 'U-1998'::text) AND (eventid =3D >>>> '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=3D2.80..2.83 rows=3D1 width=3D1) (actual time=3D0.029..0.= 030 >> rows=3D1 loops=3D1) >> InitPlan 1 (returns $0) >> -> Index Only Scan using idx_cachekeys on cachekeys >> (cost=3D0.55..2.80 rows=3D1 width=3D0) (actual time=3D0.028..0.028 rows= =3D1 loops=3D1) >> Index Cond: ((cachetype =3D 'BoMatrix'::text) AND (trsid =3D >> 'daznbetuk'::text) AND (brandid =3D 'daznbet'::text) AND (sportid =3D >> 'BOX'::text) AND (competitionid =3D 'U-1998'::text) AND (eventid =3D >> '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 i= s > the problem? > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > 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 > --000000000000dd7be106304550a2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Fri, 14 Mar, 2025, 09:11 Ron = Johnson, <ronljohnsonjr@gmail= .com> wrote:
On Thu, Mar 13, 2025 at 11:25=E2=80=AFPM Durgamahesh Man= ne <maheshpostgres9@gmail.com> wrote:
On Fri, Mar 14, 2025 at 8:19=E2=80=AFAM Ron Johnson = <ronljohnsonjr@gmail.com> wrote:
On Thu, Mar 13, 2025 at 10:16=E2=80=AFPM Durgamahesh Manne &l= t;maheshpostgres9@gmail.com> wrote:
[snip]=C2=A0
Hi Adrian = Klaver

1) Postgres version.
=C2=A0select version();
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 version<= br>------------------------------------------------------------------------= ---------------------------------------
=C2=A0PostgreSQL 14.12 on aarch6= 4-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.

=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 Table "liveaggregations.cachekeys"
=C2=A0 =C2=A0 Column= =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Type =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0| Collation | Nullable | Default | Storage =C2=A0| Compres= sion
---------------+------------------------+-----------+----------+---= ------+----------+------------
=C2=A0cachetype =C2=A0 =C2=A0 | character= varying(255) | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0trsid =C2=A0 =C2=A0 =C2=A0 =C2=A0 | chara= cter varying(255) | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0brandid =C2=A0 =C2=A0 =C2=A0 | charact= er varying(255) | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0sportid =C2=A0 =C2=A0 =C2=A0 | character = varying(255) | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0competitionid | character varying(255) | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| = =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0
=C2=A0eventid =C2=A0 =C2=A0 =C2=A0 | character varying(255) | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| = =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0
=C2=A0marketid =C2=A0 =C2=A0 =C2=A0| character varying(255) | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| = =C2=A0 =C2=A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0
=C2=A0selectionid =C2=A0 | character varying(255) | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0keytype =C2=A0 =C2=A0 =C2=A0 | character varying(255) | =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0key =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | character varying(255) | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null | =C2=A0 =C2=A0 =C2=A0 =C2=A0= | extended | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
Indexes:
=C2= =A0 =C2=A0 "cachekeys_key_pk" PRIMARY KEY, btree (key)
=C2=A0 = =C2=A0 "idx_cachekeys" btree (cachetype, trsid, brandid, sportid,= competitionid, eventid, marketid)
=C2=A0 =C2=A0 "idx_marketid"= ; btree (marketid)

3) Output of EXPLAIN= ANALYZE of query.

=C2=A0Result =C2=A0(cost=3D2.80..2.83 rows=3D1 width=3D1) (act= ual time=3D0.030..0.030 rows=3D1 loops=3D1)
=C2=A0 =C2=A0InitPlan 1 (ret= urns $0)
=C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Only Scan using idx_cache= keys on cachekeys =C2=A0(cost=3D0.55..2.80 rows=3D1 width=3D0) (actual time= =3D0.029..0.029 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0Index Cond: ((cachetype =3D 'BoMatrix'::text) AND (trsid =3D = 'daznbetuk'::text) AND (brandid =3D 'daznbet'::text) AND (s= portid =3D 'BOX'::text) AND (competitionid =3D 'U-1998'::te= xt) AND (eventid =3D 'U-523596'::text))
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0Heap Fetches: 0
=C2=A0Planning Time: 0.221 ms
=C2=A0= Execution Time: 0.046 ms

Th= at looks pretty reasonable.
=C2=A0
1. Now show what hap= pens with the LIMIT clause.
2. How many rows does it return?
3. Do you keep the table regularly vacuumed and analyzed?
<= br>
Hey Ron

1. Now show what happens with the LIMIT clause.
and result set = of query=C2=A0 and Size=C2=A0of the table 287MB
=C2= =A0exists
--------
=C2=A0t
(1 row)
----------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------
=C2=A0Result =C2=A0(cost= =3D2.80..2.83 rows=3D1 width=3D1) (actual time=3D0.029..0.030 rows=3D1 loop= s=3D1)
=C2=A0 =C2=A0InitPlan 1 (returns $0)
=C2=A0 =C2=A0 =C2=A0->= =C2=A0Index Only Scan using idx_cachekeys on cachekeys =C2=A0(cost=3D0.55.= .2.80 rows=3D1 width=3D0) (actual time=3D0.028..0.028 rows=3D1 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((cachetype =3D '= ;BoMatrix'::text) AND (trsid =3D 'daznbetuk'::text) AND (brandi= d =3D 'daznbet'::text) AND (sportid =3D 'BOX'::text) AND (c= ompetitionid =3D 'U-1998'::text) AND (eventid =3D 'U-523596'= ;::text))
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Heap Fetches: 1
= =C2=A0Planning Time: 0.084 ms
=C2=A0Execution Time: 0.043 ms

This might be due to caching.=C2=A0 Ru= n the query with LIMIT three times, and then remove the LIMIT and run three= times.

Honestly, though, the execution timings se= em pretty good.=C2=A0 What exactly is the problem?

--
Death to <Redacted>, and butt= er sauce.
Don't boil me, I'm still alive.
<Reda= cted> lobster!

Hi Team and Andrian=C2=A0<= /div>

LIMIT is not necessary t= o use in select here in this case=C2=A0

To return one row takes 43ms is not optimal=C2=A0

Regards,
D= urga Mahesh=C2=A0
--000000000000dd7be106304550a2--