MIME-Version: 1.0
References: <CAD=mzVWLN35FHPxN0Ajh2vnDkFLyg9f0vT6EKF9uPm7CYkHS5g@mail.gmail.com>
 <88e5c335-c70e-4973-9b71-6c12e251e5b7@ewie.name> <CAD=mzVWHC8O+fLksgc-uKAcq19xQSVvHz1AsButFmL+xh0_btg@mail.gmail.com>
 <CAKAnmmJW_pmtYLPyO2MONF+L_Wz_8kgPZE881cHPzd7MScrsXA@mail.gmail.com> <CAJCZko+-DCLp75-9JvkYxjrnvFqCA++VN1yLhFgwCYJWvqp8rA@mail.gmail.com>
In-Reply-To: <CAJCZko+-DCLp75-9JvkYxjrnvFqCA++VN1yLhFgwCYJWvqp8rA@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Fri, 11 Oct 2024 08:48:19 -0400
Message-ID: <CAKAnmmKORn7S+Nc6Pr1y57hV2G833F6QyM9vhdFeoAZmTmdnSw@mail.gmail.com>
Subject: Re: Question on indexes
To: Durgamahesh Manne <maheshpostgres9@gmail.com>
Cc: sud <suds1434@gmail.com>, Erik Wienhold <ewie@ewie.name>, xof@thebuild.com, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f8f997062432e874"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmKORn7S%2BNc6Pr1y57hV2G833F6QyM9vhdFeoAZmTmdnSw%40mail.gmail.com>
Precedence: bulk

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

(please start a new thread in the future rather than replying to an
existing one)

You cannot query on b and use an index on (a,b) as you observed. However,
you can have two indexes:

index1(a)
index2(b)

Postgres will be able to combine those when needed in the case where your
WHERE clause needs to filter by both columns. So then you no longer need
the two-column index.

Cheers,
Greg

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

<div dir=3D"ltr">(please start a new thread in the future rather than reply=
ing to an existing one)<div><br></div><div>You cannot query on b and use an=
 index on (a,b) as you observed. However, you can have two indexes:</div><d=
iv><br></div><div>index1(a)</div><div>index2(b)</div><div><br></div><div>Po=
stgres will be able to combine those when needed in the case where your WHE=
RE clause needs to filter by both columns. So then you no longer need the t=
wo-column index.</div><div><br></div><div>Cheers,</div><div>Greg</div><div>=
<br></div><div><br></div></div>

--000000000000f8f997062432e874--