Slow UPDATEs in PostgreSQL
I found that some query was dealing with a relatively small fraction of rows in the table, but the query execution was based on some regular index. Postgres indexes come with a lot of various flavors: besides the type of the index you may choose, there are multicolumn, functional, partial, and – since Postgres 11 – covering indexes. For the SELECT queries, I was oxptimizing, using a partial index was a natural choice. It improved the SELECT indeed, as expected. But it also made UPDATEs to the same table significantly slower!
This was a big surprise. I know that this will be a surprise to many of my readers too. Let's buckle up and travel thru a series of short experiments to explore how this may happen: we'll check various index options looking at the performance of both SELECT and UPDATE queries, and in the end, we'll try to draw some useful conclusions, as well as discuss how we can build an optimization approach that would follow the "first, do no harm" principle.
Source: How partial, covering, and multicolumn indexes may slow down UPDATEs in PostgreSQL, an article by Nikolay Samokhvalov.