MIME-Version: 1.0
References: 
 <CACJPJu8oY9hb7LSsqHxbn24Gpa_tWBkcwPei=fottvgBeSc+PQ@mail.gmail.com>
 <79f3cb0ac1e221012df38d7baa72463662c37095.camel@cybertec.at>
 <783872.1745852832@sss.pgh.pa.us>
In-Reply-To: <783872.1745852832@sss.pgh.pa.us>
From: =?UTF-8?Q?Felipe_L=C3=B3pez_Montes?= <xocas89@gmail.com>
Date: Wed, 30 Apr 2025 11:11:14 +0200
Message-ID: 
 <CACJPJu8_7yFDmOLcTiVCBLRs31UB4smUtUVEeLq1GoBujqfPqQ@mail.gmail.com>
Subject: Re: PostgreSQL Choosing Full Index Over Partial Index
To: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000002c277b0633fb4d04"
Archived-At: 
 <https://www.postgresql.org/message-id/CACJPJu8_7yFDmOLcTiVCBLRs31UB4smUtUVEeLq1GoBujqfPqQ%40mail.gmail.com>
Precedence: bulk

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

Thanks a lot for your response Tom.

May I ask how do you debug those functions?
Or is it just that you read the code and more or less guess what should be
the value for each variable with information coming from querying Postgres
tables?

Thanks a lot.

El lun, 28 abr 2025 a las 17:07, Tom Lane (<tgl@sss.pgh.pa.us>) escribi=C3=
=B3:

> Laurenz Albe <laurenz.albe@cybertec.at> writes:
> > On Mon, 2025-04-28 at 15:22 +0200, Felipe L=C3=B3pez Montes wrote:
> >> Following the book PostgreSQL Query Optimization (Second Edition),
> there is a
> >> statement on page 90 talking about Partial Indexes that says that the
> planner
> >> will use the partial index rather than the full index on the flight
> table,
> >> however after doing my own tests I have checked that this is not true
> and the
> >> planner estimates that scanning the full index is cheaper than scannin=
g
> the
> >> partial one and would like to understand why.
>
> > Which index is bigger (you can use \di+ in "psql")?
>
> I find that I can reproduce something similar with a very tiny
> partial index: the cost estimate for the partial index comes out
> higher than for an equivalent query using a non-partial index.
> After tracing through it, the blame seems to affix to this
> formula in genericcostestimate:
>
>     /*
>      * Estimate the number of index pages that will be retrieved.
>      *
>      * We use the simplistic method of taking a pro-rata fraction of the
> total
>      * number of index pages.  In effect, this counts only leaf pages and
> not
>      * any overhead such as index metapage or upper tree levels.
>      *
>      * In practice access to upper index levels is often nearly free
> because
>      * those tend to stay in cache under load; moreover, the cost involve=
d
> is
>      * highly dependent on index type.  We therefore ignore such costs he=
re
>      * and leave it to the caller to add a suitable charge if needed.
>      */
>     if (index->pages > 1 && index->tuples > 1)
>         numIndexPages =3D ceil(numIndexTuples * index->pages /
> index->tuples);
>     else
>         numIndexPages =3D 1.0;
>
> (numIndexTuples is the estimated number of index entries to be
> visited.)  In the example I'm looking at, the query wants to
> retrieve 5 rows and the index holds exactly those 5 rows, so
>
> (gdb) p numIndexTuples
> $38 =3D 5
> (gdb) p index->pages
> $39 =3D 2
> (gdb) p index->tuples
> $40 =3D 5
>
> and numIndexPages comes out to 2, ie we expect to visit the
> whole index.  But if we're considering a non-partial index,
>
> (gdb) p numIndexTuples
> $44 =3D 5
> (gdb) p index->pages
> $45 =3D 17
> (gdb) p index->tuples
> $46 =3D 10000
>
> and numIndexPages comes out to 1, so we estimate half as much
> disk access cost and the partial index looks worse.
>
> I think what's wrong here is that index->pages is the entire
> size of the index including the meta page, but the calculation
> is being done (as the comment says) on the assumption that
> only leaf pages are involved.  If we were to exclude the meta
> page from the calculation then we'd conclude numIndexPages =3D 1
> for both indexes.  Felipe is considering a slightly larger index
> but I bet it's fundamentally the same issue.  With indexes having
> more than a few hundred entries, the delta due to the meta page
> would drop into the noise and we'd eventually prefer the partial
> index.  But I think the absolute index size only contributes to
> our estimate of descentCost (in btcostestimate) so you'd need
> fair-sized indexes before that reliably wins out.
>
> I don't consider this a serious defect: for this size of index
> it barely matters which one the planner picks, as evidenced by
> the fact that the true execution times are so close.  But it could
> be something to try to improve.  We could trivially discount the
> meta page for index types that have one.  Discounting intermediate
> upper pages would take more calculation (since we don't know a-priori
> how many there are) and might not be worth the trouble.
>
>                         regards, tom lane
>

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

<div dir=3D"ltr">Thanks a lot for your response Tom.<br><br>May I ask how d=
o you debug those functions?<br>Or is it just that you read the code and mo=
re or less guess what should be the value for each variable with informatio=
n coming from querying Postgres tables?<br><br>Thanks a lot.</div><br><div =
class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail=
_attr">El lun, 28 abr 2025 a las 17:07, Tom Lane (&lt;<a href=3D"mailto:tgl=
@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt;) escribi=C3=B3:<br></div><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px=
 solid rgb(204,204,204);padding-left:1ex">Laurenz Albe &lt;<a href=3D"mailt=
