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 1tswAg-006wtB-Cy for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 03:57:18 +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 1tswAe-00EA0c-PT for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 03:57:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tswAe-00EA0U-Bp for pgsql-general@lists.postgresql.org; Fri, 14 Mar 2025 03:57:16 +0000 Received: from mail-oo1-xc2a.google.com ([2607:f8b0:4864:20::c2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tswAa-002lf0-2z for pgsql-general@postgresql.org; Fri, 14 Mar 2025 03:57:16 +0000 Received: by mail-oo1-xc2a.google.com with SMTP id 006d021491bc7-5fe9c1c14baso934663eaf.0 for ; Thu, 13 Mar 2025 20:57:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741924632; x=1742529432; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=BYrz/erh+jfGyEP4sQf8dYzo0rL5nWEvlBjYtpFxW0I=; b=iPQXOt0mLFE8gvlgRyuBW1kXbdBqtYHiAi4N4LZ/ljdleFruZoFTg0d6NDc2KhEOW3 67wVkhNRF+yCZf0kjzeF/3F2OQ5+qCc8GEYF85QCaKwuX0NYeOXjiMR2ZOdM2n2OORG0 SEAHL1/h73jpvtD0TFLge57kIP10dzQh7YRgIkvNib824T9EWkplhoK+P8tqxccrd5z2 vBNCU1AeVUSuGuQGoviFZZ5qIi1f5vrECK0giHluu6xTmgwCFw/PwpfnyxIBoJz5Yz2G sibVY0j+MHc/jxUxhlE3lbFV6dkI6RyZImSAaYABe1PhMUC0q1FlWrCFAQ7EBj6MT/b0 14Iw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741924632; x=1742529432; h=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=BYrz/erh+jfGyEP4sQf8dYzo0rL5nWEvlBjYtpFxW0I=; b=W3UgUVIMVavmDryeKMAMajiMHYOzpAKTfEPfxRAyhnlhxxldn/Q4HHGEfTjrvNOrZT XewDgY+gQTtT+nqJZnyk+lFHc32vp7Yzz80vUCv1f/xSziJxY1D5NajVH4awxFm4UtPg tRzAqCnQjfCkEkVvvfRSY1z3mmp1DYcacoiJb4LgYECwHm6zMCBPWzP3WPqUayokaCMv YtdtI++gsm0LEbewnq5f71+wciB9y8H+TH8dvtgAcYHIZ5ybFk1Bz1jqyO89kXKvsg6K ec2HI1vfYFT680KJcHat34xX+L2iuZCo//WNZm8826ZL1OpUJD+/3z59+bgQMtiE8bRz 2gBQ== X-Gm-Message-State: AOJu0Yw2myNRzSzfVTANcPNjUBRoY4I66XrhBZPzIkDMFZEN1zGV4X1A yDeZ4rs7yaxoRVVXxM1b2i8oUSe3g5bhoeeDXkNptuVtuRiSbT+oqCu2XJpxC07GC7NjjdcWzaT R9bbDr0cIRAEyg9Vs4ENcCfpqmj8lUVRD X-Gm-Gg: ASbGnct5xKy+B+tG6aWCDUL+ohUHnOaFrqbxkdWcdSZDEURO5LLzMbgZ8yNadKU+tye v39YoIAbfsNOOSHk8fn+Ldf1mhogZlYGq8+DO0ppJMQQ++5trhRfpSS2W3p60SJFYX8oQIqRHdy eaC23aGmIWcLQta87ez46SvBWE1RQkIOvVjrJa23tuBQxdxyHDhEAswYYQ2EkR X-Google-Smtp-Source: AGHT+IGUT5QJcOfTltvfiFytGT4gBnhfBwc1QHoBxKzUsENnLRJAYPf9beMroVj9QtA4ddb4w6omC4Ox+qPzFTwKzxE= X-Received: by 2002:a05:6808:1825:b0:3fa:8735:cfbe with SMTP id 5614622812f47-3fdee36f5b0mr399972b6e.1.1741924632026; Thu, 13 Mar 2025 20:57:12 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 13 Mar 2025 23:57:00 -0400 X-Gm-Features: AQ5f1JrfGH-OKpXHqgmj9dQp9C9IssC0rEU5Fgdl_9DpHBPbjQRgGxhFanGPxCs Message-ID: Subject: Re: Query optimization To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000eae65f0630456e45" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000eae65f0630456e45 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Mar 13, 2025 at 11:48=E2=80=AFPM Durgamahesh Manne < maheshpostgres9@gmail.com> wrote: > 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 r= ows=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 row= s=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 >> 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 =3D $1 AND TrsId =3D $2 AND BrandId =3D $3 A= ND SportId =3D $4 AND CompetitionId =3D $5 AND EventId =3D $6 AND MarketId =3D= $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 =3D $1 AND TrsId =3D $2 AND BrandId =3D $3 AND SportId =3D $4 AND CompetitionId =3D $5 AND EventId =3D = $6 AND MarketId =3D $7 --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000eae65f0630456e45 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Mar 13, 2025 at 11:48=E2=80=AFPM = Durgamahesh Manne <maheshpo= stgres9@gmail.com> wrote:
= 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 Manne <= ;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 <maheshpostgres9@gmail.com> wrote:
<= div>[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
----= ---------------------------------------------------------------------------= --------------------------------
=C2=A0PostgreSQL 14.12 on aarch64-unkno= wn-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-6), 64-bi= t

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 T= able "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| Compression
--= -------------+------------------------+-----------+----------+---------+---= -------+------------
=C2=A0cachetype =C2=A0 =C2=A0 | character varying(2= 55) | =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 | character varyin= g(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 | 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=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=A0= keytype =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=A0= key =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 | exte= nded | =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, compet= itionid, eventid, marketid)
=C2=A0 =C2=A0 "idx_marketid" btree= (marketid)

3) Output of EXPLAIN ANALYZ= E of query.

=C2=A0Result =C2=A0(cost=3D2.80..2.83 rows=3D1 width=3D1) (actual tim= e=3D0.030..0.030 rows=3D1 loops=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.02= 9..0.029 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0In= dex Cond: ((cachetype =3D 'BoMatrix'::text) AND (trsid =3D 'daz= nbetuk'::text) AND (brandid =3D 'daznbet'::text) AND (sportid = =3D 'BOX'::text) AND (competitionid =3D 'U-1998'::text) 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=A0Executi= on Time: 0.046 ms

That look= s pretty reasonable.
=C2=A0
1. Now show what happens wi= th the LIMIT clause.
2. How many rows does it return?
3= . Do you keep the table regularly vacuumed and analyzed?

Hey Ron

1. No= w show what happens with the LIMIT clause.
and result set of quer= y=C2=A0 and Size=C2=A0of the table 287MB
=C2=A0exist= s
--------
=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 loops=3D1)=C2=A0 =C2=A0InitPlan 1 (returns $0)
=C2=A0 =C2=A0 =C2=A0-> =C2=A0In= dex Only Scan using idx_cachekeys on cachekeys =C2=A0(cost=3D0.55..2.80 row= s=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 (brandid =3D = 9;daznbet'::text) AND (sportid =3D 'BOX'::text) AND (competitio= nid =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=A0Plann= ing Time: 0.084 ms
=C2=A0Execution Time: 0.043 ms

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

Honestly, though, the execution timings seem pretty g= ood.=C2=A0 What exactly is the problem?=C2=A0

Hi T= eam and Andrian=C2=A0

LI= MIT is not necessary to use in select here in this case=C2=A0

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

What did it used t= o take?

Planning takes 2x as long as execution.=C2= =A0 What if you just run "SELECT Key FROM CACHEKEYS WHERE CacheType = =3D $1 AND TrsId =3D $2 AND BrandId =3D $3 AND SportId =3D $4 AND Competiti= onId =3D $5 AND EventId =3D $6 AND MarketId =3D $7" and change app so = that "returns one or more rows means true"?

<= div>This is also a valid method:
SELECT COUNT(*) FROM CACHEKEYS W= HERE CacheType =3D $1 AND TrsId =3D $2 AND BrandId =3D $3 AND SportId =3D $= 4 AND CompetitionId =3D $5 AND EventId =3D $6 AND MarketId =3D $7

--
Death to <Redacte= d>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
--000000000000eae65f0630456e45--