[YSQL] Colocation GA: Index creation takes 8+ sec when 1000+ colocated tables with default value columns are present in Database

[shishir2001-yb]

Jira Link: DB-5718

Description

Index creation for each colocated table is taking around 8-10 sec when there are 1000 colocated tables with default values columns in the database, whereas it takes around 4-5 sec with 100 colocated tables.

Steps to repro:

1. Create 1000 colocated tables
2. Create an Index on any one of the colocated tables

Table schema:

CREATE TABLE c_table_{table_number} (id text primary key,  uuid_col uuid DEFAULT uuid_generate_v4 (), name text, c complex, info json, contact JSONB, arr smallint[], cash money, i inet, m macaddr, i2 serial, i3 bigserial, val smallint, details e_details, age int, collated_data text ,date DATE,n NUMERIC (3, 2),r real, c1 CHAR(1), created_at timestamptz, uuid0 uuid DEFAULT uuid_nil(), uuid1 uuid DEFAULT uuid_generate_v1(), p1 POINT,t1 TIME,ts1 TIMESTAMP,i4 INTERVAL,p2 path, p3 polygon,b box, c2 circle, l line, l1 lseg, a2 text[][], zip postal_code);

Extension and Types

['CREATE EXTENSION IF NOT EXISTS "uuid-ossp"']
["CREATE TYPE complex AS (re float8, im float8)"]
["CREATE DOMAIN postal_code AS TEXT CHECK(VALUE ~ '^\\d{5}$'OR VALUE ~ '^\\d{5} -\\d{4}$')"]
["CREATE TYPE e_details AS ENUM ('Email', 'Sms', 'Phone')"]

Test 1: Created a colocated database(DB 1) Created 1000 colocated tables with no default value columns Created index on each table, 800 indexes took between 3.3sec to 3.5 sec each and 200 indexes took between 2.8 sec-3.3 sec each

Test 2: Created colocated database(DB 2) Created 1000 colocated tables with default value columns Created index on each table, 95% of indexes took around 6.5 sec each

[shishir2001-yb]

Update: Even after removing Default value columns, when 10k colocated tables are present, Index creation takes 8+ sec