MIME-Version: 1.0
References: <CAJCZkoL+DHs0H9enQEg8JXZw=7032zrmFKG8Vx0fup6_wqNpnQ@mail.gmail.com>
 <b319261b-3e06-41f9-9772-1a1747ede2b1@aklaver.com> <CAJCZkoLSaApgwHuWepO4EUENtjKMy1oX+GpEsFW4D+gFKoN_GQ@mail.gmail.com>
 <CANzqJaAwMLzGLetJAFJzONszYGvr4p7Jt4XYpvVHsCXq3b8+Gg@mail.gmail.com>
 <CAJCZkoJuh00qMAJJfs=XcAXpN3SRRe9=Fhx6t-69aHbFsZN75Q@mail.gmail.com>
 <CANzqJaAiz8-bri-xNNFyBp4aBj0ni+PHhZZxSSbc1Y9YfVREyg@mail.gmail.com> <CAJCZkoJe9iAkPoNB-i+nXkPe0Er3EF+FN6uEVe_1Rubw2SGN+g@mail.gmail.com>
In-Reply-To: <CAJCZkoJe9iAkPoNB-i+nXkPe0Er3EF+FN6uEVe_1Rubw2SGN+g@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Fri, 14 Mar 2025 10:00:31 -0400
Message-ID: <CAKAnmmJM9xF2xpe48LkA6h0rLPJs9yPbuE7UWA9Gwz-Fzvo4Tw@mail.gmail.com>
Subject: Re: Query optimization
To: Durgamahesh Manne <maheshpostgres9@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b86e9106304ddead"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmJM9xF2xpe48LkA6h0rLPJs9yPbuE7UWA9Gwz-Fzvo4Tw%40mail.gmail.com>
Precedence: bulk

--000000000000b86e9106304ddead
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Mar 13, 2025 at 11:49=E2=80=AFPM Durgamahesh Manne <
maheshpostgres9@gmail.com> wrote:

> To return one row takes 43ms is not optimal
>

It's actually much faster than that, but even 43ms is overall good. The
query is already pretty optimal, as it uses a single index only scan. There
are a few tricks left to make this potentially faster, but you may want to
look into using some sort of in-memory caching system if your application
is that sensitive to timings. Or find us a version in which the execution
time is not 1/20th of a millisecond and we can work on that one.

Also take a look at pg_stat_statements so you can see how fast the query is
on average.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

--000000000000b86e9106304ddead
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Mar 13, 2025 at 11:49=E2=80=AFPM =
Durgamahesh Manne &lt;<a href=3D"mailto:maheshpostgres9@gmail.com">maheshpo=
stgres9@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote=
_container"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"a=
uto"><div dir=3D"auto">To return one row takes 43ms is not optimal=C2=A0</d=
iv></div></blockquote><div><br></div><div>It&#39;s actually much faster tha=
n that, but even 43ms is overall good. The query is already pretty optimal,=
 as it uses a single index only scan. There are a few tricks left to make t=
his potentially faster, but you may want to look into using some sort of in=
-memory caching system if your application is that sensitive to timings. Or=
 find us a version in which the execution time is not 1/20th of a milliseco=
nd and we can work on that one.</div><div><br></div><div>Also take a look a=
t pg_stat_statements so you can see how fast the query is on average.</div>=
<div><br></div><div>Cheers,</div></div><div dir=3D"ltr" class=3D"gmail_sign=
ature"><div dir=3D"ltr"><div>Greg</div><div><br></div><div>--</div><div>Cru=
nchy Data - <a href=3D"https://www.crunchydata.com" target=3D"_blank">https=
://www.crunchydata.com</a></div><div>Enterprise Postgres Software Products =
&amp; Tech Support</div><div><br></div></div></div></div>

--000000000000b86e9106304ddead--