Re: Question about behavior of conditional indexes

On 21/09/21 22:28, Koen De Groote wrote:
> Greetings all,
>
> Working on postgres 11.
>
> I'm researching an index growing in size and never shrinking, and not
> being used anymore after a while.
>
> The index looks like this:
>
> |"index002" btree (action_performed, should_still_perform_action,
> action_performed_at DESC) WHERE should_still_perform_action = false
> AND action_performed = true |
> So, there are 2 boolean fields, and a conditional clause for both. The
> table has about 50M rows, the index barely ever goes over 100K matched
> rows.
>
> The idea is to search for rows matching these conditions quickly, and
> then update them. This happens daily.
>
> This means the condition no longer match the index. At this point,
> does the row get removed from the index? Or does it stay in there as a
> dead row?
>
> I'm noticing index bloat on this index and am wondering if all these
> updated rows just stay in the index?
>
> The index currently sits at 330MB. If I drop and re-create it, it's 1.5MB.
>
> A cronjob runs a vacuum once per day, I can see the amount of dead
> rows dropping in monitoring software.
>
> But should this also take care of indexes? In postgres 11, you can't
> reindex concurrently, so I was wondering if indexes are skipped by
> vacuum? Or only in case of conditional indexes?
>
>
>
> So I'm wondering if the behavior is as I described.
>
> Regards,
> Koen De Groote

Can you upgrade to pg13?  A lot of work was done on indexes in pg12 &
13.  So possibly your problem may have been resolved, at least in part.

Note that pg 14 is due out this month.

Cheers,
Gavin