[YSQL] UPDATE table SET sec_index_key_col = <unmodified> causes unnecessary writes

[karthik-ramanathan-3006]

Jira Link: DB-8116

Description

Consider a table with the following schema:

CREATE TABLE bar (h INT PRIMARY KEY, v1 INT, v2 INT, v3 INT);
CREATE INDEX bar_v1 ON bar(v1);
CREATE INDEX bar_v2_v3 ON bar(v2, v3);

yugabyte=# \d bar
                Table "public.bar"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 h      | integer |           | not null |
 v1     | integer |           |          |
 v2     | integer |           |          |
 v3     | integer |           |          |
Indexes:
    "bar_pkey" PRIMARY KEY, lsm (h HASH)
    "bar_v1" lsm (v1 HASH)
    "bar_v2_v3" lsm (v2 HASH, v3 ASC)

bar has two secondary indexes: on v1, and on (v2, v3).

Consider the following UPDATE queries. They do not update the values in any of (v1, v2, v3).

yugabyte=# EXPLAIN (ANALYZE, DIST) UPDATE bar SET v1 = 1 WHERE h = 1;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Update on bar  (cost=0.00..4.11 rows=1 width=88) (actual time=10.988..10.988 rows=0 loops=1)
   ->  Index Scan using bar_pkey on bar  (cost=0.00..4.11 rows=1 width=88) (actual time=2.953..2.965 rows=1 loops=1)
         Index Cond: (h = 1)
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 1.856 ms
         Storage Table Write Requests: 1.000
         Storage Index Write Requests: 2.000
         Storage Flush Requests: 1
         Storage Flush Execution Time: 6.849 ms
 Planning Time: 0.191 ms
 Execution Time: 13.189 ms
 Storage Read Requests: 1
 Storage Read Execution Time: 1.856 ms
 Storage Write Requests: 3.000
 Catalog Read Requests: 0
 Catalog Write Requests: 0.000
 Storage Flush Requests: 2
 Storage Flush Execution Time: 8.606 ms
 Storage Execution Time: 10.462 ms
 Peak Memory Usage: 24 kB
(20 rows)

yugabyte=# EXPLAIN (ANALYZE, DIST) UPDATE bar SET v1 = v1 WHERE h = 1;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Update on bar  (cost=0.00..4.11 rows=1 width=88) (actual time=8.718..8.718 rows=0 loops=1)
   ->  Index Scan using bar_pkey on bar  (cost=0.00..4.11 rows=1 width=88) (actual time=2.646..2.655 rows=1 loops=1)
         Index Cond: (h = 1)
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 1.514 ms
         Storage Table Write Requests: 1.000
         Storage Index Write Requests: 4.000
         Storage Flush Requests: 1
         Storage Flush Execution Time: 4.620 ms
 Planning Time: 0.108 ms
 Execution Time: 10.740 ms
 Storage Read Requests: 1
 Storage Read Execution Time: 1.514 ms
 Storage Write Requests: 5.000
 Catalog Read Requests: 0
 Catalog Write Requests: 0.000
 Storage Flush Requests: 2
 Storage Flush Execution Time: 6.347 ms
 Storage Execution Time: 7.861 ms
 Peak Memory Usage: 24 kB
(20 rows)

yugabyte=# EXPLAIN (ANALYZE, DIST) UPDATE bar SET v1 = 1, v3 = 1 WHERE h = 1;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Update on bar  (cost=0.00..4.11 rows=1 width=88) (actual time=19.865..19.865 rows=0 loops=1)
   ->  Index Scan using bar_pkey on bar  (cost=0.00..4.11 rows=1 width=88) (actual time=4.398..4.408 rows=1 loops=1)
         Index Cond: (h = 1)
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 2.954 ms
         Storage Table Write Requests: 1.000
         Storage Index Write Requests: 4.000
         Storage Flush Requests: 1
         Storage Flush Execution Time: 14.075 ms
 Planning Time: 0.218 ms
 Execution Time: 22.352 ms
 Storage Read Requests: 1
 Storage Read Execution Time: 2.954 ms
 Storage Write Requests: 5.000
 Catalog Read Requests: 0
 Catalog Write Requests: 0.000
 Storage Flush Requests: 2
 Storage Flush Execution Time: 16.200 ms
 Storage Execution Time: 19.153 ms
 Peak Memory Usage: 24 kB
(20 rows)

In all of the above cases, both indexes are updated (DELETE + INSERT), although neither index needs to be updated.
The DELETE + INSERT combo needs two flushes, which further exacerbates the problem.

The issue seeks to resolve the redundant update to the secondary indexes.

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.

[karthik-ramanathan-3006]

Covered as part of #18822.