2

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.

2
  • 2
    It would help to provide a bit more clarification. Are you explicitly looking for tables with one index consisting of a single column, ascending; and a second column consisting of just that same index, descending? Or, do you need to catch table with an index where the first (possibly only, possibly not) column is ascending, and another where that same column is the first column, descending? What if there are duplicate indexes (it happens): are you looking for at least one index with the same column, or exactly one? – RDFozz Apr 23 '18 at 16:14
  • 1
    What about multicolumn indexes? Is one matching index column enough or do you look for dupes on all index columns? In the same order? And please always disclose your version of Postgres. – Erwin Brandstetter Apr 23 '18 at 21:47
6

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:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.