Início / dba / Perguntas / 204706
Accepted
adopilot
Asked: 2018-04-24 04:51:23 +0800 CST

Encontre tabelas com vários índices na mesma coluna

  • 772

Estou tendo um problema com o scaffolding PostgreSQL DB porque o npgsql não lida bem com tabelas com dois índices na mesma coluna. Aqui está um link para o meu projeto no github .

Estou tentando encontrar esses índices/tabelas para poder excluí-los do meu comando de scaffolding.

Exemplo:

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);

Meu problema é quando uma tabela tem 2 índices diferentes com a mesma coluna (um ASCe um DESC); Estou tentando consultar tabelas/índices como este no db que estou tentando scaffold.

Meu objetivo é encontrar tabelas que contenham mais de um índice na mesma coluna.

postgresql index

1 respostas

  1. Best Answer

    Esta consulta recupera uma lista completa de índices construídos na(s) mesma(s) coluna(s) da tabela na mesma ordem:

    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é tipo de dado int2vectore contém, citando o manual :

    uma matriz de indnattsvalores que indicam quais colunas da tabela esse índice indexa. Por exemplo, um valor de 1 3significaria que a primeira e a terceira colunas da tabela compõem a chave de índice.

    Portanto, a correspondência nesta coluna afirma as mesmas colunas na mesma ordem.
    Ordem de classificação, classe de operador, agrupamento ou qualquer outro decorador pode diferir.

    Restrições:

    • Os índices de várias colunas só se qualificam se todas as colunas de índice corresponderem na mesma ordem.
    • Índices que incluem quaisquer expressões que não sejam colunas de tabela simples são excluídos.

    A parte: o índice ialtreproc3no seu exemplo é frete morto, você deve removê-lo. O Postgres pode escanear índices em ambas as direções com praticamente a mesma velocidade. Exceção: índices de várias colunas com ordem de classificação mista. Ver:

    Embora a única diferença seja a ordem de classificação, não faz sentido manter vários índices btree na mesma coluna.

    • 6

relate perguntas