[SQL] 외래 키와 기본 키에 포스트 그레스 및 인덱스

외래 키와 기본 키에 포스트 그레스 및 인덱스

합니까 포스트 그레스가 자동으로 외래 키와 기본 키에 인덱스를 넣어? 어떻게 알 수 있습니까? 테이블의 모든 인덱스를 반환하는 명령이 있는가?

해결법

1. ==============================

   1.PostgreSQL은 자동이 아닌 외래 키 관계의 참조 측면에서, 기본 키 및 고유 제한 조건에 인덱스를 생성합니다.

   PostgreSQL은 자동이 아닌 외래 키 관계의 참조 측면에서, 기본 키 및 고유 제한 조건에 인덱스를 생성합니다.

   대학원은 암시 적 인덱스를 만들 때 그것이 일어날 때 당신이 볼 수 있도록, psql 프로그램 및 / 또는 시스템 로그에서 볼 수있는주의 사항 수준의 메시지를 방출합니다. 자동적으로 생성 된 인덱스도 테이블의 \ D 출력에 표시된다.

   고유 인덱스에 대한 설명서는 말한다 :

   과 제약에 대한 설명서는 말한다 :

   따라서 당신은 당신이 그들을 원하는 경우 외국 키 자신에 대한 인덱스를 생성해야합니다.

   당신은 M-에-N 테이블의 PK로 2 FK의 같은 차 외국 키를 사용하는 경우, 당신은 아마도 PK에 인덱스를 가지고 것을 참고 여분의 인덱스를 만들 필요가 없습니다.

   그것은 당신의 참조 측 외래 키 열 (또는 포함)에 인덱스를 생성하는 것이 좋은 생각하지만, 필요하지 않습니다. 각 인덱스는 당신이 모든 INSERT, UPDATE에 성능 비용을 지불하거나 삭제 있도록 감속이, 아래로 약간 작업을 DML 추가합니다. 인덱스가 거의 사용하지 않는 경우이 가진 가치가되지 않을 수 있습니다.

2. ==============================

   2.당신이 당신의 프로그램에서 스키마 (들)에있는 모든 테이블의 인덱스를 나열 할 경우, 모든 정보는 카탈로그의 손에 있습니다 :

   당신이 당신의 프로그램에서 스키마 (들)에있는 모든 테이블의 인덱스를 나열 할 경우, 모든 정보는 카탈로그의 손에 있습니다 :

   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]를 사용하여 카탈로그를 쿼리하는 방법을 알아내는 위해 유용합니다.

3. ==============================

   3.이 쿼리는 외래 키, 원본 소스에없는 인덱스를 나열합니다.

   이 쿼리는 외래 키, 원본 소스에없는 인덱스를 나열합니다.

   편집 : 그것은 작은 테이블 (미만 9메가바이트)과 일부 다른 경우를 확인하지 않습니다. 최종 WHERE 문을 참조하십시오.

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

4. ==============================

   4.예 - (워드 프로세서에서 이상) 외래 키의 경우 - 기본 키에 대해.

   예 - (워드 프로세서에서 이상) 외래 키의 경우 - 기본 키에 대해.

   \d <table_name>
   

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

5. ==============================

   5.나는이이 문서 냉각 성능 설명은 EclipseLink 2.5의 기능을하는 방법을 좋아한다

   나는이이 문서 냉각 성능 설명은 EclipseLink 2.5의 기능을하는 방법을 좋아한다

6. ==============================

   6.기본 키의 경우, 인덱스는 다음과 같은 메시지가 생성됩니다 :

   기본 키의 경우, 인덱스는 다음과 같은 메시지가 생성됩니다 :

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

   참조 된 테이블에 대한 인덱스가없는 경우 외부 키 제약 조건이 생성되지 않습니다.

   참조 테이블의 인덱스는 (비록 원하는)이 필요하지 않고, 따라서 내재적 생성되지 않을 것이다.

from https://stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys by cc-by-sa and MIT license