Re: Unused expression indexes

On Tue, Aug 6, 2024 at 1:25 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The planner has no info about the values of sqrt(x1), so you get a
> default estimate (one-third) of the selectivity of the WHERE clause.
> But watch this:
>
> regression=# create index on foo (sqrt(x1));
> CREATE INDEX
> regression=# analyze foo;
> ANALYZE
> regression=# explain analyze select * from foo where sqrt(x1) < 1;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------
> Bitmap Heap Scan on foo (cost=24.24..84.63 rows=1026 width=8) (actual time=0.078..0.229 rows=1028 loops=1)
> Recheck Cond: (sqrt(x1) < '1'::double precision)
> Heap Blocks: exact=45
> -> Bitmap Index Scan on foo_sqrt_idx (cost=0.00..23.98 rows=1026 width=0) (actual time=0.068..0.068 rows=1028 loops=1)
> Index Cond: (sqrt(x1) < '1'::double precision)
> Planning Time: 0.113 ms
> Execution Time: 0.259 ms
> (7 rows)
>
> Now there are stats about the values of sqrt(x1), allowing a far more
> accurate selectivity estimate to be made. In this particular example
> there's no change of plan (it would have used the index anyway), but
> certainly a different rowcount estimate can make a big difference.

Thanks, but I was asking specifically about _unused_ indexes
(according to pg_stat_user_indexes). Bruce's blog post showed how they
can still influence rowcount estimates, but can they do that (1) even
if they don't end up being used by the query plan and (2) in a way
that leads to a different plan?

Basically, if I have some unused expression indexes, is it safe to
drop them, or could they be used for planning optimizations even if
they are not used directly.

Thanks,
Maciek