Re: slow queries on system tables - Mailing list pgsql-general

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