Question about behavior of conditional indexes

From: Koen De Groote <kdg(dot)dev(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Question about behavior of conditional indexes
Date: 2021-09-21 10:28:48
Message-ID: CAGbX52Fitf7BUhfDGzzasxv31WM3EOmdfhaYdcUKkNajixau0A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gavin Flower 2021-09-21 11:14:32 Re: Question about behavior of conditional indexes
Previous Message Karsten Hilbert 2021-09-21 08:54:54 Aw: Re: Re: Timestamp with vs without time zone.