Re: Query planning around one key of a multi-column index

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

On Wed, Dec 18, 2019 at 11:19 PM Wells Oliver <[email protected]>
wrote:

> But so are you guys!
>
> Thanks, this is helpful.
>
> Is it really always true that querying off any column that's not the first
> column of a multi-column index will NOT use that index?
>

Are you talking only about btree indexes?  Those are certainly the most
common, but other index types exist and have other properties.

Anyway, it is not always true for btree indexes.  The planner might decide
to use the index as a skinny version of the table, and scan the full index
to extract some non-leading column from it.  How likely it is to do this
will depend mainly on how much smaller the index is than the table, and
your relative settings of seq_page_cost and random_page_cost.  (Unlike some
other products, PostgreSQL will scan the index in logical order, not
physical order, even though it doesn't care about the order and this
generates a more random IO pattern, because this is the only practical way
to protect against concurrent page splits while it scans).  Unfortunately,
the EXPLAIN output does not make it clear when a index is being used in
this way, rather than the more traditional way.

Cheers,

Jeff

>

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Dec 18, 2019 at 11:19 PM Wells Ol=
iver &lt;<a href=3D"mailto:[email protected]">[email protected]</=
a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><div dir=3D"ltr"><div>But so are you guys!</div><d=
iv><br></div><div>Thanks, this is helpful.</div><div><br></div><div>Is it r=
eally always true that querying off any column that&#39;s not the first col=
umn of a multi-column index will NOT use that index?</div></div></blockquot=
e><div><br></div><div>Are you talking only about btree indexes?=C2=A0 Those=
 are certainly the most common, but other index types exist and have other =
properties.</div><div><br></div><div>Anyway, it is not always true for btre=
e indexes.=C2=A0 The planner might decide to use the index as a skinny vers=
ion of the table, and scan the full index to extract some non-leading colum=
n from it.=C2=A0=20

How likely it is to do this will depend mainly on how much smaller the inde=
x is than the table, and your relative settings of seq_page_cost and random=
_page_cost.=C2=A0 (Unlike some other products, PostgreSQL will scan the ind=
ex in logical order, not physical order, even though it doesn&#39;t care ab=
out the order and this generates a more random IO pattern, because this is =
the only practical way to protect against concurrent page splits while it s=
cans).=C2=A0 Unfortunately, the EXPLAIN output does not make it clear when =
a index is being used in this way, rather=C2=A0than the more traditional wa=
y.</div><div><br></div><div>Cheers,</div><div><br></div><div>Jeff</div><blo=
ckquote 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"ltr"><div dir=3D"=
ltr"></div></div>
</blockquote></div></div>

--000000000000b7e681059a0ee995--