On Wed, Aug 30, 2023 at 8:43 PM jayaprabhakar k <jayaprabhakar@xxxxxxxxx> wrote:
On Tue, Aug 29, 2023, 12:43 PM Jeff Janes <jeff.janes@xxxxxxxxx> wrote:On Mon, Aug 28, 2023 at 8:33 PM jayaprabhakar k <jayaprabhakar@xxxxxxxxx> wrote:Since we are only interested in the pending tasks, I created a partial index
`"tasks_pending_status_created_type_idx" btree (status, created, task_type) WHERE status <> ALL (ARRAY[3, 4, 5])`.This looks like a poorly designed index. Since the status condition exactly matches the index where clause, there is no residual point in having "status" be the first column in the index, it can only get in the way (for this particular query). Move it to the end, or remove it altogether.Interesting. I don't understand why it will get in the way. Unfortunately we have a few other cases where status is used in filter. That said, I will consider how to get this to work.Would removing status from the index column, improve HOT updates %? For example, changing status from 1->2, doesn't change anything on the index (assuming other criteria for HOT updates are met), but I am not sure how the implementation is.
No, changes to the status column will not qualify as HOT updates, even if status is only in the WHERE clause and not the index body. I don't know if there is a fundamental reason that those can't be done as HOT, or if it is just an optimization that no one implemented.
Within the tuples which pass the status check, which inequality is more selective, the "created" one or "updated" one?Obviously updated time is more selective (after status), and the created time is included only to exclude some bugs in our system that had left some old tasks stuck in progress (and for sorting). We do try to clean up occasionally, but not each time.
If "created" were the leading column in the index, then it could jump directly to the part of the index which meets the `created > ...` without having to scroll through all of them and throw them out one by one. But it sounds like there are so few of them that being able to skip them wouldn't be worth very much.
However we cannot add an index on `updated` column because that timestamp gets updated over 10x on average for each task. Since if a single index use a column, then the update will not be HOT, and every index needs to be updated. That will clearly add a bloat to every index. Did I miss something?
Why does it get updated so much? It seems like status should go from 1 to 2, then from 2 to 3,4,or 5, and then be done. So only 2 updates, not 10. Maybe the feature which needs this frequent update could be done in some other way which is less disruptive.
But anyway, PostgreSQL has features to prevent the index bloat from becoming too severe of a problem, and you should figure out why they are not working for you. The most common ones I know of are 1) long open snapshots preventing clean up, 2) all index scans being bitmap index scans, which don't to micro-vacuuming/index hinting the way ordinary btree index scans do, and 3) running the queries on a hot-standby, where index hint bits must be ignored. If you could identify and solve this issue, then you wouldn't need to twist yourself into knots avoiding non-HOT updates.
Cheers,
Jeff
