The driver I´m using does some queries on system tables and obviously I cannot change them because are driver inside. These two queries are usually fast, but sometimes they take 8 or 15 times more to run. The question is, can I change something on these tables ? Can i create an index, can I load them into cache ? What can I do to speed up if I cannot change the way these queries are written ? Extracted from pg_stat_statements: Query 1 - calls: 200000 times, min_time ~ 20ms, mean_time ~ 50ms, max_time ~ 480ms SELECT current_database() AS TABLE_CATALOG, n.nspname AS TABLE_SCHEMA, t.relname AS TABLE_NAME, t.oid AS TABLE_OID, current_database() AS INDEX_CATALOG, n.nspname AS INDEX_SCHEMA, c.relname AS INDEX_NAME, c.oid AS INDEX_OID, a.attname AS COLUMN_NAME, a.attnum AS COLUMN_POSITION,indoption[index] & $1 AS DESCENDING , indisunique::integer AS UNIQUE FROM (SELECT generate_series($2, indnatts - $3), indrelid, indexrelid, indkey, indoption, indisunique, indisprimary FROM pg_index i) i(index, indrelid, indexrelid, indkey, indoption, indisunique, indisprimary) INNER JOIN pg_class c ON c.oid = indexrelid INNER JOIN pg_class t ON t.oid = indrelid INNER JOIN pg_namespace n ON n.oid = t.relnamespace INNER JOIN pg_attribute a ON a.attrelid = indrelid AND a.attnum = indkey[index] WHERE n.nspname LIKE $4 AND t.relname LIKE $5 ORDER BY indisprimary::integer DESC, n.nspname, t.relname, c.relname, a.attnum Query 1 - calls: 20000 times, min_time ~ 70ms, mean_time ~ 95ms, max_time ~ 170ms. SELECT pg_attribute.attname FROM pg_index, pg_class, pg_attribute WHERE upper(pg_class.relname) = $1 AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
