외래 키 및 기본 키에 대한 Postgres 및 색인

외래 키 및 기본 키에 대한 Postgres 및 색인

---

Postgres는 외래 키 및 기본 키에 색인을 자동으로 넣습니까? 어떻게 알 수 있습니까? 테이블의 모든 인덱스를 반환하는 명령이 있습니까?

---

PostgreSQL은 기본 키와 고유 제약 조건에 대한 인덱스를 자동으로 생성하지만 외래 키 관계의 참조 측에는 인덱스를 생성하지 않습니다.

Pg는 암시 적 인덱스를 만들면 시스템 로그 및 / 또는 시스템 로그 NOTICE에서 볼 수 있는 수준의 메시지를 표시 psql하므로 언제 발생하는지 확인할 수 있습니다. 자동 생성 된 인덱스도 \d테이블 출력에 표시됩니다 .

고유 색인에 대한 문서 는 다음과 같습니다.

PostgreSQL은 고유성을 유지하기 위해 각 고유 제약 조건 및 기본 키 제약 조건에 대한 인덱스를 자동으로 생성합니다. 따라서 기본 키 열에 대해 명시 적으로 색인을 작성할 필요가 없습니다.

제약 조건에 대한 문서 는 다음 과 같이 말합니다.

참조 된 테이블에서 행을 삭제하거나 참조 된 열을 업데이트하려면 이전 값과 일치하는 행에 대한 참조 테이블을 스캔해야하므로 참조 열을 색인화하는 것이 좋습니다. 이것이 항상 필요한 것은 아니며, 색인화 방법에 대한 많은 선택 사항이 있기 때문에 외래 키 제약 조건의 선언이 참조 열에 색인을 자동으로 작성하지는 않습니다.

따라서 원하는 경우 외래 키에 대한 인덱스를 직접 만들어야합니다.

M-to-N 테이블에서 2 개의 FK와 같은 1 차 외래 키를 PK로 사용하는 경우 PK에 인덱스가 있으며 추가 인덱스를 만들 필요가 없습니다.

일반적으로 참조 측 외래 키 열에 대한 색인을 작성하거나 포함하는 것이 좋습니다. 각 인덱스는 당신이 모든에 성능 비용을 지불하도록 감속이, 아래로 약간 작업을 DML 추가 INSERT, UPDATE또는 DELETE. 인덱스를 거의 사용하지 않으면 가치가 없을 수 있습니다.

---

프로그램에서 스키마에있는 모든 테이블의 인덱스를 나열하려면 모든 정보가 카탈로그에 있습니다.

select
     n.nspname  as "Schema"
    ,t.relname  as "Table"
    ,c.relname  as "Index"
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

더 자세히 알아 보려면 (예 : 열 및 순서) pg_catalog.pg_index를 참조하십시오. 사용이 psql -E [dbname]카탈로그를 쿼리하는 방법을 알아내는 위해 유용합니다.

---

예-기본 키, 아니요-외래 키 ( 문서에 더 있음 )

\d <table_name>

에서 "psql의" 쇼의 모든 인덱스를 포함하는 테이블의 설명.

---

이 쿼리는 원본 키인 외래 키 에서 누락 된 인덱스를 나열 합니다 .

-- check for FKs where there is no matching index
-- on the referencing side
-- or a bad index

