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 1tsw9Q-006weJ-70 for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 03:56:00 +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 1tsw9N-00E4Q3-RP for pgsql-general@arkaria.postgresql.org; Fri, 14 Mar 2025 03:55:57 +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 1tsw9N-00E4Pu-Fk for pgsql-general@lists.postgresql.org; Fri, 14 Mar 2025 03:55:57 +0000 Received: from mail-oa1-x34.google.com ([2001:4860:4864:20::34]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tsw9K-002leX-0S for pgsql-general@postgresql.org; Fri, 14 Mar 2025 03:55:57 +0000 Received: by mail-oa1-x34.google.com with SMTP id 586e51a60fabf-2c11ddc865eso606908fac.3 for ; Thu, 13 Mar 2025 20:55:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741924553; x=1742529353; darn=postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=PgqkrLe4BeiOnLw3/k+ulQIFkXGpYJWkJnF+0yGcgd4=; b=is4EXBFUssiOEFr5aXShaMOLYxvWUNkN0zzeOf8cP30hhFPQZP8gJGV2yrSvpwq9cC GakmrCua1gVr9SGqa2fvseOJJEuyhADhRHfnCR2E65wWGysPJdPgZ5h4aoSFquaStm84 +SjGq6zwTrdqp09yZRNQVsnAJzLBkFU6sHDUD1GVuLQ9rsG3UeXfHRlG0pUqhy480Hs2 sXHnI5gqJ7ZLDK3UfsFxSU3TxGOK2wqy3prZDYAUFkD+2pcXg8qX13qsDPNm+I/dlJTA gk9O+mT9CchwTNuVwjRlq6IfbmZKEphWTiUN8bVCL7PsNsCYXgbh/4CsuARqIehg3yXf eJUw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741924553; x=1742529353; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=PgqkrLe4BeiOnLw3/k+ulQIFkXGpYJWkJnF+0yGcgd4=; b=UqrH7O6DBWJmzwP/ZOCG1AZwGz1HXgjhKpH87PlhBxAla7vLQ8d2+PjOp8JWkhzvnz toPApvvGLEIb9KHS7Q/X16VVZ3VqCX+LbWC1Z6CozsWz+QH8hLcaH8GGen2MZxlG/DMn xtO7TMDsHbOgwLSIMGYWoRM4f8qR2IaqWfJWYQPiEd6Jq5DSLnnob4GUU7ZOGMfavPxX dODeLiI1c5nY0cQPthfe7vw83lwtPmecz7f5HhrlROiD3R6HRG+wLUo+Scoi8Vk8alfn +qAlDzPt9lOpDnW8Bqa+fNCain1ef4ijNI9I04wmQqtkqTJBb7gbrIlxirwcY4vtAt94 FrnQ== X-Forwarded-Encrypted: i=1; AJvYcCU8MVoCDY6C6IUEsAquj/9lHI5DR454M1Cnk5grH0HQqOh83e5233Ra43aCGrZUgT6+PnTH8dx2hN/n8SMw@postgresql.org X-Gm-Message-State: AOJu0YwMskpqNlyFrIF3Fe8lvKQVcNr/U1Kl58fji1P3NtgdcJ4GY9+L ix1PFvSwNLFN0KT5sqVjKv+0cVdDHrel0O9810cmdclGlmm3VFgrADdMixc82h/Dba79dcYpE1U pvYJoFflP172ILd2dt67wQQP9gT8= X-Gm-Gg: ASbGncusWrx8DQQuMLkPRTAd/VJNZYv0pJXL2dF/BgU+esy/lhFuXDps39KzGfu0H4t y/wekl37TU64Hul6Mx2LgZaV+9ulbzB82TNcS5+CMu5mw2MgwQL5Z97JSPgURP4767malQ3fu6Y J6Hpcc3qIJ3FitpkI6S6IGxLjE X-Google-Smtp-Source: AGHT+IHlFp5OzwLd6oMSoug1/UVPK9n8qRvM6I8cefx4gwft9gTTShQ0wq3olOdgQoj3iSFWwq0jnQbKGHZ9bP20q8Y= X-Received: by 2002:a05:6871:4b06:b0:29f:b09d:d93a with SMTP id 586e51a60fabf-2c690f63f6bmr504846fac.16.1741924553472; Thu, 13 Mar 2025 20:55:53 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a05:6802:5405:b0:589:13f9:e937 with HTTP; Thu, 13 Mar 2025 20:55:52 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Thu, 13 Mar 2025 20:55:52 -0700 X-Gm-Features: AQ5f1Jp8v611yYhthVsB5BwKoU08Pt8AdamE96-DkPdYYQ-7PyDdlNViNBNvwRE Message-ID: Subject: Re: Query optimization To: Durgamahesh Manne Cc: Ron Johnson , pgsql-general Content-Type: multipart/alternative; boundary="0000000000003c40ec0630456ad2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003c40ec0630456ad2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, March 13, 2025, Durgamahesh Manne wrote: > > > ------------------------------------------------------------ >>>>> ------------------------------------------------------------ >>>>> ------------------------------------------------------------ >>>>> -------------------------------------- >>>>> >>>> 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 >>> >> >> >> To return one row takes 43ms is not optimal >> > You are off by a factor of 1000 in your claimed performance. It=E2=80=99s = 0.043ms David J. --0000000000003c40ec0630456ad2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, March 13, 2025, Durgamahesh Manne <maheshpostgres9@gmail.com> wrote:


--------------= -----------------------------------------------------------------= -----------------------------------------------------------------= -----------------------------------------------------------------= ---------
=C2=A0R= esult =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=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 ro= ws=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 (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=A0= Heap Fetches: 1
=C2=A0Planning Time: 0.084 ms
=C2=A0Execution Time: 0= .043 ms


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

You are off by a fa= ctor of 1000 in your claimed performance.=C2=A0 It=E2=80=99s 0.043ms
<= div>
David J.

--0000000000003c40ec0630456ad2--