[DocDB] High latencies on non-unique index after repeated inserts and deletes on the same value

[es1024]

Jira Link: DB-3401

Description

Given the following schema:

CREATE EXTENSION pgcrypto;
CREATE TABLE test(key UUID PRIMARY KEY, col UUID NOT NULL);
CREATE INDEX test_col_idx ON test(col);

If one inserts and deletes rows with the same value for col, e.g.

INSERT INTO test VALUES(gen_random_uuid(), '00000000-0000-0000-0000-000000000000');
DELETE FROM test;

repeatedly, then any read on the index with that value will experience high latency (proportional to the number of rows ever inserted):

yugabyte=# -- Before any inserts
yugabyte=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM test WHERE col = '00000000-0000-0000-0000-000000000000';
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Index Scan using test_col_idx on test  (cost=0.00..5.22 rows=10 width=32) (actual time=6.352..6.352 rows=0 loops=1)
   Index Cond: (col = '00000000-0000-0000-0000-000000000000'::uuid)
 Planning Time: 19.620 ms
 Execution Time: 6.433 ms
 Peak Memory Usage: 16 kB
(5 rows)

yugabyte=# -- After inserting and deleting 10000 rows total
yugabyte=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM test WHERE col = '00000000-0000-0000-0000-000000000000';
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_col_idx on test  (cost=0.00..5.22 rows=10 width=32) (actual time=591.929..591.929 rows=0 loops=1)
   Index Cond: (col = '00000000-0000-0000-0000-000000000000'::uuid)
 Planning Time: 18.317 ms
 Execution Time: 592.104 ms
 Peak Memory Usage: 16 kB
(5 rows)

yugabyte=# -- After inserting and deleting 100000 rows total
yugabyte=# EXPLAIN (ANALYZE, TIMING) SELECT * FROM test WHERE col = '00000000-0000-0000-0000-000000000000';
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_col_idx on test  (cost=0.00..5.22 rows=10 width=32) (actual time=5682.493..5682.493 rows=0 loops=1)
   Index Cond: (col = '00000000-0000-0000-0000-000000000000'::uuid)
 Planning Time: 5.107 ms
 Execution Time: 5682.581 ms
 Peak Memory Usage: 16 kB
(5 rows)

High latencies appear to persist even after triggering a compaction on both the table and index, but disappear if the index is recreated.

[bmatican]

@es1024 for the compaction note, are you waiting out the 15min history retention interval? we will not clean up tombstones during that window, as we might have active sessions reading at older times

High latencies appear to persist even after triggering a compaction on both the table and index

[es1024]

@bmatican Yes -- I tried doing INSERT + DELETE, then compacting both table and index several hours later, with the following result:

>  yb-admin compact_table ysql.yugabyte test_col_idx
Compacted [yugabyte.test_col_idx] tables.
>  yb-admin compact_table ysql.yugabyte test
Compacted [yugabyte.test] tables.
>  ysqlsh
ysqlsh (11.2-YB-2.15.3.0-b0)
Type "help" for help.

yugabyte=# \timing on
Timing is on.
yugabyte=# select *  from test;
 key | col
-----+-----
(0 rows)

Time: 109.477 ms
yugabyte=# select * from test where col = '00000000-0000-0000-0000-000000000000';
 key | col
-----+-----
(0 rows)

Time: 4889.215 ms (00:04.889)
yugabyte=#

So the high latencies on the index persist even after compaction on both table and index.

[kmuthukk]

@es1024 --

Not sure if compactions trigger a flush. But if not, can you try flushing the index and the table, and then triggering compactions? And the compaction must be done after retention interval has elapsed (as you did before).

CC: @bmatican , @rthallamko3

[es1024]

@kmuthukk I think compactions should trigger a flush - we send a FlushTables request with is_compaction=true to trigger a compaction. In any case, I tried flushing both index/table, then triggering a compaction on the same cluster as before and latencies remain unchanged.