WITH fk_actions ( code, action ) AS (
    VALUES ( 'a', 'error' ),
        ( 'r', 'restrict' ),
        ( 'c', 'cascade' ),
        ( 'n', 'set null' ),
        ( 'd', 'set default' )
),
fk_list AS (
    SELECT pg_constraint.oid as fkoid, conrelid, confrelid as parentid,
        conname, relname, nspname,
        fk_actions_update.action as update_action,
        fk_actions_delete.action as delete_action,
        conkey as key_cols
    FROM pg_constraint
        JOIN pg_class ON conrelid = pg_class.oid
        JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
        JOIN fk_actions AS fk_actions_update ON confupdtype = fk_actions_update.code
        JOIN fk_actions AS fk_actions_delete ON confdeltype = fk_actions_delete.code
    WHERE contype = 'f'
),
fk_attributes AS (
    SELECT fkoid, conrelid, attname, attnum
    FROM fk_list
        JOIN pg_attribute
            ON conrelid = attrelid
            AND attnum = ANY( key_cols )
    ORDER BY fkoid, attnum
),
fk_cols_list AS (
    SELECT fkoid, array_agg(attname) as cols_list
    FROM fk_attributes
    GROUP BY fkoid
),
index_list AS (
    SELECT indexrelid as indexid,
        pg_class.relname as indexname,
        indrelid,
        indkey,
        indpred is not null as has_predicate,
        pg_get_indexdef(indexrelid) as indexdef
    FROM pg_index
        JOIN pg_class ON indexrelid = pg_class.oid
    WHERE indisvalid
),
fk_index_match AS (
    SELECT fk_list.*,
        indexid,
        indexname,
        indkey::int[] as indexatts,
        has_predicate,
        indexdef,
        array_length(key_cols, 1) as fk_colcount,
        array_length(indkey,1) as index_colcount,
        round(pg_relation_size(conrelid)/(1024^2)::numeric) as table_mb,
        cols_list
    FROM fk_list
        JOIN fk_cols_list USING (fkoid)
        LEFT OUTER JOIN index_list
            ON conrelid = indrelid
            AND (indkey::int2[])[0:(array_length(key_cols,1) -1)] @> key_cols

),
fk_perfect_match AS (
    SELECT fkoid
    FROM fk_index_match
    WHERE (index_colcount - 1) <= fk_colcount
        AND NOT has_predicate
        AND indexdef LIKE '%USING btree%'
),
fk_index_check AS (
    SELECT 'no index' as issue, *, 1 as issue_sort
    FROM fk_index_match
    WHERE indexid IS NULL
    UNION ALL
    SELECT 'questionable index' as issue, *, 2
    FROM fk_index_match
    WHERE indexid IS NOT NULL
        AND fkoid NOT IN (
            SELECT fkoid
            FROM fk_perfect_match)
),
parent_table_stats AS (
    SELECT fkoid, tabstats.relname as parent_name,
        (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as parent_writes,
        round(pg_relation_size(parentid)/(1024^2)::numeric) as parent_mb
    FROM pg_stat_user_tables AS tabstats
        JOIN fk_list
            ON relid = parentid
),
fk_table_stats AS (
    SELECT fkoid,
        (n_tup_ins + n_tup_upd + n_tup_del + n_tup_hot_upd) as writes,
        seq_scan as table_scans
    FROM pg_stat_user_tables AS tabstats
        JOIN fk_list
            ON relid = conrelid
)
SELECT nspname as schema_name,
    relname as table_name,
    conname as fk_name,
    issue,
    table_mb,
    writes,
    table_scans,
    parent_name,
    parent_mb,
    parent_writes,
    cols_list,
    indexdef
FROM fk_index_check
    JOIN parent_table_stats USING (fkoid)
    JOIN fk_table_stats USING (fkoid)
WHERE table_mb > 9
    AND ( writes > 1000
        OR parent_writes > 1000
        OR parent_mb > 10 )
ORDER BY issue_sort, table_mb DESC, table_name, fk_name;

---

이것이 EclipseLink 2.5의 멋진 성능 기능 기사에서 설명하는 방법을 좋아합니다.

외래 키 인덱싱

첫 번째 기능은 외래 키의 자동 인덱싱입니다. 대부분의 사람들은 데이터베이스가 기본적으로 외래 키를 색인화한다고 가정합니다. 글쎄, 그들은하지 않습니다. 기본 키는 자동 인덱싱되지만 외래 키는 자동 인덱싱되지 않습니다. 이것은 외래 키를 기반으로 한 모든 쿼리가 전체 테이블 스캔을 수행한다는 것을 의미합니다. 이것은 많은 OneToOne 관계 뿐만 아니라 OneToMany , ManyToMany 또는 ElementCollection 관계이며, 조인 또는 개체 비교와 관련된 관계에 대한 대부분의 쿼리입니다 . 이것은 중요한 수행 문제 일 수 있으므로 항상 외래 키 필드를 색인화해야합니다.

---

의 경우 PRIMARY KEY다음 메시지와 함께 색인이 작성됩니다.

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "index" for table "table" 

A에 대한 FOREIGN KEYreferenc의에 인덱스가없는 경우, 제한 조건이 생성되지 않습니다 에드 테이블.

referenc에 인덱스 보내고 테이블 (원하는하지만)하지 않아도되기 때문에 내재적 생성되지 않을 것이다.

참고 URL : https://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys