Message-ID: <79f3cb0ac1e221012df38d7baa72463662c37095.camel@cybertec.at>
Subject: Re: PostgreSQL Choosing Full Index Over Partial Index
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Felipe =?ISO-8859-1?Q?L=F3pez?= Montes <xocas89@gmail.com>,
	pgsql-performance@lists.postgresql.org
Date: Mon, 28 Apr 2025 15:35:51 +0200
In-Reply-To: 
 <CACJPJu8oY9hb7LSsqHxbn24Gpa_tWBkcwPei=fottvgBeSc+PQ@mail.gmail.com>
References: 
	<CACJPJu8oY9hb7LSsqHxbn24Gpa_tWBkcwPei=fottvgBeSc+PQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/79f3cb0ac1e221012df38d7baa72463662c37095.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-04-28 at 15:22 +0200, Felipe L=C3=B3pez Montes wrote:
> I am using=C2=A0PostgreSQL=C2=A017.4 on x86_64-pc-linux-gnu, compiled by =
gcc (GCC) 12.4.0, 64-bit,
> and working with the postgres_air Database.
>=20
> I have a very simple query (please forget about the sense of the query it=
self,
> I just want to focus on the planner):
>=20
> SELECT status
> FROM postgres_air.flight
> WHERE status =3D 'Canceled';
>=20
> And the following indexes:
>=20
> CREATE INDEX flight_status_index ON flight(status)
>=20
> CREATE INDEX flight_canceled ON flight(status)
> WHERE status =3D 'Canceled'
>=20
>=20
> Following the book PostgreSQL Query Optimization (Second Edition), there =
is a
> statement on page 90 talking about Partial Indexes that says that the pla=
nner
> 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 scanning t=
he
> partial one and would like to understand why.
>=20
> I assume but might be wrong that having this partial index, lighter than =
the
> full table index, with both satisfying a specific index-suitable filter
> condition (in this case canceled flights represent 171 rows vs 683178 row=
s
> from the whole table), should be a reason for the planner to know that
> searching in the partial index should be faster than searching in the ful=
l
> index, besides the true fact that this partial index weights less than th=
e
> full one.
>=20
> I also tried downgrading the version to the one used by the authors of th=
e
> book but same behavior happens.
>=20
> Please see attached the different plan executions:
>=20
> Plan for the full index:
>
> QUERY PLAN
> Index Only Scan using flight_status_index on flight  (cost=3D0.42..7.61 r=
ows=3D182 width=3D11) (actual time=3D0.042..0.062 rows=3D171 loops=3D1)
> =C2=A0=C2=A0Index Cond: (status =3D 'Canceled'::text)
> =C2=A0=C2=A0Heap Fetches: 0
> Planning Time: 0.173 ms
> Execution Time: 0.080 ms
>
> Plan for the partial index:
>=20
> QUERY PLAN
> Index Only Scan using flight_canceled on flight  (cost=3D0.14..10.82 rows=
=3D182 width=3D11) (actual time=3D0.039..0.050 rows=3D171 loops=3D1)
> =C2=A0=C2=A0Heap Fetches: 0
> Planning Time: 0.135 ms
> Execution Time: 0.066 ms

Which index is bigger (you can use \di+ in "psql")?

Could you run the pgstatindex() function from the "pgstattuple" extension o=
n
both indexes and compare the output?

Does ANALYZE on the table make a difference?

Yours,
Laurenz Albe