public inbox for [email protected]
help / color / mirror / Atom feedPostgreSQL Choosing Full Index Over Partial Index
6+ messages / 3 participants
[nested] [flat]
* PostgreSQL Choosing Full Index Over Partial Index
@ 2025-04-28 13:22 Felipe López Montes <[email protected]>
2025-04-28 13:35 ` Re: PostgreSQL Choosing Full Index Over Partial Index Laurenz Albe <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Felipe López Montes @ 2025-04-28 13:22 UTC (permalink / raw)
To: [email protected]
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL Choosing Full Index Over Partial Index
2025-04-28 13:22 PostgreSQL Choosing Full Index Over Partial Index Felipe López Montes <[email protected]>
@ 2025-04-28 13:35 ` Laurenz Albe <[email protected]>
2025-04-28 14:54 ` Re: PostgreSQL Choosing Full Index Over Partial Index Felipe López Montes <[email protected]>
2025-04-28 15:07 ` Re: PostgreSQL Choosing Full Index Over Partial Index Tom Lane <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Laurenz Albe @ 2025-04-28 13:35 UTC (permalink / raw)
To: Felipe López Montes <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL Choosing Full Index Over Partial Index
2025-04-28 13:22 PostgreSQL Choosing Full Index Over Partial Index Felipe López Montes <[email protected]>
2025-04-28 13:35 ` Re: PostgreSQL Choosing Full Index Over Partial Index Laurenz Albe <[email protected]>
@ 2025-04-28 14:54 ` Felipe López Montes <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Felipe López Montes @ 2025-04-28 14:54 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; [email protected]
Hi Mr. Laurenz,
Thanks a lot for your response :).
The full index is bigger as it has an entry for all the rows of the table,
whilst the partial one only has entries for canceled flights.
Output of pgstatindex() for the *partial index:*
version,tree_level,index_size,root_block_no,internal_pages,leaf_pages,empty_pages,deleted_pages,avg_leaf_density,leaf_fragmentation
4,0,16384,1,0,1,0,0,13.4,0
Output of pgstatindex() for the
*full index:*
version,tree_level,index_size,root_block_no,internal_pages,leaf_pages,empty_pages,deleted_pages,avg_leaf_density,leaf_fragmentation
4,2,4825088,180,5,583,0,0,90.1,0
I already ran ANALYZE and also VACUUM ANALYZE, and it still goes for the
full one.
El lun, 28 abr 2025 a las 15:35, Laurenz Albe (<[email protected]>)
escribió:
> 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL Choosing Full Index Over Partial Index
2025-04-28 13:22 PostgreSQL Choosing Full Index Over Partial Index Felipe López Montes <[email protected]>
2025-04-28 13:35 ` Re: PostgreSQL Choosing Full Index Over Partial Index Laurenz Albe <[email protected]>
@ 2025-04-28 15:07 ` Tom Lane <[email protected]>
2025-04-30 09:11 ` Re: PostgreSQL Choosing Full Index Over Partial Index Felipe López Montes <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2025-04-28 15:07 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Felipe López Montes <[email protected]>; [email protected]
Laurenz Albe <[email protected]> writes:
> On Mon, 2025-04-28 at 15:22 +0200, Felipe López 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 scanning 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 involved is
* highly dependent on index type. We therefore ignore such costs here
* and leave it to the caller to add a suitable charge if needed.
*/
if (index->pages > 1 && index->tuples > 1)
numIndexPages = ceil(numIndexTuples * index->pages / index->tuples);
else
numIndexPages = 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 = 5
(gdb) p index->pages
$39 = 2
(gdb) p index->tuples
$40 = 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 = 5
(gdb) p index->pages
$45 = 17
(gdb) p index->tuples
$46 = 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 = 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL Choosing Full Index Over Partial Index
2025-04-28 13:22 PostgreSQL Choosing Full Index Over Partial Index Felipe López Montes <[email protected]>
2025-04-28 13:35 ` Re: PostgreSQL Choosing Full Index Over Partial Index Laurenz Albe <[email protected]>
2025-04-28 15:07 ` Re: PostgreSQL Choosing Full Index Over Partial Index Tom Lane <[email protected]>
@ 2025-04-30 09:11 ` Felipe López Montes <[email protected]>
2025-04-30 14:14 ` Re: PostgreSQL Choosing Full Index Over Partial Index Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Felipe López Montes @ 2025-04-30 09:11 UTC (permalink / raw)
To: Tom Lane <[email protected]>; [email protected]
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 (<[email protected]>) escribió:
> Laurenz Albe <[email protected]> writes:
> > On Mon, 2025-04-28 at 15:22 +0200, Felipe López 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 scanning
> 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 involved
> is
> * highly dependent on index type. We therefore ignore such costs here
> * and leave it to the caller to add a suitable charge if needed.
> */
> if (index->pages > 1 && index->tuples > 1)
> numIndexPages = ceil(numIndexTuples * index->pages /
> index->tuples);
> else
> numIndexPages = 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 = 5
> (gdb) p index->pages
> $39 = 2
> (gdb) p index->tuples
> $40 = 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 = 5
> (gdb) p index->pages
> $45 = 17
> (gdb) p index->tuples
> $46 = 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 = 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL Choosing Full Index Over Partial Index
2025-04-28 13:22 PostgreSQL Choosing Full Index Over Partial Index Felipe López Montes <[email protected]>
2025-04-28 13:35 ` Re: PostgreSQL Choosing Full Index Over Partial Index Laurenz Albe <[email protected]>
2025-04-28 15:07 ` Re: PostgreSQL Choosing Full Index Over Partial Index Tom Lane <[email protected]>
2025-04-30 09:11 ` Re: PostgreSQL Choosing Full Index Over Partial Index Felipe López Montes <[email protected]>
@ 2025-04-30 14:14 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2025-04-30 14:14 UTC (permalink / raw)
To: Felipe López Montes <[email protected]>; +Cc: [email protected]
=?UTF-8?Q?Felipe_L=C3=B3pez_Montes?= <[email protected]> writes:
> 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?
The guessing was in building a test case, since you didn't provide
a self-contained reproducer...
Once I had a case where the estimated cost was smaller for the larger
index, I just worked through the code to see where the costs diverged.
It helped that I already know the structure of that code well, but
there wasn't guesswork involved, other than where to set breakpoints
to narrow down the source more quickly.
I forgot to mention on this thread that I posted a possible fix at
[1].
regards, tom lane
[1] https://www.postgresql.org/message-id/870686.1745860834%40sss.pgh.pa.us
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-04-30 14:14 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-28 13:22 PostgreSQL Choosing Full Index Over Partial Index Felipe López Montes <[email protected]>
2025-04-28 13:35 ` Laurenz Albe <[email protected]>
2025-04-28 14:54 ` Felipe López Montes <[email protected]>
2025-04-28 15:07 ` Tom Lane <[email protected]>
2025-04-30 09:11 ` Felipe López Montes <[email protected]>
2025-04-30 14:14 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox