Find tables with multiple indexes on same column

Question

I am having an issue with scaffolding PostgreSQL DB because npgsql does not handle tables with two index on the same column well. Here's a link to my project on github.

I am trying to find these indexes / tables so I can exclude them from my scaffolding command.

Example:

CREATE TABLE public.altreproc (
  apcod integer NOT NULL,
  aptip character(3),
  apcam character(3),
  CONSTRAINT altreproc_pkey PRIMARY KEY (apcod)
);

CREATE INDEX ialtreproc1
  ON public.altreproc
  USING btree
  (aptip COLLATE pg_catalog."default");

CREATE INDEX ialtreproc2
  ON public.altreproc
  USING btree
  (apcam COLLATE pg_catalog."default");

CREATE INDEX ialtreproc3
  ON public.altreproc
  USING btree
  (aptip COLLATE pg_catalog."default" DESC);

My problem is when a table has 2 different index with the same column (one ASC and one DESC); I am trying to query tables / indexes like this in db which I am trying to scaffold.

My goal is to find tables which contain more then one index on same column.

Answer 1

This query retrieves a complete list of indexes build on the same (list of) table column(s in the same order):

SELECT indrelid::regclass::text AS table
     , idx_columns
     , indexrelid::regclass::text AS index
FROM   pg_index i
     , LATERAL (
   SELECT string_agg(attname, ', ') AS idx_columns
   FROM   pg_attribute
   WHERE  attrelid = i.indrelid
   AND    attnum  = ANY(i.indkey)  -- 0 excluded by: indexprs IS NULL
   ) a
WHERE  EXISTS (
   SELECT FROM pg_index
   WHERE  indrelid = i.indrelid
   AND    indkey = i.indkey
   AND    indexrelid <> i.indexrelid  -- exclude self
   )
AND    indexprs IS NULL -- exclude expression indexes
ORDER  BY 1, 2, 3;

pg_index.indkey is data type int2vector and contains, quoting the manual:

an array of indnatts values that indicate which table columns this index indexes. For example a value of 1 3 would mean that the first and the third table columns make up the index key.

So the match on this column asserts the same columns in the same order.
Sort order, operator class, collation or any other decorators can differ.

Constraints:

• Multicolumn indexes only qualify if all index columns match in the same order.
• Indexes including any expressions other than plain table columns are excluded.

Aside: index ialtreproc3 in your example is dead freight, you should remove it. Postgres can scan indexes in both directions with practically the same speed. Exception: multicolumn indexes with mixed sort order. See:

• Optimizing queries on a range of timestamps (two columns)

While the only difference is sort order, it makes no sense to maintain multiple btree indexes on the same single column.