主页 / dba / 问题 / 204706
Accepted
adopilot
Asked: 2018-04-24 04:51:23 +0800 CST

在同一列上查找具有多个索引的表

  • 772

我在搭建 PostgreSQL DB 时遇到问题，因为 npgsql 不能很好地处理同一列上有两个索引的表。这是我在 github 上的项目的链接。

我正在尝试查找这些索引/表，以便我可以将它们从我的脚手架命令中排除。

例子：

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

我的问题是当一个表有 2 个不同的索引具有相同的列（一个ASC和一个DESC）时；我试图在我试图搭建的数据库中查询这样的表/索引。

我的目标是找到在同一列上包含多个索引的表。

postgresql index

1 个回答

  1. Best Answer

    此查询检索在相同（列表）表列（以相同顺序）上构建的索引的完整列表：

    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是数据类型int2vector并包含，引用手册：

    一个值数组indnatts，指示该索引索引哪些表列。例如，值1 3将意味着第一和第三表列组成索引键。

    因此，此列上的匹配以相同的顺序断言相同的列。
    排序顺序、运算符类、排序规则或任何其他装饰器可以不同。

    约束：

    • 仅当所有索引列以相同的顺序匹配时，多列索引才符合条件。
    • 包括除普通表列以外的任何表达式的索引都被排除在外。

    另外：您示例中的索引ialtreproc3是死运费，您应该将其删除。Postgres 可以以几乎相同的速度双向扫描索引。例外：具有混合排序顺序的多列索引。看：

    虽然唯一的区别是排序顺序，但在同一列上维护多个 btree 索引是没有意义的。

    • 6

相关问题