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

indexpostgresql

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.

Related Solutions

Sql-server – SQL Server 2008 query planner failing after index drop & recreate

Well, the issue is now resolved:

While it would seem logical to use filtered indexes (NOT NULL), to reduce database size and as so many sources on the web say, increase performance, the reality it seems is something else entirely.

In layman's terms, SQL Server query planner resolves even your basic inner joins without making any assumptions as to the content of the columns. Even though NULL values do not form a join, they must be included in the column index in order for query planner to use it, unless otherwise specified with predicates such as WHERE joinCol_ID IS NOT NULL. Basically, SQL Server does not use filtered indexes for joins at all, unless the queries themselves are modified to account for the filter value. Instead, it will create new statistics on these columns and / or use a clustered index scan or other indexes including the column, whichever it deems most effective. Using filtered indexes on foreign keys is therefore an absolutely horrid idea.

We still have no idea how months worth of testing this in multiple other environments never produced the same results outside of this one, single DB, but this is the way it's supposed to work. Apparently something that as far as we know is not related to cache, statistics or configurations, caused the production DB to behave differently and correctly detect and use the filtered indexes, while all of the testing environments simply used the old indexes (seeing as the indexes were dropped and recreated with the same name, this seems a valid theory even if there is no real proof).

So the lesson of the story: The web is filled with examples of how underused filtered indexes are, how awesome they can be. But this serious downside never popped up except as a nagging thought in the back of my head saying "if these are so great, then why aren't NULL values filtered out of indexes by default, since they only take up space and only serve a purpose in special circumstances"? Well, now I know why. :)

PostgreSQL – Return unique combinations of columns based on where clause

The execution plan shown does not seem to match the big SELECT DISTINCT query because the Sort and Unique steps are missing. Anyway you are correct than when retrieving ~50% of a table, index don't help. The best strategy is a big sequential scan of the main table and only fast hardware helps with that.

For the 2nd part of the question:

How would I go about selecting only the unique combinations of adjacent columns? Is this too complicated a task to perform through a database query? Would it speed up the query?

To remove duplicate combinations of adjacent columns, the structure of the resultset should be changed so that each output row has only one couple of adjacent columns along with their corresponding dimensions in the parallel coordinates graph. Well, except that the dimension for the 2nd column is not necessary since it's always the dimension for the other column plus one.

In one single query, this could be written like this:

WITH logs as (
  SELECT log_time_mapped, syslog_priority_mapped, 
     operation_mapped, message_code_mapped, protocol_mapped, 
     source_ip_mapped, destination_ip_mapped, 
     source_port_mapped, destination_port_mapped, 
     destination_service_mapped, direction_mapped, 
     connections_built_mapped, connections_torn_down_mapped, 
     hourofday_mapped, meridiem_mapped 
  FROM firewall_logs_mapped 
  WHERE operation = 'Built')
SELECT DISTINCT 1, log_time_mapped, syslog_priority_mapped FROM logs
UNION ALL
SELECT DISTINCT 2, syslog_priority_mapped, operation_mapped FROM logs
UNION ALL
SELECT DISTINCT 3, operation_mapped, message_code_mapped FROM logs
UNION ALL
...etc...
SELECT DISTINCT 14,  hourofday_mapped, meridiem_mapped FROM logs
;

The first SELECT DISTINCT subquery extracts the lines to draw between dimensions 1 and 2, the next subquery between dimensions 2 and 3, and so on. DISTINCT eliminates duplicates, so the client side doesn't have to do it. The UNION ALL concatenates the results without any further processing.

However it's a heavy query and it's dubious that it would be any faster than what you're already doing.

The WITH subquery is likely to gets slowly materialized on disk, so it might be interesting to compare the execution time with this other form repeating the same condition:

SELECT DISTINCT 1, log_time_mapped, syslog_priority_mapped
   FROM firewall_logs_mapped WHERE operation = 'Built'
UNION ALL
SELECT DISTINCT 2, syslog_priority_mapped, operation_mapped
   FROM firewall_logs_mapped WHERE operation = 'Built'
UNION ALL
SELECT DISTINCT 3, operation_mapped, message_code_mapped
   FROM firewall_logs_mapped WHERE operation = 'Built'
...etc...
;

Best Answer

Related Solutions

Sql-server – SQL Server 2008 query planner failing after index drop & recreate

PostgreSQL – Return unique combinations of columns based on where clause

Related Question