Skip to content

Fix H004_redundant_indexes for tables with 10 or more columns

Nikolay Samokhvalov requested to merge fix-redundant-for-wide-tables into master

The method of redundant index detection is based on the analysis of pg_index.indkey::text.

For tables with 10+ columns, straightforward LIKE can be wrong – for example, "1 23" will match the line "1 2", which is incorrect.

The solution is to lpad all the numbers and work with 3-digit number sequences: "010 023" and "001 002" for the example above (this pair won't match, as expected)

Test

(example from https://github.com/NikolayS/postgres_dba/pull/50)

create table demo_line_items_16_columns(box_line_id uuid, product_id int, product_name text, product_sku text, qty int, cod numeric(12, 2), qty_delivered int, qty_returned int, created_by text, created_at timestamptz, updated_by text, updated_at timestamptz, runsheet_id uuid, box_id uuid, qty_reversed int, status boolean);
alter table demo_line_items_16_columns add primary key(box_id, runsheet_id, box_line_id);
create index demo_line_items_box_line_id_idx on demo_line_items_16_columns(box_line_id);
create index runsheet_line_items_runsheet_id_index on demo_line_items_16_columns(runsheet_id);

– for this table, we shouldn't see any redundant indexes.

Edited by Nikolay Samokhvalov

Merge request reports