o:laurenz.albe@cybertec.at" target=3D"_blank">laurenz.albe@cybertec.at</a>&=
gt; writes:<br>
&gt; On Mon, 2025-04-28 at 15:22 +0200, Felipe L=C3=B3pez Montes wrote:<br>
&gt;&gt; Following the book PostgreSQL Query Optimization (Second Edition),=
 there is a<br>
&gt;&gt; statement on page 90 talking about Partial Indexes that says that =
the planner<br>
&gt;&gt; will use the partial index rather than the full index on the fligh=
t table,<br>
&gt;&gt; however after doing my own tests I have checked that this is not t=
rue and the<br>
&gt;&gt; planner estimates that scanning the full index is cheaper than sca=
nning the<br>
&gt;&gt; partial one and would like to understand why.<br>
<br>
&gt; Which index is bigger (you can use \di+ in &quot;psql&quot;)?<br>
<br>
I find that I can reproduce something similar with a very tiny<br>
partial index: the cost estimate for the partial index comes out<br>
higher than for an equivalent query using a non-partial index.<br>
After tracing through it, the blame seems to affix to this<br>
formula in genericcostestimate:<br>
<br>
=C2=A0 =C2=A0 /*<br>
=C2=A0 =C2=A0 =C2=A0* Estimate the number of index pages that will be retri=
eved.<br>
=C2=A0 =C2=A0 =C2=A0*<br>
=C2=A0 =C2=A0 =C2=A0* We use the simplistic method of taking a pro-rata fra=
ction of the total<br>
=C2=A0 =C2=A0 =C2=A0* number of index pages.=C2=A0 In effect, this counts o=
nly leaf pages and not<br>
=C2=A0 =C2=A0 =C2=A0* any overhead such as index metapage or upper tree lev=
els.<br>
=C2=A0 =C2=A0 =C2=A0*<br>
=C2=A0 =C2=A0 =C2=A0* In practice access to upper index levels is often nea=
rly free because<br>
=C2=A0 =C2=A0 =C2=A0* those tend to stay in cache under load; moreover, the=
 cost involved is<br>
=C2=A0 =C2=A0 =C2=A0* highly dependent on index type.=C2=A0 We therefore ig=
nore such costs here<br>
=C2=A0 =C2=A0 =C2=A0* and leave it to the caller to add a suitable charge i=
f needed.<br>
=C2=A0 =C2=A0 =C2=A0*/<br>
=C2=A0 =C2=A0 if (index-&gt;pages &gt; 1 &amp;&amp; index-&gt;tuples &gt; 1=
)<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 numIndexPages =3D ceil(numIndexTuples * index-&=
gt;pages / index-&gt;tuples);<br>
=C2=A0 =C2=A0 else<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 numIndexPages =3D 1.0;<br>
<br>
(numIndexTuples is the estimated number of index entries to be<br>
visited.)=C2=A0 In the example I&#39;m looking at, the query wants to<br>
retrieve 5 rows and the index holds exactly those 5 rows, so<br>
<br>
(gdb) p numIndexTuples<br>
$38 =3D 5<br>
(gdb) p index-&gt;pages<br>
$39 =3D 2<br>
(gdb) p index-&gt;tuples<br>
$40 =3D 5<br>
<br>
and numIndexPages comes out to 2, ie we expect to visit the<br>
whole index.=C2=A0 But if we&#39;re considering a non-partial index,<br>
<br>
(gdb) p numIndexTuples<br>
$44 =3D 5<br>
(gdb) p index-&gt;pages<br>
$45 =3D 17<br>
(gdb) p index-&gt;tuples<br>
$46 =3D 10000<br>
<br>
and numIndexPages comes out to 1, so we estimate half as much<br>
disk access cost and the partial index looks worse.<br>
<br>
I think what&#39;s wrong here is that index-&gt;pages is the entire<br>
size of the index including the meta page, but the calculation<br>
is being done (as the comment says) on the assumption that<br>
only leaf pages are involved.=C2=A0 If we were to exclude the meta<br>
page from the calculation then we&#39;d conclude numIndexPages =3D 1<br>
for both indexes.=C2=A0 Felipe is considering a slightly larger index<br>
but I bet it&#39;s fundamentally the same issue.=C2=A0 With indexes having<=
br>
more than a few hundred entries, the delta due to the meta page<br>
would drop into the noise and we&#39;d eventually prefer the partial<br>
index.=C2=A0 But I think the absolute index size only contributes to<br>
our estimate of descentCost (in btcostestimate) so you&#39;d need<br>
fair-sized indexes before that reliably wins out.<br>
<br>
I don&#39;t consider this a serious defect: for this size of index<br>
it barely matters which one the planner picks, as evidenced by<br>
the fact that the true execution times are so close.=C2=A0 But it could<br>
be something to try to improve.=C2=A0 We could trivially discount the<br>
meta page for index types that have one.=C2=A0 Discounting intermediate<br>
upper pages would take more calculation (since we don&#39;t know a-priori<b=
r>
how many there are) and might not be worth the trouble.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--0000000000002c277b0633fb4d04--