외부 키와 프라이머리 키의 포스트그레스 및 인덱스

외부 키와 프라이머리 키의 포스트그레스 및 인덱스

Postgres는 외부 키와 기본 키에 인덱스를 자동으로 저장합니까?내가 어떻게 알아?테이블의 모든 인덱스를 반환하는 명령어가 있습니까?

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

하면 Pg는 "Pg", "Pg"를 내보냅니다.NOTICE에서 볼 수psql및/또는 시스템 로그가 생성되면 언제 발생하는지 확인할 수 있습니다.는 에 됩니다.\d테이블 출력도 가능합니다.

고유 인덱스에 대한 설명서는 다음과 같습니다.

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

제약사항에 대한 문서에는 다음과 같이 기재되어 있습니다.

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

따라서 외부 키를 원하는 경우 사용자가 직접 인덱스를 작성해야 합니다.

M-to-N 테이블에서 2개의 FK와 같은 기본 외부 키를 PK로 사용하는 경우 PK에 인덱스가 있으므로 추가 인덱스를 생성할 필요가 없습니다.

일반적으로 참조측 외부 키 열에 색인을 작성하는 것이 좋지만, 반드시 필요한 것은 아닙니다.약간 에 DML 동작 .INSERT,UPDATE ★★★★★★★★★★★★★★★★★」DELETE지수를 거의 사용하지 않으면 가치가 없을 수 있다.

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

편집: 작은 테이블(9MB 미만) 및 기타 케이스는 체크하지 않습니다.최종 참조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;

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

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>

in "psql"은 모든 인덱스를 포함하는 테이블에 대한 설명을 보여줍니다.

기사에서 설명하는 EclipseLink 2.5의 멋진 성능 특징

외부 키 인덱싱

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

이 함수는 Laurenz Albe가 https://www.cybertec-postgresql.com/en/index-your-foreign-key/,에서 수행한 작업을 기반으로 누락된 인덱스를 가진 모든 외부 키를 나열합니다.테이블의 크기가 표시됩니다. 작은 테이블의 경우 검색 성능이 인덱스보다 더 우수할 수 있습니다.

--
-- function:    missing_fk_indexes
-- purpose:     List all foreing keys in the database without and index in the referencing table.
-- author:      Based on the work of Laurenz Albe
-- see:         https://www.cybertec-postgresql.com/en/index-your-foreign-key/
--
create or replace function missing_fk_indexes () 
returns table (
  referencing_table regclass,
  fk_columns        varchar,
  table_size        varchar,
  fk_constraint     name,
  referenced_table  regclass
)
language sql as $$
  select
    -- referencing table having ta foreign key declaration
    tc.conrelid::regclass as referencing_table,
    
    -- ordered list of foreign key columns
    string_agg(ta.attname, ', ' order by tx.n) as fk_columns,
    
    -- referencing table size
    pg_catalog.pg_size_pretty (
      pg_catalog.pg_relation_size(tc.conrelid)
    ) as table_size,
    
    -- name of the foreign key constraint
    tc.conname as fk_constraint,
    
    -- name of the target or destination table
    tc.confrelid::regclass as referenced_table
    
  from pg_catalog.pg_constraint tc
  
  -- enumerated key column numbers per foreign key
  cross join lateral unnest(tc.conkey) with ordinality as tx(attnum, n)
  
  -- name for each key column
  join pg_catalog.pg_attribute ta on ta.attnum = tx.attnum and ta.attrelid = tc.conrelid
  
  where not exists (
    -- is there ta matching index for the constraint?
    select 1 from pg_catalog.pg_index i
    where 
      i.indrelid = tc.conrelid and 
      -- the first index columns must be the same as the key columns, but order doesn't matter
      (i.indkey::smallint[])[0:cardinality(tc.conkey)-1] @> tc.conkey) and 
      tc.contype = 'f'
    group by 
      tc.conrelid, 
      tc.conname, 
      tc.confrelid
    order by 
      pg_catalog.pg_relation_size(tc.conrelid) desc
$$;

이렇게 테스트하면

select * from missing_fk_indexes();

이런 리스트가 표시됩니다.

   referencing_table    |    fk_columns    | table_size |                fk_constraint                 | referenced_table 
------------------------+------------------+------------+----------------------------------------------+------------------
 stk_warehouse          | supplier_id      | 8192 bytes | stk_warehouse_supplier_id_fkey               | stk_supplier
 stk_reference          | supplier_id      | 0 bytes    | stk_reference_supplier_id_fkey               | stk_supplier
 stk_part_reference     | reference_id     | 0 bytes    | stk_part_reference_reference_id_fkey         | stk_reference
 stk_warehouse_part     | part_id          | 0 bytes    | stk_warehouse_part_part_id_fkey              | stk_part
 stk_warehouse_part_log | dst_warehouse_id | 0 bytes    | stk_warehouse_part_log_dst_warehouse_id_fkey | stk_warehouse
 stk_warehouse_part_log | part_id          | 0 bytes    | stk_warehouse_part_log_part_id_fkey          | stk_part
 stk_warehouse_part_log | src_warehouse_id | 0 bytes    | stk_warehouse_part_log_src_warehouse_id_fkey | stk_warehouse
 stk_product_part       | part_id          | 0 bytes    | stk_product_part_part_id_fkey                | stk_part
 stk_purchase           | parent_id        | 0 bytes    | stk_purchase_parent_id_fkey                  | stk_purchase
 stk_purchase           | supplier_id      | 0 bytes    | stk_purchase_supplier_id_fkey                | stk_supplier
 stk_purchase_line      | reference_id     | 0 bytes    | stk_purchase_line_reference_id_fkey          | stk_reference
 stk_order              | freighter_id     | 0 bytes    | stk_order_freighter_id_fkey                  | stk_freighter
 stk_order_line         | product_id       | 0 bytes    | stk_order_line_product_id_fkey               | cnt_product
 stk_order_fulfillment  | freighter_id     | 0 bytes    | stk_order_fulfillment_freighter_id_fkey      | stk_freighter
 stk_part               | sibling_id       | 0 bytes    | stk_part_sibling_id_fkey                     | stk_part
 stk_order_part         | part_id          | 0 bytes    | stk_order_part_part_id_fkey                  | stk_part

