MIME-Version: 1.0
References: <CAJCZkoL3E5M7taGpdtJRhwM0zCQ1-T2uzv4FKj_Arrz-fRusOQ@mail.gmail.com>
 <CAJCZkoKEA4ZcYq0ereB2vFUQ+RQ8o2CEAUu5a4p-_ijuUvFL0g@mail.gmail.com> <CAApHDvpy1Goh=tmZKDvJ+3Uqb69r8i3U09_m9+P4NBertCyVmw@mail.gmail.com>
In-Reply-To: <CAApHDvpy1Goh=tmZKDvJ+3Uqb69r8i3U09_m9+P4NBertCyVmw@mail.gmail.com>
From: Durgamahesh Manne <maheshpostgres9@gmail.com>
Date: Tue, 15 Oct 2024 15:44:26 +0530
Message-ID: <CAJCZkoJEWdn3B57AWcnNn_4xycO33iKiUZZRXcy8EqyRHLv8fw@mail.gmail.com>
Subject: Re: Inefficient use of index scan on 2nd column of composite index
 during concurrent activity
To: David Rowley <dgrowleyml@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-general@postgresql.org>, Greg Sabino Mullane <htamfids@gmail.com>
Content-Type: multipart/alternative; boundary="000000000000cd38610624813815"
Archived-At: <https://www.postgresql.org/message-id/CAJCZkoJEWdn3B57AWcnNn_4xycO33iKiUZZRXcy8EqyRHLv8fw%40mail.gmail.com>
Precedence: bulk

--000000000000cd38610624813815
Content-Type: text/plain; charset="UTF-8"

On Tue, 15 Oct, 2024, 15:15 David Rowley, <dgrowleyml@gmail.com> wrote:

> On Sat, 12 Oct 2024 at 02:28, Durgamahesh Manne
> <maheshpostgres9@gmail.com> wrote:
> > Second column of composite index not in use effectively with index scan
> when using second column at where clause
> >
> > I have composite index on (placedon,id) of test
> > When quering  select * from test where id = '4234';
> > Value of id changes and during concurrent activity and cpu utilization
> increased toomuch  that i have observed which means query plan changed why
> >
> > I could see index scan with explain for it
> >
> > Is there any way to keep index scan for it during even on concurrency
> rather than seperate index on second column of composite index ?
>
> It sounds like you might be asking about something we call "index skip
> scans". Currently, PostgreSQL does not support these, however there is
> work being done to add support and that might arrive in PG18.
>
> There is some information about a possible workaround in [1] which may
> be of use to you.
>
> David
>
> [1] https://wiki.postgresql.org/wiki/Loose_indexscan


Hi David

Thanks you for valuable info

Regards,
Durga Mahesh

>
>

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

<div dir=3D"auto"><div><br><br><div class=3D"gmail_quote"><div dir=3D"ltr" =
class=3D"gmail_attr">On Tue, 15 Oct, 2024, 15:15 David Rowley, &lt;<a href=
=3D"mailto:dgrowleyml@gmail.com">dgrowleyml@gmail.com</a>&gt; wrote:<br></d=
iv><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left=
:1px #ccc solid;padding-left:1ex">On Sat, 12 Oct 2024 at 02:28, Durgamahesh=
 Manne<br>
&lt;<a href=3D"mailto:maheshpostgres9@gmail.com" target=3D"_blank" rel=3D"n=
oreferrer">maheshpostgres9@gmail.com</a>&gt; wrote:<br>
&gt; Second column of composite index not in use effectively with index sca=
n=C2=A0 when using second column at where clause<br>
&gt;<br>
&gt; I have composite index on (placedon,id) of test<br>
&gt; When quering=C2=A0 select * from test where id =3D &#39;4234&#39;;<br>
&gt; Value of id changes and during concurrent activity and cpu utilization=
 increased toomuch=C2=A0 that i have observed which means query plan change=
d why<br>
&gt;<br>
&gt; I could see index scan with explain for it<br>
&gt;<br>
&gt; Is there any way to keep index scan for it during even on concurrency =
rather than seperate index on second column of composite index ?<br>
<br>
It sounds like you might be asking about something we call &quot;index skip=
<br>
scans&quot;. Currently, PostgreSQL does not support these, however there is=
<br>
work being done to add support and that might arrive in PG18.<br>
<br>
There is some information about a possible workaround in [1] which may<br>
be of use to you.<br>
<br>
David<br>
<br>
[1] <a href=3D"https://wiki.postgresql.org/wiki/Loose_indexscan" rel=3D"nor=
eferrer noreferrer" target=3D"_blank">https://wiki.postgresql.org/wiki/Loos=
e_indexscan</a></blockquote></div></div><div dir=3D"auto"><br></div><div di=
r=3D"auto">Hi David</div><div dir=3D"auto"><br></div><div dir=3D"auto">Than=
ks you for valuable info=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D=
"auto">Regards,</div><div dir=3D"auto">Durga Mahesh=C2=A0</div><div dir=3D"=
auto"><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D=
"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>
</blockquote></div></div></div>

--000000000000cd38610624813815--