Re: slow queries on system tables

As I told you before, these queries cannot be changed because the driver
creates them.
As I cannot change them how can I help Postgres to run it faster, just that.

My log_min_duration_statement = 500ms, so I find SQL some dozens of this sql
inside it daily, but I´m sure this sql is used thousands and thousands a
day.

duration: 566.415 ms execute PRSTMTST1590201604/PORTALST1590201604: 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(0, indnatts - 1), 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
'f10_0275, public' AND t.relname LIKE 'sys_var' ORDER BY
indisprimary::integer DESC, n.nspname, t.relname, c.relname, a.attnum

QUERY PLAN
Sort (cost=260362.69..260362.69 rows=1 width=470) (actual
time=59.526..59.527 rows=0 loops=1)
Sort Key: ((i.indisprimary)::integer) DESC, n.nspname, t.relname,
c.relname, a.attnum
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=1003.58..260362.68 rows=1 width=470) (actual
time=59.519..59.519 rows=0 loops=1)
-> Nested Loop (cost=1003.44..260360.98 rows=1 width=238) (actual
time=5.251..58.872 rows=424 loops=1)
-> Gather (cost=1003.02..260360.42 rows=1 width=174) (actual
time=5.232..72.894 rows=424 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Nested Loop (cost=3.02..259360.32 rows=1 width=174)
(actual time=8.357..48.200 rows=212 loops=2)
Join Filter: (t.oid = a.attrelid)
-> Hash Join (cost=2.59..259357.51 rows=4
width=138) (actual time=8.322..46.124 rows=212 loops=2)
Hash Cond: (i.indrelid = t.oid)
-> ProjectSet (cost=0.00..258260.16
rows=51008000 width=66) (actual time=0.011..33.557 rows=53694 loops=2)
-> Parallel Seq Scan on pg_index i
(cost=0.00..2710.08 rows=51008 width=64) (actual time=0.007..6.804
rows=43564 loops=2)
-> Hash (cost=2.44..2.44 rows=12 width=72)
(actual time=7.614..7.615 rows=210 loops=2)
Buckets: 1024 Batches: 1 Memory
Usage: 30kB
-> Index Scan using
pg_class_relname_nsp_index on pg_class t (cost=0.42..2.44 rows=12 width=72)
(actual time=0.029..7.556 rows=210 loops=2)
Index Cond: ((relname >=
'sys'::name) AND (relname < 'syt'::name))
Filter: (relname ~~
'sys_account'::text)
Rows Removed by Filter: 1696
-> Index Scan using
pg_attribute_relid_attnum_index on pg_attribute a (cost=0.42..0.69 rows=1
width=70) (actual time=0.009..0.009 rows=1 loops=424)
Index Cond: ((attrelid = i.indrelid) AND
(attnum = (i.indkey)[(generate_series(0, (i.indnatts - 1)))]))
-> Index Scan using pg_class_oid_index on pg_class c
(cost=0.42..0.56 rows=1 width=68) (actual time=0.005..0.005 rows=1
loops=424)
Index Cond: (oid = i.indexrelid)
-> Index Scan using pg_namespace_oid_index on pg_namespace n
(cost=0.14..0.92 rows=1 width=68) (actual time=0.001..0.001 rows=0
loops=424)
Index Cond: (oid = t.relnamespace)
Filter: (nspname ~~ 'f10_0511, public'::text)
Rows Removed by Filter: 1
Planning Time: 1.176 ms
Execution Time: 76.031 ms

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html