PostgreSQL: How to index all foreign keys?

EDIT : so, I wrote the query below and then thought... "hang on, Postgresql requires that foreign key targets have to have unique indices. " So I guess I misunderstood what you meant? You can use the below query to check that the source of your foreign keys have indices by substituing "conrelid" for "confrelid" and "conkey" for "confkey" (yeah, yeah, no aliases in the query...) Well, I guess it should be possible to go through the system catalogues... As usual, the best guide to the system catalogues is to use psql and do "\set ECHO_HIDDEN 1" and then see what SQL it generates for interesting "\d" commands.

Here's the SQL used to find the foreign keys for a table ("\d tablename") : $1 is the table OID, e.g. 'tablename'::regclass SELECT conname, conrelid::pg_catalog. Regclass, pg_catalog. Pg_get_constraintdef(c.

Oid, true) as condef FROM pg_catalog. Pg_constraint c WHERE c. Confrelid = $1 AND c.

Contype = 'f' ORDER BY 1 Seems that pg_constraint has columns conkey and confkey that look like they could be the column numbers that the key is defined across. Probably confkey is the column numbers in the foreign table since it's only non-null for foreign keys. Also, took me a while to realise this is the SQL to show foreign keys referencing the given table.

Which is what we want anyway So something this query shows the data beginning to take shape: select confrelid, conname, column_index, attname from pg_attribute join (select confrelid::regclass, conname, unnest(confkey) as column_index from pg_constraint where confrelid = 'ticket_status'::regclass) fkey on fkey. Confrelid = pg_attribute. Attrelid and fkey.

Column_index = pg_attribute. Attnum I'm going to be using 8.4 features like unnest you might be able to get along without I ended up with: select pg_index. Indexrelid::regclass, 'create index ' || relname || '_' || array_to_string(column_name_list, '_') || '_idx on ' || confrelid || ' (' || array_to_string(column_name_list, ',') || ')' from (select distinct confrelid, array_agg(attname) column_name_list, array_agg(attnum) as column_list from pg_attribute join (select confrelid::regclass, conname, unnest(confkey) as column_index from (select distinct confrelid, conname, confkey from pg_constraint join pg_class on pg_class.

Oid = pg_constraint. Confrelid join pg_namespace on pg_namespace. Oid = pg_class.

Relnamespace where nspname! ~ '^pg_' and nspname 'information_schema' ) fkey ) fkey on fkey. Confrelid = pg_attribute.

Attrelid and fkey. Column_index = pg_attribute. Attnum group by confrelid, conname ) candidate_index join pg_class on pg_class.

Oid = candidate_index. Confrelid left join pg_index on pg_index. Indrelid = confrelid and indkey::text = array_to_string(column_list, ' ') OK, this monstrosity prints out the candidate index commands and tries to match them up with existing indices.

So you can simply add "where indexrelid is null" on the end to get the commands to create indices that don't seem to exist This query doesn't deal with multi-column foreign keys very well; but imho if you're using those, you deserve trouble LATER EDIT : here's the query with the proposed edits up at the top put in. So this shows the commands to create indices that don't exist, on columns that are the source of a foreign key (not its target) select pg_index. Indexrelid::regclass, 'create index ' || relname || '_' || array_to_string(column_name_list, '_') || '_idx on ' || conrelid || ' (' || array_to_string(column_name_list, ',') || ')' from (select distinct conrelid, array_agg(attname) column_name_list, array_agg(attnum) as column_list from pg_attribute join (select conrelid::regclass, conname, unnest(conkey) as column_index from (select distinct conrelid, conname, conkey from pg_constraint join pg_class on pg_class.

Oid = pg_constraint. Conrelid join pg_namespace on pg_namespace. Oid = pg_class.

Relnamespace where nspname! ~ '^pg_' and nspname 'information_schema' ) fkey ) fkey on fkey. Conrelid = pg_attribute.

Attrelid and fkey. Column_index = pg_attribute. Attnum group by conrelid, conname ) candidate_index join pg_class on pg_class.

Oid = candidate_index. Conrelid left join pg_index on pg_index. Indrelid = conrelid and indkey::text = array_to_string(column_list, ' ') where indexrelid is null My experience is that this isn't really all that useful.

It suggests creating indices for things like reference codes that really don't need to be indexed.

EDIT: so, I wrote the query below and then thought... "hang on, Postgresql requires that foreign key targets have to have unique indices. " So I guess I misunderstood what you meant? You can use the below query to check that the source of your foreign keys have indices by substituing "conrelid" for "confrelid" and "conkey" for "confkey" (yeah, yeah, no aliases in the query...) Well, I guess it should be possible to go through the system catalogues... As usual, the best guide to the system catalogues is to use psql and do "\set ECHO_HIDDEN 1" and then see what SQL it generates for interesting "\d" commands.

Here's the SQL used to find the foreign keys for a table ("\d tablename") : -- $1 is the table OID, e.g. 'tablename'::regclass SELECT conname, conrelid::pg_catalog. Regclass, pg_catalog. Pg_get_constraintdef(c.

Oid, true) as condef FROM pg_catalog. Pg_constraint c WHERE c. Confrelid = $1 AND c.

Contype = 'f' ORDER BY 1; Seems that pg_constraint has columns conkey and confkey that look like they could be the column numbers that the key is defined across. Probably confkey is the column numbers in the foreign table since it's only non-null for foreign keys. Also, took me a while to realise this is the SQL to show foreign keys referencing the given table.

Which is what we want anyway. So something this query shows the data beginning to take shape: select confrelid, conname, column_index, attname from pg_attribute join (select confrelid::regclass, conname, unnest(confkey) as column_index from pg_constraint where confrelid = 'ticket_status'::regclass) fkey on fkey. Confrelid = pg_attribute.

Attrelid and fkey. Column_index = pg_attribute. Attnum I'm going to be using 8.4 features like unnest... you might be able to get along without.

I ended up with: select pg_index. Indexrelid::regclass, 'create index ' || relname || '_' || array_to_string(column_name_list, '_') || '_idx on ' || confrelid || ' (' || array_to_string(column_name_list, ',') || ')' from (select distinct confrelid, array_agg(attname) column_name_list, array_agg(attnum) as column_list from pg_attribute join (select confrelid::regclass, conname, unnest(confkey) as column_index from (select distinct confrelid, conname, confkey from pg_constraint join pg_class on pg_class. Oid = pg_constraint.

Confrelid join pg_namespace on pg_namespace. Oid = pg_class. Relnamespace where nspname!

~ '^pg_' and nspname 'information_schema' ) fkey ) fkey on fkey. Confrelid = pg_attribute. Attrelid and fkey.

Column_index = pg_attribute. Attnum group by confrelid, conname ) candidate_index join pg_class on pg_class. Oid = candidate_index.

Confrelid left join pg_index on pg_index. Indrelid = confrelid and indkey::text = array_to_string(column_list, ' ') OK, this monstrosity prints out the candidate index commands and tries to match them up with existing indices.So you can simply add "where indexrelid is null" on the end to get the commands to create indices that don't seem to exist. This query doesn't deal with multi-column foreign keys very well; but imho if you're using those, you deserve trouble.

LATER EDIT: here's the query with the proposed edits up at the top put in.So this shows the commands to create indices that don't exist, on columns that are the source of a foreign key (not its target). Select pg_index. Indexrelid::regclass, 'create index ' || relname || '_' || array_to_string(column_name_list, '_') || '_idx on ' || conrelid || ' (' || array_to_string(column_name_list, ',') || ')' from (select distinct conrelid, array_agg(attname) column_name_list, array_agg(attnum) as column_list from pg_attribute join (select conrelid::regclass, conname, unnest(conkey) as column_index from (select distinct conrelid, conname, conkey from pg_constraint join pg_class on pg_class.

Oid = pg_constraint. Conrelid join pg_namespace on pg_namespace. Oid = pg_class.

Relnamespace where nspname! ~ '^pg_' and nspname 'information_schema' ) fkey ) fkey on fkey. Conrelid = pg_attribute.

Attrelid and fkey. Column_index = pg_attribute. Attnum group by conrelid, conname ) candidate_index join pg_class on pg_class.

