Postgres e índices en keys externas y keys principales

¿Postgres coloca automáticamente índices en Llaves extranjeras y Llaves primarias? ¿Cómo puedo decir? ¿Hay algún command que devuelva todos los índices en una tabla?

PostgreSQL crea automáticamente índices en keys primarias y restricciones únicas, pero no en el lado de reference de las relaciones de foreign keys.

Cuando Pg crea un índice implícito, emitirá un post de nivel de NOTICE que puede ver en psql y / o en los loggings del sistema, para que pueda ver cuándo sucede. Los índices creados automáticamente son visibles en \d salida para una tabla, también.

La documentation sobre índices únicos dice:

PostgreSQL crea automáticamente un índice para cada restricción única y restricción de key primaria para imponer la singularidad. Por lo tanto, no es necesario crear un índice explícitamente para columnas de key primaria.

y la documentation sobre restricciones dice:

Como un DELETE de una fila de la tabla referenceda o una ACTUALIZACIÓN de una columna referenceda requerirá un escaneo de la tabla de reference para las filas que coinciden con el valor anterior, a menudo es una buena idea indexar las columnas de reference. Como esto no siempre es necesario, y hay muchas opciones disponibles sobre cómo indexar, la statement de una restricción de key externa no crea automáticamente un índice en las columnas de reference.

Por lo tanto, usted tiene que crear índices en foreign keys usted mismo si lo desea.

Tenga en count que si utiliza keys extranjeras-primarias, como 2 FK como PK en una tabla M-to-N, tendrá un índice en PK y probablemente no necesite crear ningún índice adicional.

Si bien generalmente es una buena idea crear un índice en (o include) sus columnas de key externa del lado de reference, no es necesario. Cada índice que agrega ralentiza ligeramente las operaciones de DML, por lo que paga un costo de performance en cada INSERT , UPDATE o DELETE . Si el índice se usa raramente, puede que no valga la pena tenerlo.

Si desea enumerar los índices de todas las tablas en su esquema (s) de su progtwig, toda la información está disponible en el catálogo:

 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 

Si desea profundizar (como columnas y pedidos), debe consultar pg_catalog.pg_index. El uso de psql -E [dbname] es útil para descubrir cómo consultar el catálogo.

Sí – para llaves principales, no – para llaves extranjeras (más en los documentos ).

 \d <table_name> 

en "psql" muestra una descripción de una tabla que incluye todos sus índices.

Esta consulta enumerará los índices que faltan en las keys externas , fuente original .

 -- 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, indpnetworking is not null as has_pnetworkingicate, 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_pnetworkingicate, 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_pnetworkingicate 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; 

Para una PRIMARY KEY , se creará un índice con el siguiente post:

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

Para una FOREIGN KEY , la restricción no se creará si no hay un índice en la tabla referida.

No se requiere un índice en la tabla de reference (aunque se desee) y, por lo tanto, no se creará implícitamente.

Me encanta cómo se explica esto en el artículo Buenas características de performance de EclipseLink 2.5

Indización de keys extranjeras

La primera característica es la indexing automática de keys externas. La mayoría de las personas asume incorrectamente que las bases de datos indexan foreign keys de forma pnetworkingeterminada. Bueno, no lo hacen. Las keys primarias se indexan automáticamente, pero las keys externas no. Esto significa que cualquier consulta basada en la key externa realizará escaneos completos de tabla. Esta es una relación OneToMany , ManyToMany o ElementCollection , así como muchas relaciones OneToOne , y la mayoría de las consultas sobre cualquier relación que implique uniones o comparaciones de objects . Esto puede ser un problema importante de performance, y siempre debe indexar los campos de keys externas.