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 1tsuan-006gg0-Ol for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 02:16:09 +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 1tsuam-00BqqX-HG for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 02:16:08 +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 1tsuam-00BqqM-0I for pgsql-general@lists.postgresql.org; Fri, 14 Mar 2025 02:16:08 +0000 Received: from mail-ej1-x62f.google.com ([2a00:1450:4864:20::62f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tsuag-002iOH-0E for pgsql-general@lists.postgresql.org; Fri, 14 Mar 2025 02:16:06 +0000 Received: by mail-ej1-x62f.google.com with SMTP id a640c23a62f3a-ab78e6edb99so239007566b.2 for ; Thu, 13 Mar 2025 19:16:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741918561; x=1742523361; darn=lists.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=K3bgwKEUnIaoFQPbYmIwdLfV775QjLwhqSIF7+O7Yns=; b=O7lB92Zt7tI/vsyq+/iGeVvvdI9tRTTIN17y77m6HzeMV0IePqDQFiWFa1fF9uVjG9 ssMin3Pi4qhnqFhFY5hWIzXXMP7k+y/tBak/R/Snz1NHFjPXMU9lE98czqFTTSKYi95a ZZP/MOALhxFXaaugyhfOrVt3wNYkIIslTn+mjHt2p2HETXohGmSQqPloyPvby2XEPMi/ RjpEGhEe68KmYU5s5aeU+XWzUdtts1TIIz4tXJTtbP/APJT+6FMhGqcs3yKT7jHQXMAk XFCZKi4Ek1pNwbwyWygeeaqd+HFjXL+evdk5OArhd35fHTcOEAw46ttA5HQVwZN+bES/ DZSQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741918561; x=1742523361; 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=K3bgwKEUnIaoFQPbYmIwdLfV775QjLwhqSIF7+O7Yns=; b=huBjUz4canpVtolZNrWjJcBg0ug/S+Lwu2oYIPAMddsG0XoLIawuAMin5winySkDvO tQzGbM2CQ4B5ce7ZKhYpan5HW/Flp6mA0VRoG+5OsIwhZA8uN64Xi6lbNhTgO5kMtRaX 7TpdHv7ozec/KC8xJEZaFQgKvubRDyRTiOYrb3Qet0ERwFWNfcYbJJF7bDT5t/dAIV46 jE/AjNWqtUS10TNEy8D1i/2TyVx6jZqcxrQaVQMSmhK1HuAROou7yhawd8+vJXxI3747 MA0wxpB2XYwAENSPy5x7ME0D2QXvV85JpSooASlWmLkYy//WwIcIZdq+8wJrMcGNB6XR 8fYA== X-Forwarded-Encrypted: i=1; AJvYcCXPTunSkIDai6jimYcNv/wXSUxwL3Fe8ZWT/RIIFdTS9y3wH+TuHlkGlLN1uCd4+ySRpfF5Ak0mty3j65iP@lists.postgresql.org X-Gm-Message-State: AOJu0Ywlj7dRAbKfCBsVlbmtrw9uSt+aYO582bQPnzhf70GSAXaUh6Zy agM263Bz2gALVcQ8SWWFckFS7yrD0B13599kGhzFM3hEj1czaPbqRa2gsjqETejRuwxhA+BO000 SdQfWJlkEym5Vic9JK77ZTHbj1qA= X-Gm-Gg: ASbGncuQTtaJZEGt64nThjN4LrDxNWAefQSM50xYFQ0g5Ob5uwwyIXod+c0LOqFEP8H V5NMvyoPJqcuWTridCPeTHPIwwTRKN57oWLusL0THh5F+8GTMiZLAMJK5S2+tE8Gsm+M6vA+BRq K2rXwDt0V5f0D/saYdsP0zZHf/IVCpHwc39vrL X-Google-Smtp-Source: AGHT+IHR27SJVVR9bVIWitm7EY0+Z+jegKD+rCDFfgZMgNVmcldwCP1Rw9mvXwqK8qz45vZg62NDtBbiezH/ZC6wiQc= X-Received: by 2002:a17:907:7e8e:b0:ac2:b4c3:c5ec with SMTP id a640c23a62f3a-ac33052eda9mr73539666b.56.1741918560457; Thu, 13 Mar 2025 19:16:00 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Durgamahesh Manne Date: Fri, 14 Mar 2025 07:48:01 +0530 X-Gm-Features: AQ5f1JqCz5yj9TZCrH7Kx4I-Qoorfa2lmlQ7p_IR30lSOnUis5dNJEcbC-k3g20 Message-ID: Subject: Re: Query optimization To: Adrian Klaver , pgsql-general , laurenz.albe@cybertec.at Content-Type: multipart/alternative; boundary="0000000000000619d2063044050c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000619d2063044050c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Mar 14, 2025 at 12:47=E2=80=AFAM Adrian Klaver 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 > > optimally > > > > > > > > SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType =3D $1 AND Trs= Id > > =3D $2 AND BrandId =3D $3 AND SportId =3D $4 AND CompetitionId =3D $5 A= ND > > EventId =3D $6 AND MarketId =3D $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=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 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: 0 Planning Time: 0.221 ms Execution Time: 0.046 ms 4) In what client is this being run? betting application Regards, Durga Mahesh --0000000000000619d2063044050c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Fri, Mar 14,= 2025 at 12:47=E2=80=AFAM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 3/13/25 12:12, Durgamahesh Manne = wrote:
> Hi Team
>
> This query takes more=C2=A0time 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 run= ning
> optimally
>
>
>
> SELECT EXISTS (SELECT Key FROM CACHEKEYS WHERE CacheType =3D $1 AND Tr= sId
> =3D $2 AND BrandId =3D $3 AND SportId =3D $4 AND CompetitionId =3D $5 = AND
> EventId =3D $6 AND MarketId =3D $7 LIMIT ?)
>
> Regards
> Durga Mahesh

--
Adrian Klaver
adrian.klave= r@aklaver.com

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 v= ersion
-----------------------------------------------------------------= ----------------------------------------------
=C2=A0PostgreSQL 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.

= =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| = Compression
---------------+------------------------+-----------+-------= ---+---------+----------+------------
=C2=A0cachetype =C2=A0 =C2=A0 | ch= aracter 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 = | 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=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 | cha= racter 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) (actual time= =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.029= ..0.029 rows=3D1 loops=3D1)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Ind= ex Cond: ((cachetype =3D 'BoMatrix'::text) AND (trsid =3D 'dazn= betuk'::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

4) In what client is this being run?=C2=A0
betting application

Regards,
Dur= ga Mahesh
--0000000000000619d2063044050c--