Oid = candidate_index. Conrelid left join pg_index on pg_index. Indrelid = conrelid and indkey::text = array_to_string(column_list, ' ') where indexrelid is null My experience is that this isn't really all that useful.It suggests creating indices for things like reference codes that really don't need to be indexed.

After your edit, this looks like it could well work, I'm gonna run a comparison between this and leonbloy's answer – biggusjimmus Jun 4 '10 at 16:19 This one doesn't show foreign keys but primary keys only. It makes no sense because you don't need index for pk because pk constraint "is" an index – peperg Nov 4 '10 at 10:14 @peperg no, the query doesn't have anything to do with primary keys directly: it finds the target for foreign keys. Of course, if your database is well-designed, then the targets for foreign keys are primary keys.

Note the edit at the top suggesting how it could be changed to find where the source of a foreign key isn't indexed (which actually isn't too useful imho). Sorry for the confusion, the answer should probably be rewritten. – araqnid Nov 5 '10 at 0:46 I have table "x" with column "id" and table "y" with column "x_id".

X.Id is a PK so there is an idnex on it. I have foreign key y. X_id -> x.Id .

I'd like this code to generate index on y. X_id but not on x.Id (there is a PK index) – peperg Nov 5 '10 at 9:34 @peperg right, so change confrelid to conrelid and confkey to conkey look at the foreign keys that way round, and append where indexrelid is null to only show indices that don't exist. I'll update the answer to show the edited query.

– araqnid Nov 5 '10 at 12:21.

The information is inside the catalog tables. But it seem it's not very straightforward to do want you need, specially if there are already some indexes created (and what about multicolumn indexes...) If you don't have any indexed FK , you could do something quick and dirty, as SELECT 'CREATE INDEX ' || table_name || '_' || column_name || '_idx ON (' || table_name || '(' || column_name || ');' from foreign_key_tables where schema = 'public'; You'd replace with the schema you are interested, dump that to a file, edit, check, pray and feed to psql. BEWARE this procedure does not detect already existant indexes.

Ah, foreign_key_tables is an informational view created as: CREATE VIEW foreign_key_tables AS SELECT n. Nspname AS schema, cl. Relname AS table_name, a.

Attname AS column_name, ct. Conname AS key_name, nf. Nspname AS foreign_schema, clf.

Relname AS foreign_table_name, af. Attname AS foreign_column_name, pg_get_constraintdef(ct. Oid) AS create_sql FROM pg_catalog.

Pg_attribute a JOIN pg_catalog. Pg_class cl ON (a. Attrelid = cl.

Oid AND cl. Relkind = 'r') JOIN pg_catalog. Pg_namespace n ON (n.

Oid = cl. Relnamespace) JOIN pg_catalog. Pg_constraint ct ON (a.

Attrelid = ct. Conrelid AND ct. Confrelid!

= 0 AND ct. Conkey1 = a. Attnum) JOIN pg_catalog.

Pg_class clf ON (ct. Confrelid = clf. Oid AND clf.

Relkind = 'r') JOIN pg_catalog. Pg_namespace nf ON (nf. Oid = clf.

Relnamespace) JOIN pg_catalog. Pg_attribute af ON (af. Attrelid = ct.

Confrelid AND af. Attnum = ct. Confkey1).

This looks to be more what I'm after. Sadly, my table/column names get a bit too long to just run it directly. Let me play with it a bit.

– biggusjimmus Jun 4 '10 at 16:17 good solution by leonbloy!. This is what I'm looking for because I cannot create those indexes in nhibernate mapping file (hbm). My last solution would be to execute sql script.

– Murad Mohd Zain Aug 8 at 0:47.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.