public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Felipe López Montes <[email protected]>
To: [email protected]
Subject: Re: PostgreSQL Choosing Full Index Over Partial Index
Date: Mon, 28 Apr 2025 15:35:51 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJPJu8oY9hb7LSsqHxbn24Gpa_tWBkcwPei=fottvgBeSc+PQ@mail.gmail.com>
References: <CACJPJu8oY9hb7LSsqHxbn24Gpa_tWBkcwPei=fottvgBeSc+PQ@mail.gmail.com>

On Mon, 2025-04-28 at 15:22 +0200, Felipe López Montes wrote:
> I am using PostgreSQL 17.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.4.0, 64-bit,
> and working with the postgres_air Database.
> 
> I have a very simple query (please forget about the sense of the query itself,
> I just want to focus on the planner):
> 
> SELECT status
> FROM postgres_air.flight
> WHERE status = 'Canceled';
> 
> And the following indexes:
> 
> CREATE INDEX flight_status_index ON flight(status)
> 
> CREATE INDEX flight_canceled ON flight(status)
> WHERE status = 'Canceled'
> 
> 
> 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 scanning the
> partial one and would like to understand why.
> 
> 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 rows
> 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 full
> index, besides the true fact that this partial index weights less than the
> full one.
> 
> I also tried downgrading the version to the one used by the authors of the
> book but same behavior happens.
> 
> Please see attached the different plan executions:
> 
> Plan for the full index:
>
> QUERY PLAN
> Index Only Scan using flight_status_index on flight  (cost=0.42..7.61 rows=182 width=11) (actual time=0.042..0.062 rows=171 loops=1)
>   Index Cond: (status = 'Canceled'::text)
>   Heap Fetches: 0
> Planning Time: 0.173 ms
> Execution Time: 0.080 ms
>
> Plan for the partial index:
> 
> QUERY PLAN
> Index Only Scan using flight_canceled on flight  (cost=0.14..10.82 rows=182 width=11) (actual time=0.039..0.050 rows=171 loops=1)
>   Heap 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 on
both indexes and compare the output?

Does ANALYZE on the table make a difference?

Yours,
Laurenz Albe

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: PostgreSQL Choosing Full Index Over Partial Index
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox