반응형
PostgreSQL에서 인덱스가있는 열 나열
PostgreSQL에서 인덱스가있는 열을 가져오고 싶습니다.
MySQL에서는 열을 사용 SHOW INDEXES FOR table하고 볼 수 있습니다 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 | |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
PostgreSQL에 이와 같은 것이 있습니까?
나는 시도했습니다 \d상기 psql합니다 (로 프롬프트 명령 -ESQL을 표시하는 옵션)하지만 내가 찾는 정보를 표시하지 않습니다.
업데이트 : 답변을 추가 한 모든 사람에게 감사합니다. cope360 은 내가 찾던 것을 정확히 제공했지만 여러 사람들이 매우 유용한 링크로 차임했습니다. 미래 참고로, 설명서 확인 PG_INDEX (를 통해 Milen A. Radev )과 매우 유용한 기사 의 PostgreSQL에서 추출 메타 정보 (통해 마이클 니클라스을 ).
테스트 데이터 생성 ...
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));
색인화 된 색인 및 열 목록 :
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
열 이름을 롤업하십시오.
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
\d table_name에서이 정보를 보여 psql주지만 SQL을 사용하여 데이터베이스에서 이러한 정보를 얻으려면 PostgreSQL에서 META 정보 추출을 살펴보십시오 .
유틸리티 에서 이러한 정보를 사용 하여 테스트 및 프로덕션 환경에서 PostgreSQL 데이터베이스를 비교하기 위해 db 스키마의 일부 정보를보고 합니다.
PostgreSQL ( pg_indexes ) :
SELECT * FROM pg_indexes WHERE tablename = 'mytable';
MySQL ( SHOW INDEX ) :
SHOW INDEX FROM mytable;
그냥 해: \d table_name
그러나 열에 대한 정보가 없다는 것을 어떻게 알 수 있습니까?
예를 들면 다음과 같습니다.
# \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)
인덱스가 주어진 열이이 테이블에 명확하게 표시됩니다.
# \di
가장 쉽고 짧은 방법은 \di현재 데이터베이스의 모든 인덱스를 나열합니다.
$ \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의은 "작은 형제"입니다 \d현재의 모든 관계가 나열됩니다 명령 D의 atabase을. 따라서 \di확실히 "이 나를 보여 위해 서 d는 이 터 베 내가 ndexes".
입력 \diS하면 시스템 전체에서 사용 된 모든 인덱스가 나열되므로 모든 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--
이 두 명령을 모두 사용 +하여 인덱스에 필요한 디스크 공간 크기 및 사용 가능한 경우 설명과 같은 추가 정보를 얻을 수 있습니다.
$ \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)
psql에서는 명령어 입력에 대한 도움말을 쉽게 찾을 수 있습니다 \?.
다른 코드와 결합하여보기를 작성했습니다.
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;
일부 샘플 데이터 ...
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));
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)
이 명령은 테이블 변수, 인덱스 및 제약 조건을 보여줍니다.
=# \d table_name;
예:
testannie=# \d dv.l_customer_account;
\d tablename 버전 8.3.8의 열 이름을 보여줍니다.
"username_idx" UNIQUE, btree (username), tablespace "alldata1"
쿼리 결과 :
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)
질문:
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;
원시 정보는 pg_index에 있습니다.
인덱스에서 열 순서를 유지하려면 다음을 수행하는 (매우 추악한) 방법이 있습니다.
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
열 순서는 pg_index.indkey 열에 저장되므로 해당 배열의 아래 첨자로 정렬했습니다.
인덱스를 가지고 놀 때 인덱스에서 생성되는 열의 순서는 열 자체만큼 중요합니다.
다음 쿼리는 주어진 테이블에 대한 모든 인덱스와 모든 열을 정렬 된 방식으로 나열합니다.
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
필요한 색인으로 드릴 다운하려면 아래 쿼리를 시도하십시오
아래와 같이 쿼리하십시오-나는 이것을 개인적으로 시도하고 자주 사용합니다.
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;
허용 된 답변과 비슷하지만 pg_attribute 에서 일반 조인 으로 pg_attribute에 조인 을 남겨 두거나 pg_attribute를 사용한 쿼리 는 다음과 같은 인덱스를 제공하지 않습니다.
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;
다음은 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;
용법:
select * from getIndices('<my_table>')
간단한 해결책은 어떻습니까?
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%'
`
@ cope360의 훌륭한 답변은 조인 구문을 사용하도록 변환되었습니다.
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
;
나는이 버전 이이 스레드에 아직 있다고 생각하지 않습니다 : 인덱스의 ddl과 함께 열 이름 목록을 제공합니다.
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;
함수를 사용하는 인덱스는 열 이름에 연결되지 않으므로 때때로 3을 사용할 때 하나의 열 이름과 같은 인덱스 목록을 찾을 수 있습니다.
예:
CREATE INDEX ui1 ON table1 (coalesce(col1,''),coalesce(col2,''),col3)
쿼리는 인덱스에서 열로 'col3'만 반환하지만 DDL은 인덱스에 사용 된 전체 열 집합을 표시합니다.
@ Cope360의 좋은 답변으로 확장하십시오. 테이블 OID를 사용하여 특정 테이블을 얻으려면 (테이블 이름이 같지만 스키마가 다른 경우).
select
t.relname as table_name
,i.relname as index_name
,a.attname as column_name
,a.attrelid tableid
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 'tbassettype'
and a.attrelid = '"dbLegal".tbassettype'::regclass
order by
t.relname,
i.relname;
설명 : 스키마 'dbAsset'과 'dbLegal'모두에 테이블 이름 'tbassettype'이 있습니다. dbLegal에서 테이블 만 가져 오려면 a.attrelid = 해당 OID로 설정하십시오.
@ cope360의 약간 수정 된 답변 :
create table test (a int, b int, c int, constraint pk_test primary key(c, a, b));
select i.relname as index_name,
ix.indisunique as is_unique,
a.attname as column_name,
from pg_class c
inner join pg_index ix on c.oid=ix.indrelid
inner join pg_class i on ix.indexrelid=i.oid
inner join pg_attribute a on a.attrelid=c.oid and a.attnum=any(ix.indkey)
where c.oid='public.test'::regclass::oid
order by array_position(ix.indkey, a.attnum) asc;
인덱스 열이 올바른 순서로 표시됩니다.
index_name is_unique column_name
pk_test true c
pk_test true a
pk_test true b
select t.relname as table_name,
i.relname as index_name,
array_position(ix.indkey,a.attnum) pos,
a.attname as column_name
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 'orders'
order by t.relname, i.relname, array_position(ix.indkey,a.attnum)
@ cope360의 대답 은 훌륭하지만 Oracle의 DBA_IND_COLUMNS, ALL_IND_COLUMNS 및 USER_IND_COLUMNS와 같은 것을 원했습니다 (예 : 테이블 / 인덱스 스키마 및 다중 열 인덱스의 인덱스 위치보고). 이것에 대답하십시오 :
with
ind_cols as (
select
n.nspname as schema_name,
t.relname as table_name,
i.relname as index_name,
a.attname as column_name,
1 + array_position(ix.indkey, a.attnum) as column_position
from
pg_catalog.pg_class t
join pg_catalog.pg_attribute a on t.oid = a.attrelid
join pg_catalog.pg_index ix on t.oid = ix.indrelid
join pg_catalog.pg_class i on a.attnum = any(ix.indkey)
and i.oid = ix.indexrelid
join pg_catalog.pg_namespace n on n.oid = t.relnamespace
where t.relkind = 'r'
order by
t.relname,
i.relname,
array_position(ix.indkey, a.attnum)
)
select *
from ind_cols
where schema_name = 'test'
and table_name = 'indextest'
order by schema_name, table_name
;
이것은 다음과 같은 출력을 제공합니다.
schema_name | table_name | index_name | column_name | column_position
-------------+------------+------------+-------------+-----------------
test | indextest | testind1 | singleindex | 1
test | indextest | testind2 | firstoftwo | 1
test | indextest | testind2 | secondoftwo | 2
(3 rows)
참고 URL : https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql
반응형
'Programing' 카테고리의 다른 글
| 정기적으로 어떤 Xcode 키보드 단축키를 사용하십니까? (0) | 2020.04.29 |
|---|---|
| DROP 데이터베이스 권한없이 명령 행에서 모든 MySQL 테이블을 제거하는 방법은 무엇입니까? (0) | 2020.04.29 |
| Swift에서 pull을 사용하여 새로 고치는 방법은 무엇입니까? (0) | 2020.04.29 |
| -XAllowAmbiguousTypes는 언제 적절한가요? (0) | 2020.04.29 |
| 폭탄 적하 알고리즘 (0) | 2020.04.29 |