Postgresql – better way to exclude all “not actively created by a human” indexes than what I’m doing


My goal is to list the indexes which I, a human, have actually added into my database. I don't want to see ones that were created by PG when creating PKs and whatnot. I didn't even know it did that, but it sure seems to do. A lot. It's not obvious how to sort out all those, since there is no column such as "was_created_automatically_by_pg". I started with:

SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes;

That causes tons of "junk" records to be returned with the schemaname "pg_catalog". So, ignoring those:

SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname <> 'pg_catalog';

I still get a lot of indexes which I never created.

SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname <> 'pg_catalog' AND indexdef NOT LIKE 'CREATE UNIQUE INDEX %';

Now, all the resulting records seem to correspond to indexes which I remember having created, some with names such as "djhhcdhxchhhsxhsddf" and some with names that were auto-created, based on the columns it involves. Not sure how I made those since pgAdmin 4 refuses to let me create an index without manually naming it. (Which is quite annoying, might I add.)

However, what if I added a "UNIQE INDEX" myself? It feels like my own "final" query is "hiding" some records this way. It doesn't seem reliable.

How come they didn't have a column as I described above? Or is this actually pgAdmin 4 creating the indexes automatically? Is it actually possible to create a PRIMARY KEY without an internal index on it? I don't understand why anyone would ever want to create a PK which is not unique and indexed. That's the whole point of the field… to be unique and enable fast FKing from other tables.

It makes me anxious to see how many indexes there are which I never thought of. I assume that I shouldn't touch them, ever, but I always assumed that the PKs and whatnot had "internal" indexes which are never mentioned/displayed/returned.

Best Answer

This will return a list of indexes that are used to implement a constraint:

SELECT conindid::regclass
FROM pg_constraint
WHERE contype IN ('p', 'u', 'x');

Exclude these indexes from your query result, and you have thr list you desire.