Unused expression indexes

From: Maciek Sakrejda <maciek(at)pganalyze(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Unused expression indexes
Date: 2024-08-06 20:06:08
Message-ID: CADXhmgS_84iLCV9ZVgYksgdZtE7MOdZhg-7zFQGdb023wXTRZQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

In a blog post [1], Bruce Momjian notes that expression indexes can
help with planning even if they're not used directly. But the examples
cited in that post are vague (i.e., they improve stats, but it's not
clear how they could change plans), and Bruce's answer to a comment
[2] suggests that this is not documented.

Is there any more info on this mechanism? Specifically, if one has
unused expression indexes (according to pg_stat_user_indexes), is it
safe to drop them? Or could they be providing statistics that
materially affect query planning even though the indexes themselves
are unused?

It looks like a very similar question was asked earlier this year on
pgsql-general [3], but got only a vague answer. Any background or tips
for where to look in the source regarding this behavior would be
greatly appreciated.

Thanks,
Maciek

[1]: https://momjian.us/main/blogs/pgblog/2017.html#February_20_2017
[2]: https://momjian.us/main/comment_item.html?/main/blogs/pgblog.html/February_20_2017#comment-3174376969
[3]: https://www.postgresql.org/message-id/flat/CAHg%3DPn%3DOZu7A3p%2B0Z-CDG4s2CHYe3UFQCTZp4RWGCEn2gmD35A%40mail.gmail.com

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2024-08-06 20:12:12 Re: Minor refactorings to eliminate some static buffers
Previous Message Noah Misch 2024-08-06 19:32:22 Re: Inval reliability, especially for inplace updates