Fix some wrong detect redundant indexes when the table have more than 10 columns

[v0112358]

I have 2 tables with schema:

                                     Table "public.demo_line_items_9_columns"
    Column     |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
---------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 box_line_id   | uuid                     |           | not null |         | plain    |              |
 qty_delivered | integer                  |           |          |         | plain    |              |
 qty_returned  | integer                  |           |          |         | plain    |              |
 created_by    | text                     |           |          |         | extended |              |
 created_at    | timestamp with time zone |           |          |         | plain    |              |
 updated_by    | text                     |           |          |         | extended |              |
 updated_at    | timestamp with time zone |           |          |         | plain    |              |
 runsheet_id   | uuid                     |           | not null |         | plain    |              |
 box_id        | uuid                     |           | not null |         | plain    |              |
Indexes:
    "demo_line_items_9_columns_tmp_pk" PRIMARY KEY, btree (box_id, runsheet_id, box_line_id)
    "demo_line_items_9_columns_box_line_id_idx" btree (box_line_id)
    "demo_line_items_9_columns_runsheet_id_idx" btree (runsheet_id)

                                     Table "public.demo_line_items_16_columns"
    Column     |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
---------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 box_line_id   | uuid                     |           | not null |         | plain    |              |
 product_id    | integer                  |           |          |         | plain    |              |
 product_name  | text                     |           |          |         | extended |              |
 product_sku   | text                     |           |          |         | extended |              |
 qty           | integer                  |           |          |         | plain    |              |
 cod           | numeric(12,2)            |           |          |         | main     |              |
 qty_delivered | integer                  |           |          |         | plain    |              |
 qty_returned  | integer                  |           |          |         | plain    |              |
 created_by    | text                     |           |          |         | extended |              |
 created_at    | timestamp with time zone |           |          |         | plain    |              |
 updated_by    | text                     |           |          |         | extended |              |
 updated_at    | timestamp with time zone |           |          |         | plain    |              |
 runsheet_id   | uuid                     |           | not null |         | plain    |              |
 box_id        | uuid                     |           | not null |         | plain    |              |
 qty_reversed  | integer                  |           |          |         | plain    |              |
 status        | boolean                  |           |          | false   | plain    |              |
Indexes:
    "runsheet_line_items_pk" PRIMARY KEY, btree (box_id, runsheet_id, box_line_id)
    "demo_line_items_box_line_id_idx" btree (box_line_id)
    "runsheet_line_items_runsheet_id_index" btree (runsheet_id)

• The query i2_redundant_indexes.sql will show the table demo_line_items_16_columns have redundant indexes.

  -[ RECORD 1 ]---+-------------------------------------------------------------------------------------------------------------------------------
  schema_name     | public
  table_name      | demo_line_items_16_columns
  index_name      | runsheet_line_items_pk
  access_method   | btree
  reason          | redundant to index: demo_line_items_box_line_id_idx
  main_index_def  | CREATE INDEX demo_line_items_box_line_id_idx ON public.demo_line_items_16_columns USING btree (box_line_id)
  main_index_size | 8192 bytes
  index_def       | CREATE UNIQUE INDEX runsheet_line_items_pk ON public.demo_line_items_16_columns USING btree (box_id, runsheet_id, box_line_id)
  index_size      | 8192 bytes
  index_usage     | 0
  -[ RECORD 2 ]---+-------------------------------------------------------------------------------------------------------------------------------
  schema_name     | public
  table_name      | demo_line_items_16_columns
  index_name      | runsheet_line_items_runsheet_id_index
  access_method   | btree
  reason          | redundant to index: demo_line_items_box_line_id_idx
  main_index_def  | CREATE INDEX demo_line_items_box_line_id_idx ON public.demo_line_items_16_columns USING btree (box_line_id)
  main_index_size | 8192 bytes
  index_def       | CREATE INDEX runsheet_line_items_runsheet_id_index ON public.demo_line_items_16_columns USING btree (runsheet_id)
  index_size      | 8192 bytes
  index_usage     | 0

[NikolayS]

@v0112358 thank you! Somehow I've stopped receiving email notifications from GitHub, so missed this – going to work on it and other contributions soon.

[NikolayS]

@v0112358 I've reworked i2 to use lpaded keys (example: "001 012" instead of "1 12"), please check it out and test if possible: https://github.com/NikolayS/postgres_dba/pull/55

Also fixed it in postgres-checkup: https://gitlab.com/postgres-ai/postgres-checkup/-/merge_requests/457