sql - usar - uso de indices en oracle

Listar columnas con índices en PostgreSQL

Question

Me gustaría obtener las columnas en las que se encuentra un índice en PostgreSQL.

En MySQL puede usar SHOW INDEXES FOR table y ver la columna Column_name .

mysql> show indexes from foos;
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foos | 0 | PRIMARY | 1 | id | A | 19710 | NULL | NULL | | BTREE | |
| foos | 0 | index_foos_on_email | 1 | email | A | 19710 | NULL | NULL | YES | BTREE | |
| foos | 1 | index_foos_on_name | 1 | name | A | 19710 | NULL | NULL | | BTREE | |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

¿Existe algo como esto para PostgreSQL?

Lo he intentado en el psql comando psql (con la opción -E para mostrar SQL) pero no muestra la información que estoy buscando.

Actualización: Gracias a todos los que agregaron sus respuestas. cope360 me dio exactamente lo que estaba buscando, pero varias personas intervinieron con enlaces muy útiles. Para referencia futura, consulte la documentación de pg_index (a través de Milen A. Radev ) y el muy útil artículo Extracción de información de META de PostgreSQL (a través de Michał Niklas ).

Answer 1

Similar a la respuesta aceptada, pero al haber dejado join en pg_attribute como join o query normal con pg_attribute, no da índices que son como:
create unique index unique_user_name_index on users (lower(name))

select
row_number() over (order by c.relname),
c.relname as index,
t.relname as table,
array_to_string(array_agg(a.attname), '', '') as column_names
from pg_class c
join pg_index i on c.oid = i.indexrelid and c.relkind=''i'' and c.relname not like ''pg_%''
join pg_class t on t.oid = i.indrelid
left join pg_attribute a on a.attrelid = t.oid and a.attnum = ANY(i.indkey)
group by t.relname, c.relname order by c.relname;

Answer 2

# /di

La manera más fácil y más corta es /di , que listará todos los índices en la base de datos actual.

$ /di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------------------+-------+----------+---------------
public | part_delivery_index | index | shipper | part_delivery
public | part_delivery_pkey | index | shipper | part_delivery
public | shipment_by_mandator | index | shipper | shipment_info
public | shipment_by_number_and_size | index | shipper | shipment_info
public | shipment_info_pkey | index | shipper | shipment_info
(5 rows)

/di es el "hermano pequeño" del comando /d que enumerará todas las relaciones de la base de datos actual. Por lo tanto, /di ciertamente significa "muéstrame esta d atabases i ndex".

Typing /diS mostrará una lista de todos los índices utilizados en todo el sistema, lo que significa que también obtendrá todos los índices pg_catalog.

$ /diS
List of relations
Schema | Name | Type | Owner | Table
------------+-------------------------------------------+-------+----------+-------------------------
pg_catalog | pg_aggregate_fnoid_index | index | postgres | pg_aggregate
pg_catalog | pg_am_name_index | index | postgres | pg_am
pg_catalog | pg_am_oid_index | index | postgres | pg_am
pg_catalog | pg_amop_fam_strat_index | index | postgres | pg_amop
pg_catalog | pg_amop_oid_index | index | postgres | pg_amop
pg_catalog | pg_amop_opr_fam_index | index | postgres | pg_amop
pg_catalog | pg_amproc_fam_proc_index | index | postgres | pg_amproc
pg_catalog | pg_amproc_oid_index | index | postgres | pg_amproc
pg_catalog | pg_attrdef_adrelid_adnum_index | index | postgres | pg_attrdef
--More--

Con estos dos comandos, puede agregar un + después para obtener incluso más información, como el tamaño del espacio en disco que necesita el índice y una descripción, si está disponible.

$ /di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-----------------------------+-------+----------+---------------+-------+-------------
public | part_delivery_index | index | shipper | part_delivery | 16 kB |
public | part_delivery_pkey | index | shipper | part_delivery | 16 kB |
public | shipment_by_mandator | index | shipper | shipment_info | 19 MB |
public | shipment_by_number_and_size | index | shipper | shipment_info | 19 MB |
public | shipment_info_pkey | index | shipper | shipment_info | 53 MB |
(5 rows)