모든 참조 열을 체계적으로 작성하고 인덱싱하기로 결정한 사용자에게는 다음과 같은 다른 버전이 더 효율적일 수 있습니다.

--
-- function:    missing_fk_indexes2
-- purpose:     List all foreing keys in the database without and index in the referencing table.
--              The listing contains create index sentences
-- author:      Based on the work of Laurenz Albe
-- see:         https://www.cybertec-postgresql.com/en/index-your-foreign-key/
--
create or replace function missing_fk_indexes2 () 
returns setof varchar
language sql as $$
  select
    -- create index sentence
    'create index on ' || 
    tc.conrelid::regclass || 
    '(' || 
    string_agg(ta.attname, ', ' order by tx.n) || 
    ')' as create_index
        
  from pg_catalog.pg_constraint tc
  
  -- enumerated key column numbers per foreign key
  cross join lateral unnest(tc.conkey) with ordinality as tx(attnum, n)
  
  -- name for each key column
  join pg_catalog.pg_attribute ta on ta.attnum = tx.attnum and ta.attrelid = tc.conrelid
  
  where not exists (
    -- is there ta matching index for the constraint?
    select 1 from pg_catalog.pg_index i
    where 
      i.indrelid = tc.conrelid and 
      -- the first index columns must be the same as the key columns, but order doesn't matter
      (i.indkey::smallint[])[0:cardinality(tc.conkey)-1] @> tc.conkey) and 
      tc.contype = 'f'
    group by 
      tc.conrelid, 
      tc.conname, 
      tc.confrelid
    order by 
      pg_catalog.pg_relation_size(tc.conrelid) desc
$$;

출력은 다음과 같습니다.create index데이터베이스에 추가해야 하는 문장입니다.

select * from missing_fk_indexes2();

                   missing_fk_indexes2                    
----------------------------------------------------------
 create index on stk_warehouse(supplier_id)
 create index on stk_reference(supplier_id)
 create index on stk_part_reference(reference_id)
 create index on stk_warehouse_part(part_id)
 create index on stk_warehouse_part_log(dst_warehouse_id)
 create index on stk_warehouse_part_log(part_id)
 create index on stk_warehouse_part_log(src_warehouse_id)
 create index on stk_product_part(part_id)
 create index on stk_purchase(parent_id)
 create index on stk_purchase(supplier_id)
 create index on stk_purchase_line(reference_id)
 create index on stk_order(freighter_id)
 create index on stk_order_line(product_id)
 create index on stk_order_fulfillment(freighter_id)
 create index on stk_part(sibling_id)
 create index on stk_order_part(part_id)

의 경우PRIMARY KEY다음 메시지와 함께 인덱스가 생성됩니다.

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

의 경우FOREIGN KEY참조된 테이블에 인덱스가 없는 경우 제약조건은 생성되지 않습니다.

참조 테이블의 인덱스는 필수가 아니기 때문에(필요하지만), 암묵적으로 작성되지 않습니다.

@sergeyB의 SQL을 사용하여 외부 키에 누락된 인덱스에 대한 인덱스를 작성하기 위한 SQL을 생성하는 bash 스크립트입니다.

#!/bin/bash

read -r -d '' SQL <<EOM

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_to_string(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 relname as table_name,
    cols_list
FROM fk_index_check
    JOIN parent_table_stats USING (fkoid)
    JOIN fk_table_stats USING (fkoid)
ORDER BY issue_sort, table_mb DESC, table_name;
EOM

DB_NAME="dbname"
DB_USER="dbuser"
DB_PASSWORD="dbpass"
DB_HOSTNAME="hostname"
DB_PORT=5432

export PGPASSWORD="$DB_PASSWORD"
psql -h $DB_HOSTNAME -p $DB_PORT -U $DB_USER -d $DB_NAME -t -A -F"," -c "$SQL" | while read -r line; do
  IFS=','
  parts=($line)
  unset IFS
  tableName=${parts[0]}
  colsList=${parts[1]}

  indexName="${tableName}_${colsList//:/_}_index"
  printf -- "\n--Index: %s\nDROP INDEX IF EXISTS %s;\n
CREATE INDEX %s\n\t\tON %s USING btree\n\t(%s);
  " "$indexName" "$indexName" "$indexName" "$tableName" "$colsList"
done

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