[YSQL] Extra Secondary Index Writes on Trigger Insert + Foreign Key Check

[karthik-ramanathan-3006]

Jira Link: DB-7701

Description

Consider the following schema:

CREATE TABLE IF NOT EXISTS trigger_table (
    h integer PRIMARY KEY NOT NULL
);

CREATE FUNCTION trigger_insert() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
  BEGIN
      INSERT INTO trigger_table (h) (SELECT NEW.h);
      RETURN NEW;
  END;
  $$;

CREATE TABLE main_table (
    h integer PRIMARY KEY NOT NULL,
    v1 integer NOT NULL,
    v2 integer NOT NULL,
    v3 integer
);

CREATE INDEX main_table_idx1 ON main_table (v1);
CREATE UNIQUE INDEX main_table_idx2 ON main_table (v2);
ALTER TABLE main_table ADD CONSTRAINT "main_table_fk" FOREIGN KEY (h) REFERENCES trigger_table(h);

CREATE TRIGGER trigger1 BEFORE INSERT ON main_table FOR EACH ROW EXECUTE FUNCTION trigger_insert();

This consists of a main table whose primary key has a foreign key constraint on a secondary table.
Additionally, the main table has a trigger, which inserts a new key into the secondary table.

Inserting a new row into the main table:

INSERT INTO main_table VALUES(0, 0, 0, 0);

When an UPDATE is issued to the non-index column column (v3) of the main table, we see write requests corresponding to DELETEs + INSERTs for each of the secondary indexes.

yugabyte=# EXPLAIN (ANALYZE, DIST) UPDATE main_table SET v3 = 1 WHERE h = 0;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Update on main_table  (cost=0.00..4.11 rows=1 width=88) (actual time=5.575..5.576 rows=0 loops=1)
   ->  Index Scan using main_table_pkey on main_table  (cost=0.00..4.11 rows=1 width=88) (actual time=1.769..1.778 rows=1 loops=1)
         Index Cond: (h = 0)
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 0.888 ms
         Storage Table Write Requests: 1
         Storage Index Write Requests: 4
         Storage Flush Requests: 1
         Storage Flush Execution Time: 2.427 ms
 Planning Time: 0.192 ms
 Execution Time: 7.172 ms
 Storage Read Requests: 1
 Storage Read Execution Time: 0.888 ms
 Storage Write Requests: 5
 Catalog Read Requests: 1
 Catalog Read Execution Time: 3.390 ms
 Catalog Write Requests: 0
 Storage Flush Requests: 2
 Storage Flush Execution Time: 3.704 ms
 Storage Execution Time: 7.983 ms
 Peak Memory Usage: 24 kB
(21 rows)

This query should ideally have been executed as a single main table write request in a single flush.
Instead we see 4 secondary index writes + 1 main table write across 2 flushes.

Attached is a gist containing the schema:
https://gist.github.com/karthik-ramanathan-3006/8fa30b3d56829e62e228382ada3ae3b3

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]

Found a simpler reproduction for this issue. Consider a table test with the following schema:

-- Create test
CREATE TABLE test(k INT PRIMARY KEY, v1 INT, v2 INT);
CREATE INDEX test_v ON test(v1);

-- Create parent table + FK constraint
CREATE TABLE parent(h INT PRIMARY KEY);
INSERT INTO parent VALUES(1);
ALTER TABLE test ADD CONSTRAINT "test_fk" FOREIGN KEY (k) REFERENCES parent(h);

-- Create a simple trigger on test
CREATE FUNCTION trigger_print_notice() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
        RAISE NOTICE 'Trigger called with new values ("%", "%", "%")', NEW.k, NEW.v1, NEW.v2;
        RETURN NEW;
    END;
    $$;

CREATE TRIGGER trigger_print BEFORE INSERT ON test FOR EACH ROW EXECUTE FUNCTION trigger_print_notice();

The table test now looks as follows:

yugabyte=# \d test
                Table "public.test"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 k      | integer |           | not null |
 v1     | integer |           |          |
 v2     | integer |           |          |
Indexes:
    "test_pkey" PRIMARY KEY, lsm (k HASH)
    "test_v" lsm (v1 HASH)
Foreign-key constraints:
    "test_fk" FOREIGN KEY (k) REFERENCES parent(h)
Triggers:
    trigger_print BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE trigger_print_notice()

Let us insert a sample row into test to ensure that the BEFORE INSERT trigger fires.

yugabyte=# INSERT INTO test VALUES(1, 1, 1);
NOTICE:  Trigger called with new values ("1", "1", "1")
INSERT 0 1

Performing an update on non-index column v2 leads to unnecessary writes:

yugabyte=# EXPLAIN (ANALYZE, DIST) UPDATE test SET v2 = 3 WHERE k = 1;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Update on test  (cost=0.00..4.11 rows=1 width=80) (actual time=14.941..14.941 rows=0 loops=1)
   ->  Index Scan using test_pkey on test  (cost=0.00..4.11 rows=1 width=80) (actual time=7.278..7.288 rows=1 loops=1)
         Index Cond: (k = 1)
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 3.676 ms
         Storage Table Write Requests: 1
         Storage Index Write Requests: 2
         Storage Flush Requests: 1
         Storage Flush Execution Time: 5.465 ms
 Planning Time: 0.341 ms
 Execution Time: 18.260 ms
 Storage Read Requests: 1
 Storage Read Execution Time: 3.676 ms
 Storage Write Requests: 3
 Catalog Read Requests: 0
 Catalog Write Requests: 0
 Storage Flush Requests: 2
 Storage Flush Execution Time: 8.343 ms
 Storage Execution Time: 12.019 ms
 Peak Memory Usage: 24 kB
(20 rows)

A similar result on change the WHERE clause:

yugabyte=# EXPLAIN (ANALYZE, DIST) UPDATE test SET v2 = 3 WHERE v2 = 3;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Update on test  (cost=0.00..102.50 rows=1000 width=80) (actual time=14.522..14.522 rows=0 loops=1)
   ->  Seq Scan on test  (cost=0.00..102.50 rows=1000 width=80) (actual time=4.518..4.533 rows=1 loops=1)
         Remote Filter: (v2 = 3)
         Storage Table Read Requests: 1
         Storage Table Read Execution Time: 2.782 ms
         Storage Table Write Requests: 1
         Storage Index Write Requests: 2
         Storage Flush Requests: 1
         Storage Flush Execution Time: 8.581 ms
 Planning Time: 0.126 ms
 Execution Time: 17.401 ms
 Storage Read Requests: 1
 Storage Read Execution Time: 2.782 ms
 Storage Write Requests: 3
 Catalog Read Requests: 1
 Catalog Read Execution Time: 6.122 ms
 Catalog Write Requests: 0
 Storage Flush Requests: 2
 Storage Flush Execution Time: 11.013 ms
 Storage Execution Time: 19.917 ms
 Peak Memory Usage: 55 kB
(21 rows)

Summary: A table with a FK constrain and a trigger (which are in no way related to each other) causes writes to all secondary indexes in the table.