Bug in the missing index report

[yhuelf]

A missing index is reported when there's already a multi-column index for the referencing table.

For example, with the following:

CREATE TABLE foo(a INT PRIMARY KEY);
CREATE TABLE bar(id INT, b INT REFERENCES foo(a));
CREATE INDEX ON bar(b, id);

pgCluu reports a missing index:

CREATE INDEX CONCURRENTLY idx_bar_b ON public.bar (b)

But if we create it, pgCluu then detects correctly that it is contained in the other one.

[Krysztophe]

The following version should detect overlapping existing indexes. See attached files for a test case.

It does not detect redundant indexes in the generated script, and cannot tell which is the best when indexes are redundant. That deserves a warning in the report.

@darold : if you're ok with this, I'll do the PR.

SELECT 
'CREATE INDEX CONCURRENTLY idx_' || relname || '_' || 
         array_to_string(column_name_list, '_') || ' ON ' || conrelid || 
         ' (' || array_to_string(column_name_list, ',') || ')'
         || CASE WHEN COUNT(DISTINCT redi.indexrelid) >0 THEN '  /* maybe redundant with: '|| string_agg (redi.indexrelid::regclass::text,', ') || ' */' ELSE '' END
         || ' ;' AS ddl 
FROM (SELECT DISTINCT conrelid, 
       array_agg(attname) AS column_name_list, 
       array_agg(attnum) AS column_list 
     FROM pg_attribute 
          JOIN (-- contraintes existantes
                SELECT conrelid::regclass, conname, 
                 unnest(conkey) AS column_index 
                FROM (SELECT DISTINCT conrelid, conname, conkey 
                      FROM pg_constraint 
                        JOIN pg_class ON pg_class.oid = pg_constraint.conrelid 
                        JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace 
                      WHERE nspname !~ '^pg_' AND nspname <> 'information_schema' AND pg_constraint.contype = 'f' 
                      ) fkey 
               ) fkey 
               ON fkey.conrelid = pg_attribute.attrelid 
                  AND fkey.column_index = pg_attribute.attnum 
     GROUP BY conrelid, conname 
     ) candidate_index 
JOIN pg_class ON pg_class.oid = candidate_index.conrelid 
LEFT JOIN pg_index i ON i.indrelid = conrelid 
                      AND i.indkey::text = array_to_string(column_list, ' ') 
LEFT JOIN pg_index redi ON redi.indrelid = conrelid 
                       AND column_list  <@ redi.indkey::smallint[] -- contains
                       AND redi.indkey::text != array_to_string(column_list, ' ') 
WHERE i.indrelid IS NULL
GROUP BY relname, conrelid, column_name_list
ORDER BY ddl ;

Result :

                                                                                                       ddl                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE INDEX CONCURRENTLY idx_commandes_client_id ON commandes (client_id)  /* maybe redundant with: commandes_client_id_produit_idx */ ;
 CREATE INDEX CONCURRENTLY idx_lignes_commandes_commande_id ON lignes_commandes (commande_id)  /* maybe redundant with: lignes_commandes_pkey */ ;
 CREATE INDEX CONCURRENTLY idx_livraisons_client_id ON livraisons (client_id) ;
 CREATE INDEX CONCURRENTLY idx_livraisons_commande_id ON livraisons (commande_id)  /* maybe redundant with: livraisons_num_ligne_commande_id_idx, livraisons_num_ligne_commande_id_idx1 */ ;
 CREATE INDEX CONCURRENTLY idx_livraisons_commande_id_num_ligne ON livraisons (commande_id,num_ligne)  /* maybe redundant with: livraisons_num_ligne_commande_id_idx, livraisons_num_ligne_commande_id_idx1 */ ;

pgcluu_151.sql.txt

[darold]

@Krysztophe yes please proceed to the PR, thanks.