Hi all,

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
<https://github.com/hettie-d/postgres_air>.

I have a very simple query (please forget about the sense of the query
itself, I just want to focus on the planner):



*SELECT statusFROM postgres_air.flightWHERE 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




Thanks in advance.

Reply via email to