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