En psql puede encontrar ayuda fácilmente sobre los comandos que escriben /? .

Answer 3

¿Qué tal una solución simple?

SELECT
t.relname table_name,
ix.relname index_name,
indisunique,
indisprimary,
regexp_replace(pg_get_indexdef(indexrelid), ''.*/((.*)/)'', ''/1'') columns
FROM pg_index i
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_class ix ON ix.oid = i.indexrelid
WHERE t.relname LIKE ''test%''

`

Answer 4

Algunos datos de muestra ...

create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c), constraint uk_test3ab unique (a, b));

Use la función pg_get_indexdef :

select pg_get_indexdef(indexrelid) from pg_index where indrelid = ''test''::regclass;
pg_get_indexdef
--------------------------------------------------------
CREATE UNIQUE INDEX pk_test ON test USING btree (a, b)
(1 row)
select pg_get_indexdef(indexrelid) from pg_index where indrelid = ''test2''::regclass;
pg_get_indexdef
----------------------------------------------------------
CREATE UNIQUE INDEX uk_test2 ON test2 USING btree (b, c)
(1 row)
select pg_get_indexdef(indexrelid) from pg_index where indrelid =''test3''::regclass;
pg_get_indexdef
------------------------------------------------------------
CREATE UNIQUE INDEX uk_test3b ON test3 USING btree (b)
CREATE UNIQUE INDEX uk_test3c ON test3 USING btree (c)
CREATE UNIQUE INDEX uk_test3ab ON test3 USING btree (a, b)
(3 rows)

Answer 5

Aquí hay una función que envuelve la respuesta de cope360:

CREATE OR REPLACE FUNCTION getIndices(_table_name varchar)
RETURNS TABLE(table_name varchar, index_name varchar, column_name varchar) AS $$
BEGIN
RETURN QUERY
select
t.relname::varchar as table_name,
i.relname::varchar as index_name,
a.attname::varchar as column_name
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = ''r''
and t.relname = _table_name
order by
t.relname,
i.relname;
END;
$$ LANGUAGE plpgsql;

Uso:

select * from getIndices(''<my_table>'')

Answer 6

Buena respuesta de @cope360, convertida para usar la sintaxis de unión.

select t.relname as table_name
, i.relname as index_name
, array_to_string(array_agg(a.attname), '', '') as column_names
from pg_class t
join pg_index ix
on t.oid = ix.indrelid
join pg_class i
on i.oid = ix.indexrelid
join pg_attribute a
on a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
where t.relkind = ''r''
and t.relname like ''test%''
group by t.relname
, i.relname
order by t.relname
, i.relname
;

Answer 7

Combinado con código de otros y creado una vista:

CREATE OR REPLACE VIEW view_index AS
SELECT
n.nspname as "schema"
,t.relname as "table"
,c.relname as "index"
,pg_get_indexdef(indexrelid) as "def"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class t ON i.indrelid = t.oid
WHERE c.relkind = ''i''
and n.nspname not in (''pg_catalog'', ''pg_toast'')
and pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
n.nspname
,t.relname
,c.relname;

Answer 8

Crea algunos datos de prueba ...

create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c),constraint uk_test3ab unique (a, b));

Listar índices y columnas indexadas:

select
t.relname as table_name,
i.relname as index_name,
a.attname as column_name
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = ''r''
and t.relname like ''test%''
order by
t.relname,
i.relname;
table_name | index_name | column_name
------------+------------+-------------
test | pk_test | a
test | pk_test | b
test2 | uk_test2 | b
test2 | uk_test2 | c
test3 | uk_test3ab | a
test3 | uk_test3ab | b
test3 | uk_test3b | b
test3 | uk_test3c | c

Enrolle los nombres de las columnas:

select
t.relname as table_name,
i.relname as index_name,
array_to_string(array_agg(a.attname), '', '') as column_names
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = ''r''
and t.relname like ''test%''
group by
t.relname,
i.relname
order by
t.relname,
i.relname;
table_name | index_name | column_names
------------+------------+--------------
test | pk_test | a, b
test2 | uk_test2 | b, c
test3 | uk_test3ab | a, b
test3 | uk_test3b | b
test3 | uk_test3c | c

Answer 9

Cuando se juega con índices, el orden en que se construyen las columnas en el índice es tan importante como las propias columnas.

La siguiente consulta enumera todos los índices de una tabla determinada y todas sus columnas de forma ordenada.

SELECT
table_name,
index_name,
string_agg(column_name, '','')
FROM (
SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name,
(SELECT i
FROM (SELECT
*,
row_number()
OVER () i
FROM unnest(indkey) WITH ORDINALITY AS a(v)) a
WHERE v = attnum)
FROM
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
WHERE
t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND a.attrelid = t.oid
AND a.attnum = ANY (ix.indkey)
AND t.relkind = ''r''
AND t.relname LIKE ''tablename''
ORDER BY table_name, index_name, i
) raw
GROUP BY table_name, index_name

Answer 10

Este comando muestra la vista de las tablas variables, índices y restricciones también

=# /d table_name;

Ejemplo:

testannie=# /d dv.l_customer_account;

Answer 11

La información sin procesar está en pg_index .

Answer 12

No creo que esta versión exista aún en este hilo: proporciona tanto la lista de nombres de columna junto con el ddl para el índice.

CREATE OR REPLACE VIEW V_TABLE_INDEXES AS
SELECT
n.nspname as "schema"
,t.relname as "table"
,c.relname as "index"
,i.indisunique AS "is_unique"
,array_to_string(array_agg(a.attname), '', '') as "columns"
,pg_get_indexdef(i.indexrelid) as "ddl"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class t ON i.indrelid = t.oid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
WHERE c.relkind = ''i''
and n.nspname not in (''pg_catalog'', ''pg_toast'')
and pg_catalog.pg_table_is_visible(c.oid)
GROUP BY
n.nspname
,t.relname
,c.relname
,i.indisunique
,i.indexrelid
ORDER BY
n.nspname
,t.relname
,c.relname;

Descubrí que los índices que usan funciones no se vinculan con los nombres de las columnas, por lo que ocasionalmente se encuentra una lista de índices, por ejemplo, un nombre de columna cuando en realidad se usa 3.

Ejemplo:

CREATE INDEX ui1 ON table1 (coalesce(col1,''''),coalesce(col2,''''),col3)

La consulta devuelve solo ''col3'' como una columna en el índice, pero el DDL muestra el conjunto completo de columnas utilizadas en el índice.

Answer 13

Por favor, intente la consulta a continuación para profundizar en los índices requeridos

Consulta de la siguiente manera: lo he intentado personalmente y lo uso con frecuencia.

SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN ''r'' THEN ''table'' WHEN ''v'' THEN ''view'' WHEN ''i''
THEN ''index'' WHEN ''S'' THEN ''sequence'' WHEN ''s'' THEN ''special'' END as "Type",
u.usename as "Owner",
c2.relname as "Table"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN (''i'','''')
AND n.nspname NOT IN (''pg_catalog'', ''pg_toast'')
AND pg_catalog.pg_table_is_visible(c.oid)
AND c2.relname like ''%agg_transaction%'' --table name
AND nspname = ''edjus'' -- schema name
ORDER BY 1,2;

Answer 14

RESULTADO DE LA CONSULTA:

table | column | type | notnull | index_name | is_index | primarykey | uniquekey | default
-------+----------------+------------------------+---------+--------------+----------+- -----------+-----------+---------
nodes | dns_datacenter | character varying(255) | f | | f | f | f |
nodes | dns_name | character varying(255) | f | dns_name_idx | t | f | f |
nodes | id | uuid | t | nodes_pkey | t | t | t |
(3 rows)

CONSULTA:

SELECT
c.relname AS table,
f.attname AS column,
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
f.attnotnull AS notnull,
i.relname as index_name,
CASE
WHEN i.oid<>0 THEN ''t''
ELSE ''f''
END AS is_index,
CASE
WHEN p.contype = ''p'' THEN ''t''
ELSE ''f''
END AS primarykey,
CASE
WHEN p.contype = ''u'' THEN ''t''
WHEN p.contype = ''p'' THEN ''t''
ELSE ''f''
END AS uniquekey,
CASE
WHEN f.atthasdef = ''t'' THEN d.adsrc
END AS default FROM pg_attribute f
JOIN pg_class c ON c.oid = f.attrelid
JOIN pg_type t ON t.oid = f.atttypid
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
LEFT JOIN pg_index AS ix ON f.attnum = ANY(ix.indkey) and c.oid = f.attrelid and c.oid = ix.indrelid
LEFT JOIN pg_class AS i ON ix.indexrelid = i.oid
WHERE c.relkind = ''r''::char
AND n.nspname = ''public'' -- Replace with Schema name
--AND c.relname = ''nodes'' -- Replace with table name, or Comment this for get all tables
AND f.attnum > 0
ORDER BY c.relname,f.attname;

Answer 15

Si desea conservar el orden de las columnas en el índice, esta es una forma (muy fea) de hacerlo:

select table_name,
index_name,
array_agg(column_name)
from (
select
t.relname as table_name,
i.relname as index_name,
a.attname as column_name,
unnest(ix.indkey) as unn,
a.attnum
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = ''r''
and t.relnamespace = <oid of the schema you''re interested in>
order by
t.relname,
i.relname,
generate_subscripts(ix.indkey,1)) sb
where unn = attnum
group by table_name, index_name

el orden de las columnas se almacena en la columna pg_index.indkey, por lo que solicité los subíndices de esa matriz.

Answer 16

Solo hazlo: /d table_name

Pero no estoy seguro de qué quiere decir que la información sobre las columnas no está allí.

Por ejemplo:

# /d pg_class
Table "pg_catalog.pg_class"
Column | Type | Modifiers
-----------------+-----------+-----------
relname | name | not null
relnamespace | oid | not null
reltype | oid | not null
reloftype | oid | not null
relowner | oid | not null
relam | oid | not null
relfilenode | oid | not null
reltablespace | oid | not null
relpages | integer | not null
reltuples | real | not null
reltoastrelid | oid | not null
reltoastidxid | oid | not null
relhasindex | boolean | not null
relisshared | boolean | not null
relistemp | boolean | not null
relkind | "char" | not null
relnatts | smallint | not null
relchecks | smallint | not null
relhasoids | boolean | not null
relhaspkey | boolean | not null
relhasexclusion | boolean | not null
relhasrules | boolean | not null
relhastriggers | boolean | not null
relhassubclass | boolean | not null
relfrozenxid | xid | not null
relacl | aclitem[] |
reloptions | text[] |
Indexes:
"pg_class_oid_index" UNIQUE, btree (oid)
"pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)

Muestra claramente qué columnas dado índice está en esta tabla.

Answer 17

/d table_name muestra esta información de psql , pero si desea obtener dicha información de la base de datos utilizando SQL, eche un vistazo a la extracción de información META de PostgreSQL .

Utilizo esa información en mi utilidad para informar algo de información del esquema db para comparar bases de datos PostgreSQL en entornos de prueba y producción.

Answer 18

/d tablename muestra los nombres de columna para mí en la versión 8.3.8.

 "username_idx" UNIQUE, btree (username), tablespace "alldata1"

Answer 19

PostgreSQL ( pg_indexes ):

SELECT * FROM pg_indexes WHERE tablename = ''mytable'';

MySQL ( MOSTRAR ÍNDICE ):

SHOW INDEX FROM mytable;