pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedhashjoins, index loops to retrieve pk/ux constrains in pg12
7+ messages / 3 participants
[nested] [flat]
* hashjoins, index loops to retrieve pk/ux constrains in pg12
@ 2021-09-23 13:00 Arturas Mazeika <mazeika@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Arturas Mazeika @ 2021-09-23 13:00 UTC (permalink / raw)
To: pgsql-performance
Dear All,
We use (a somewhat old version of) Liquibase to implement changes in our
databases. We also use Liquibase scripts to keep track of database
migration (mostly schema, but a little bit of data too). At some point we
cleaned up all our primary indexes as well as constraints and implemented
them as Liquibase scripts (i.e., recreated them). For that purpose
Liquibase usually fires a query like this to postgres:
SELECT
FK.TABLE_NAME as "TABLE_NAME"
, CU.COLUMN_NAME as "COLUMN_NAME"
, PK.TABLE_NAME as "REFERENCED_TABLE_NAME"
, PT.COLUMN_NAME as "REFERENCED_COLUMN_NAME"
, C.CONSTRAINT_NAME as "CONSTRAINT_NAME"
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON
C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME
= CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME
, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE
lower(FK.TABLE_NAME)='secrole_condcollection'
Postgres decides to use a hashjoin (see the query plan below) and 20
seconds later spits out 2 rows. It does not matter if one sets random_page_cost
to 2, 1.5, or 1.0 (or even 0.09, which does not make any sense) one waits
20 seconds. hashjoin is used to answer this query. If one switches off the
hashjoins (set enable_hashjoin = false;), it takes 0.1 second to compute to
spit two rows. The views in information_schema are tiny:
select 'REFERENTIAL_CONSTRAINTS', count(1) from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
union all
select 'TABLE_CONSTRAINTS', count(1) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
union all
select 'KEY_COLUMN_USAGE', count(1) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
union all
select 'TABLE_CONSTRAINTS', count(1) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
REFERENTIAL_CONSTRAINTS 1079
TABLE_CONSTRAINTS 4359
KEY_COLUMN_USAGE 1999
TABLE_CONSTRAINTS 4359
the whole schema eats up 300Kb space:
SELECT pg_size_pretty(sum(pg_total_relation_size(C.oid))) AS "total_size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname = 'information_schema'
--344 kB
Any clues how I could "save face of the hash joins"?
Cheers,
Arturas
query plan hash (please note that random_page_cost is overwritten there:
set enable_hashjoin = 1;
SELECT
FK.TABLE_NAME as "TABLE_NAME"
, CU.COLUMN_NAME as "COLUMN_NAME"
, PK.TABLE_NAME as "REFERENCED_TABLE_NAME"
, PT.COLUMN_NAME as "REFERENCED_COLUMN_NAME"
, C.CONSTRAINT_NAME as "CONSTRAINT_NAME"
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON
C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME
= CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME
, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE
lower(FK.TABLE_NAME)='secrole_condcollection'
Nested Loop (cost=2174.36..13670.47 rows=1 width=320) (actual
time=5499.728..26310.137 rows=2 loops=1)
Output: "*SELECT* 1".table_name,
(a.attname)::information_schema.sql_identifier, "*SELECT*
1_1".table_name, (a_1.attname)::information_schema.sql_identifier,
(con.conname)::information_schema.sql_identifier
Inner Unique: true
Buffers: shared hit=1961035
-> Nested Loop (cost=2174.07..13670.12 rows=1 width=296) (actual
time=5499.716..26310.115 rows=2 loops=1)
Output: con.conname, "*SELECT* 1".table_name, "*SELECT*
1_1".table_name, a.attname, r.oid,
(information_schema._pg_expandarray(c_1.conkey)), r.relowner
Inner Unique: true
Buffers: shared hit=1961029
-> Nested Loop (cost=2173.78..13669.78 rows=1 width=272)
(actual time=5499.689..26310.066 rows=2 loops=1)
Output: con.conname, "*SELECT* 1".table_name, "*SELECT*
1_1".table_name, r_2.oid,
(information_schema._pg_expandarray(c_3.conkey)), r_2.relowner, r.oid,
(information_schema._pg_expandarray(c_1.conkey)), r.relowner
Join Filter: (("*SELECT* 1_2".table_name)::name =
("*SELECT* 1_1".table_name)::name)
Rows Removed by Join Filter: 1670
Buffers: shared hit=1961023
-> Hash Join (cost=497.90..5313.80 rows=1 width=104)
(actual time=7.586..29.643 rows=836 loops=1)
Output: "*SELECT* 1_2".table_name, r.oid,
(information_schema._pg_expandarray(c_1.conkey)), r.relowner
Hash Cond: (c_1.conname = ("*SELECT*
1_2".constraint_name)::name)
Buffers: shared hit=3355
-> ProjectSet (cost=324.56..1716.71 rows=249000
width=341) (actual time=1.385..21.087 rows=1983 loops=1)
Output: r.oid, NULL::name, r.relowner,
NULL::name, NULL::name, NULL::oid, c_1.conname, NULL::"char",
NULL::oid, NULL::smallint[], NULL::oid,
information_schema._pg_expandarray(c_1.conkey)
Buffers: shared hit=328
-> Hash Join (cost=324.56..408.21 rows=249
width=95) (actual time=1.246..6.050 rows=1707 loops=1)
Output: c_1.conkey, r.oid, r.relowner,
c_1.conname
Inner Unique: true
Hash Cond: (c_1.connamespace = nc.oid)
Buffers: shared hit=328
-> Hash Join (cost=323.42..405.96
rows=249 width=99) (actual time=1.226..4.977 rows=1707 loops=1)
Output: r.oid, r.relowner,
c_1.conname, c_1.conkey, c_1.connamespace
Inner Unique: true
Hash Cond: (r.relnamespace = nr.oid)
Buffers: shared hit=327
-> Hash Join
(cost=322.30..403.16 rows=374 width=103) (actual time=1.209..3.807
rows=1707 loops=1)
Output: r.oid, r.relowner,
r.relnamespace, c_1.conname, c_1.conkey, c_1.connamespace
Inner Unique: true
Hash Cond: (c_1.conrelid = r.oid)
Buffers: shared hit=326
-> Seq Scan on
pg_catalog.pg_constraint c_1 (cost=0.00..76.23 rows=1760 width=95)
(actual time=0.006..0.894 rows=1707 loops=1)
Output: c_1.oid,
c_1.conname, c_1.connamespace, c_1.contype, c_1.condeferrable,
c_1.condeferred, c_1.convalidated, c_1.conrelid, c_1.contypid,
c_1.conindid, c_1.conparentid, c_1.confrelid, c_1.confupdtype,
c_1.confdeltype, c_1.confmatchtype, c_1.conislocal, c_1.coninhcount,
c_1.connoinherit, c_1.conkey, c_1.confkey, c_1.conpfeqop,
c_1.conppeqop, c_1.conffeqop, c_1.conexclop, c_1.conbin
Filter: (c_1.contype
= ANY ('{p,u,f}'::"char"[]))
Rows Removed by Filter: 2
Buffers: shared hit=52
-> Hash
(cost=313.84..313.84 rows=677 width=12) (actual time=1.135..1.136
rows=694 loops=1)
Output: r.oid,
r.relowner, r.relnamespace
Buckets: 1024
Batches: 1 Memory Usage: 38kB
Buffers: shared hit=274
-> Seq Scan on
pg_catalog.pg_class r (cost=0.00..313.84 rows=677 width=12) (actual
time=0.009..1.024 rows=694 loops=1)
Output: r.oid,
r.relowner, r.relnamespace
Filter:
(r.relkind = ANY ('{r,p}'::"char"[]))
Rows Removed
by Filter: 2559
Buffers: shared hit=274
-> Hash (cost=1.07..1.07
rows=4 width=4) (actual time=0.009..0.009 rows=7 loops=1)
Output: nr.oid
Buckets: 1024 Batches: 1
Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on
pg_catalog.pg_namespace nr (cost=0.00..1.07 rows=4 width=4) (actual
time=0.004..0.006 rows=7 loops=1)
Output: nr.oid
Filter: (NOT
pg_is_other_temp_schema(nr.oid))
Rows Removed by Filter: 2
Buffers: shared hit=1
-> Hash (cost=1.06..1.06 rows=6
width=4) (actual time=0.008..0.009 rows=9 loops=1)
Output: nc.oid
Buckets: 1024 Batches: 1
Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on
pg_catalog.pg_namespace nc (cost=0.00..1.06 rows=6 width=4) (actual
time=0.003..0.004 rows=9 loops=1)
Output: nc.oid
Buffers: shared hit=1
-> Hash (cost=173.32..173.32 rows=1 width=128)
(actual time=6.192..6.196 rows=595 loops=1)
Output: "*SELECT* 1_2".constraint_name,
"*SELECT* 1_2".table_name
Buckets: 1024 Batches: 1 Memory Usage: 101kB
Buffers: shared hit=3027
-> Subquery Scan on "*SELECT* 1_2"
(cost=0.28..173.32 rows=1 width=128) (actual time=0.041..5.955
rows=595 loops=1)
Output: "*SELECT*
1_2".constraint_name, "*SELECT* 1_2".table_name
Buffers: shared hit=3027
-> Nested Loop (cost=0.28..173.31
rows=1 width=512) (actual time=0.040..5.849 rows=595 loops=1)
Output:
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(c_2.conname)::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(r_1.relname)::information_schema.sql_identifier,
NULL::information_schema.character_data,
NULL::information_schema.yes_or_no,
NULL::information_schema.yes_or_no, NULL::information_schema.yes_or_no
Inner Unique: true
Join Filter: (r_1.relnamespace = nr_1.oid)
Rows Removed by Join Filter: 1836
Buffers: shared hit=3027
-> Nested Loop
(cost=0.28..172.19 rows=1 width=132) (actual time=0.033..3.736
rows=595 loops=1)
Output: c_2.conname,
r_1.relname, r_1.relnamespace
Inner Unique: true
Join Filter:
(c_2.connamespace = nc_1.oid)
Rows Removed by Join Filter: 3026
Buffers: shared hit=2432
-> Nested Loop
(cost=0.28..171.05 rows=1 width=136) (actual time=0.027..1.913
rows=595 loops=1)
Output: c_2.conname,
c_2.connamespace, r_1.relname, r_1.relnamespace
Inner Unique: true
Buffers: shared hit=1837
-> Seq Scan on
pg_catalog.pg_constraint c_2 (cost=0.00..96.05 rows=9 width=72)
(actual time=0.012..0.508 rows=595 loops=1)
Output:
c_2.oid, c_2.conname, c_2.connamespace, c_2.contype,
c_2.condeferrable, c_2.condeferred, c_2.convalidated, c_2.conrelid,
c_2.contypid, c_2.conindid, c_2.conparentid, c_2.confrelid,
c_2.confupdtype, c_2.confdeltype, c_2.confmatchtype, c_2.conislocal,
c_2.coninhcount, c_2.connoinherit, c_2.conkey, c_2.confkey,
c_2.conpfeqop, c_2.conppeqop, c_2.conffeqop, c_2.conexclop, c_2.conbin
Filter:
((c_2.contype <> ALL ('{t,x}'::"char"[])) AND ((CASE c_2.contype WHEN
'c'::"char" THEN 'CHECK'::text WHEN 'f'::"char" THEN 'FOREIGN
KEY'::text WHEN 'p'::"char" THEN 'PRIMARY KEY'::text WHEN 'u'::"char"
THEN 'UNIQUE'::text ELSE NULL::text END)::text = 'PRIMARY KEY'::text))
Rows Removed
by Filter: 1114
Buffers: shared hit=52
-> Index Scan using
pg_class_oid_index on pg_catalog.pg_class r_1 (cost=0.28..8.33 rows=1
width=72) (actual time=0.002..0.002 rows=1 loops=595)
Output:
r_1.oid, r_1.relname, r_1.relnamespace, r_1.reltype, r_1.reloftype,
r_1.relowner, r_1.relam, r_1.relfilenode, r_1.reltablespace,
r_1.relpages, r_1.reltuples, r_1.relallvisible, r_1.reltoastrelid,
r_1.relhasindex, r_1.relisshared, r_1.relpersistence, r_1.relkind,
r_1.relnatts, r_1.relchecks, r_1.relhasrules, r_1.relhastriggers,
r_1.relhassubclass, r_1.relrowsecurity, r_1.relforcerowsecurity,
r_1.relispopulated, r_1.relreplident, r_1.relispartition,
r_1.relrewrite, r_1.relfrozenxid, r_1.relminmxid, r_1.relacl,
r_1.reloptions, r_1.relpartbound
Index Cond:
(r_1.oid = c_2.conrelid)
Filter:
((r_1.relkind = ANY ('{r,p}'::"char"[])) AND
(pg_has_role(r_1.relowner, 'USAGE'::text) OR
has_table_privilege(r_1.oid, 'INSERT, UPDATE, DELETE, TRUNCATE,
REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r_1.oid,
'INSERT, UPDATE, REFERENCES'::text)))
Buffers: shared hit=1785
-> Seq Scan on
pg_catalog.pg_namespace nc_1 (cost=0.00..1.06 rows=6 width=4) (actual
time=0.000..0.001 rows=6 loops=595)
Output: nc_1.oid,
nc_1.nspname, nc_1.nspowner, nc_1.nspacl
Buffers: shared hit=595
-> Seq Scan on
pg_catalog.pg_namespace nr_1 (cost=0.00..1.07 rows=4 width=4) (actual
time=0.001..0.001 rows=4 loops=595)
Output: nr_1.oid,
nr_1.nspname, nr_1.nspowner, nr_1.nspacl
Filter: (NOT
pg_is_other_temp_schema(nr_1.oid))
Rows Removed by Filter: 2
Buffers: shared hit=595
-> Nested Loop (cost=1675.88..8355.96 rows=1
width=232) (actual time=9.154..31.424 rows=2 loops=836)
Output: con.conname, "*SELECT* 1".table_name,
"*SELECT* 1_1".table_name, r_2.oid,
(information_schema._pg_expandarray(c_3.conkey)), r_2.relowner
Join Filter: (pkc.conname = ("*SELECT*
1_1".constraint_name)::name)
Rows Removed by Join Filter: 8572
Buffers: shared hit=1957668
-> Hash Join (cost=1258.23..6074.13 rows=1
width=232) (actual time=8.894..11.130 rows=2 loops=836)
Output: con.conname, pkc.conname, "*SELECT*
1".table_name, r_2.oid,
(information_schema._pg_expandarray(c_3.conkey)), r_2.relowner
Hash Cond: (c_3.conname = con.conname)
Buffers: shared hit=44349
-> ProjectSet (cost=324.56..1716.71
rows=249000 width=341) (actual time=0.013..10.797 rows=1983 loops=836)
Output: r_2.oid, NULL::name,
r_2.relowner, NULL::name, NULL::name, NULL::oid, c_3.conname,
NULL::"char", NULL::oid, NULL::smallint[], NULL::oid,
information_schema._pg_expandarray(c_3.conkey)
Buffers: shared hit=43748
-> Hash Join (cost=324.56..408.21
rows=249 width=95) (actual time=0.007..2.055 rows=1707 loops=836)
Output: c_3.conkey, r_2.oid,
r_2.relowner, c_3.conname
Inner Unique: true
Hash Cond: (c_3.connamespace = nc_2.oid)
Buffers: shared hit=43748
-> Hash Join
(cost=323.42..405.96 rows=249 width=99) (actual time=0.006..1.624
rows=1707 loops=836)
Output: r_2.oid,
r_2.relowner, c_3.conname, c_3.conkey, c_3.connamespace
Inner Unique: true
Hash Cond:
(r_2.relnamespace = nr_2.oid)
Buffers: shared hit=43747
-> Hash Join
(cost=322.30..403.16 rows=374 width=103) (actual time=0.006..1.224
rows=1707 loops=836)
Output: r_2.oid,
r_2.relowner, r_2.relnamespace, c_3.conname, c_3.conkey,
c_3.connamespace
Inner Unique: true
Hash Cond:
(c_3.conrelid = r_2.oid)
Buffers: shared hit=43746
-> Seq Scan on
pg_catalog.pg_constraint c_3 (cost=0.00..76.23 rows=1760 width=95)
(actual time=0.004..0.511 rows=1707 loops=836)
Output:
c_3.oid, c_3.conname, c_3.connamespace, c_3.contype,
c_3.condeferrable, c_3.condeferred, c_3.convalidated, c_3.conrelid,
c_3.contypid, c_3.conindid, c_3.conparentid, c_3.confrelid,
c_3.confupdtype, c_3.confdeltype, c_3.confmatchtype, c_3.conislocal,
c_3.coninhcount, c_3.connoinherit, c_3.conkey, c_3.confkey,
c_3.conpfeqop, c_3.conppeqop, c_3.conffeqop, c_3.conexclop, c_3.conbin
Filter:
(c_3.contype = ANY ('{p,u,f}'::"char"[]))
Rows Removed
by Filter: 2
Buffers:
shared hit=43472
-> Hash
(cost=313.84..313.84 rows=677 width=12) (actual time=0.988..0.989
rows=694 loops=1)
Output:
r_2.oid, r_2.relowner, r_2.relnamespace
Buckets: 1024
Batches: 1 Memory Usage: 38kB
Buffers: shared hit=274
-> Seq Scan
on pg_catalog.pg_class r_2 (cost=0.00..313.84 rows=677 width=12)
(actual time=0.006..0.875 rows=694 loops=1)
Output:
r_2.oid, r_2.relowner, r_2.relnamespace
Filter:
(r_2.relkind = ANY ('{r,p}'::"char"[]))
Rows
Removed by Filter: 2559
Buffers:
shared hit=274
-> Hash (cost=1.07..1.07
rows=4 width=4) (actual time=0.009..0.010 rows=7 loops=1)
Output: nr_2.oid
Buckets: 1024
Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on
pg_catalog.pg_namespace nr_2 (cost=0.00..1.07 rows=4 width=4) (actual
time=0.004..0.007 rows=7 loops=1)
Output: nr_2.oid
Filter: (NOT
pg_is_other_temp_schema(nr_2.oid))
Rows Removed
by Filter: 2
Buffers: shared hit=1
-> Hash (cost=1.06..1.06
rows=6 width=4) (actual time=0.012..0.013 rows=9 loops=1)
Output: nc_2.oid
Buckets: 1024 Batches: 1
Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on
pg_catalog.pg_namespace nc_2 (cost=0.00..1.06 rows=6 width=4) (actual
time=0.007..0.009 rows=9 loops=1)
Output: nc_2.oid
Buffers: shared hit=1
-> Hash (cost=933.65..933.65 rows=1
width=256) (actual time=2.158..2.170 rows=2 loops=1)
Output: con.conname, pkc.conname,
"*SELECT* 1".table_name, "*SELECT* 1".constraint_name
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=601
-> Nested Loop (cost=5.71..933.65
rows=1 width=256) (actual time=1.185..2.163 rows=2 loops=1)
Output: con.conname,
pkc.conname, "*SELECT* 1".table_name, "*SELECT* 1".constraint_name
Inner Unique: true
Join Filter: (d2.refobjid = pkc.oid)
Buffers: shared hit=601
-> Nested Loop
(cost=5.43..933.00 rows=1 width=200) (actual time=1.174..2.146 rows=2
loops=1)
Output: con.conname,
con.confrelid, d2.refobjid, "*SELECT* 1".table_name, "*SELECT*
1".constraint_name
Buffers: shared hit=593
-> Nested Loop
(cost=5.15..931.14 rows=1 width=200) (actual time=1.163..2.129 rows=2
loops=1)
Output: con.conname,
con.confrelid, d1.refobjid, "*SELECT* 1".table_name, "*SELECT*
1".constraint_name
Buffers: shared hit=587
-> Nested Loop
(cost=4.86..929.16 rows=1 width=200) (actual time=1.147..2.108 rows=2
loops=1)
Output:
con.conname, con.oid, con.confrelid, "*SELECT* 1".table_name,
"*SELECT* 1".constraint_name
Inner Unique: true
Join Filter:
(con.connamespace = ncon.oid)
Rows Removed
by Join Filter: 10
Buffers: shared hit=581
-> Nested
Loop (cost=4.86..928.02 rows=1 width=204) (actual time=1.143..2.100
rows=2 loops=1)
Output:
con.conname, con.connamespace, con.oid, con.confrelid, "*SELECT*
1".table_name, "*SELECT* 1".constraint_name
Inner Unique: true
Buffers:
shared hit=579
->
Nested Loop (cost=4.58..925.06 rows=2 width=208) (actual
time=1.129..2.082 rows=2 loops=1)
Output: con.conname, con.connamespace, con.conrelid, con.oid,
con.confrelid, "*SELECT* 1".table_name, "*SELECT* 1".constraint_name
Buffers: shared hit=573
->
Append (cost=4.30..900.14 rows=3 width=128) (actual
time=1.105..2.056 rows=5 loops=1)
Buffers: shared hit=560
-> Subquery Scan on "*SELECT* 1" (cost=4.30..449.91 rows=1
width=128) (actual time=1.104..1.121 rows=3 loops=1)
Output: "*SELECT* 1".table_name, "*SELECT* 1".constraint_name
Buffers: shared hit=282
-> Nested Loop (cost=4.30..449.90 rows=1 width=512) (actual
time=1.103..1.119 rows=3 loops=1)
Output: NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(c_4.conname)::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(r_3.relname)::information_schema.sql_identifier,
NULL::information_schema.character_data,
NULL::information_schema.yes_or_no,
NULL::information_schema.yes_or_no, NULL::information_schema.yes_or_no
Inner Unique: true
Join Filter: (c_4.connamespace = nc_3.oid)
Rows Removed by Join Filter: 15
Buffers: shared hit=282
-> Nested Loop (cost=4.30..448.76 rows=1 width=132)
(actual time=1.096..1.104 rows=3 loops=1)
Output: r_3.relname, c_4.conname,
c_4.connamespace
Buffers: shared hit=279
-> Nested Loop (cost=0.00..434.55 rows=1
width=68) (actual time=1.062..1.066 rows=1 loops=1)
Output: r_3.relname, r_3.oid
Join Filter: (nr_3.oid = r_3.relnamespace)
Rows Removed by Join Filter: 6
Buffers: shared hit=275
-> Seq Scan on pg_catalog.pg_namespace
nr_3 (cost=0.00..1.07 rows=4 width=4) (actual time=0.009..0.015
rows=7 loops=1)
Output: nr_3.oid, nr_3.nspname,
nr_3.nspowner, nr_3.nspacl
Filter: (NOT
pg_is_other_temp_schema(nr_3.oid))
Rows Removed by Filter: 2
Buffers: shared hit=1
-> Materialize (cost=0.00..433.36 rows=2
width=72) (actual time=0.004..0.149 rows=1 loops=7)
Output: r_3.relname,
r_3.relnamespace, r_3.oid
Buffers: shared hit=274
-> Seq Scan on pg_catalog.pg_class
r_3 (cost=0.00..433.35 rows=2 width=72) (actual time=0.026..1.039
rows=1 loops=1)
Output: r_3.relname,
r_3.relnamespace, r_3.oid
Filter: ((r_3.relkind = ANY
('{r,p}'::"char"[])) AND
(lower(((r_3.relname)::information_schema.sql_identifier)::text) =
'secrole_condcollection'::text) AND (pg_has_role(r_3.relowner,
'USAGE'::text) OR has_table_privilege(r_3.oid, 'INSERT, UPDATE,
DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR
has_any_column_privilege(r_3.oid, 'INSERT, UPDATE,
REFERENCES'::text)))
Rows Removed by Filter: 3252
Buffers: shared hit=274
-> Bitmap Heap Scan on pg_catalog.pg_constraint
c_4 (cost=4.30..14.18 rows=3 width=72) (actual time=0.026..0.029
rows=3 loops=1)
Output: c_4.oid, c_4.conname,
c_4.connamespace, c_4.contype, c_4.condeferrable, c_4.condeferred,
c_4.convalidated, c_4.conrelid, c_4.contypid, c_4.conindid,
c_4.conparentid, c_4.confrelid, c_4.confupdtype, c_4.confdeltype,
c_4.confmatchtype, c_4.conislocal, c_4.coninhcount, c_4.connoinherit,
c_4.conkey, c_4.confkey, c_4.conpfeqop, c_4.conppeqop, c_4.conffeqop,
c_4.conexclop, c_4.conbin
Recheck Cond: (c_4.conrelid = r_3.oid)
Filter: (c_4.contype <> ALL
('{t,x}'::"char"[]))
Heap Blocks: exact=2
Buffers: shared hit=4
-> Bitmap Index Scan on
pg_constraint_conrelid_contypid_conname_index (cost=0.00..4.30 rows=3
width=0) (actual time=0.020..0.020 rows=3 loops=1)
Index Cond: (c_4.conrelid = r_3.oid)
Buffers: shared hit=2
-> Seq Scan on pg_catalog.pg_namespace nc_3
(cost=0.00..1.06 rows=6 width=4) (actual time=0.002..0.002 rows=6
loops=3)
Output: nc_3.oid, nc_3.nspname, nc_3.nspowner,
nc_3.nspacl
Buffers: shared hit=3
-> Subquery Scan on "*SELECT* 2" (cost=0.29..450.21 rows=2
width=128) (actual time=0.924..0.931 rows=2 loops=1)
Output: "*SELECT* 2".table_name, "*SELECT* 2".constraint_name
Buffers: shared hit=278
-> Nested Loop (cost=0.29..450.19 rows=2 width=512) (actual
time=0.923..0.929 rows=2 loops=1)
Output: NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier, (((((((nr_4.oid)::text ||
'_'::text) || (r_4.oid)::text) || '_'::text) || (a_2.attnum)::text) ||
'_not_null'::text))::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(r_4.relname)::information_schema.sql_identifier,
NULL::information_schema.character_data,
NULL::information_schema.yes_or_no,
NULL::information_schema.yes_or_no, NULL::information_schema.yes_or_no
Buffers: shared hit=278
-> Nested Loop (cost=0.00..434.55 rows=1 width=72)
(actual time=0.904..0.907 rows=1 loops=1)
Output: nr_4.oid, r_4.oid, r_4.relname
Join Filter: (nr_4.oid = r_4.relnamespace)
Rows Removed by Join Filter: 6
Buffers: shared hit=275
-> Seq Scan on pg_catalog.pg_namespace nr_4
(cost=0.00..1.07 rows=4 width=4) (actual time=0.004..0.007 rows=7
loops=1)
Output: nr_4.oid, nr_4.nspname,
nr_4.nspowner, nr_4.nspacl
Filter: (NOT
pg_is_other_temp_schema(nr_4.oid))
Rows Removed by Filter: 2
Buffers: shared hit=1
-> Materialize (cost=0.00..433.36 rows=2
width=72) (actual time=0.004..0.128 rows=1 loops=7)
Output: r_4.oid, r_4.relname,
r_4.relnamespace
Buffers: shared hit=274
-> Seq Scan on pg_catalog.pg_class r_4
(cost=0.00..433.35 rows=2 width=72) (actual time=0.021..0.893 rows=1
loops=1)
Output: r_4.oid, r_4.relname,
r_4.relnamespace
Filter: ((r_4.relkind = ANY
('{r,p}'::"char"[])) AND
(lower(((r_4.relname)::information_schema.sql_identifier)::text) =
'secrole_condcollection'::text) AND (pg_has_role(r_4.relowner,
'USAGE'::text) OR has_table_privilege(r_4.oid, 'INSERT, UPDATE,
DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR
has_any_column_privilege(r_4.oid, 'INSERT, UPDATE,
REFERENCES'::text)))
Rows Removed by Filter: 3252
Buffers: shared hit=274
-> Index Scan using pg_attribute_relid_attnum_index on
pg_catalog.pg_attribute a_2 (cost=0.29..15.56 rows=2 width=6) (actual
time=0.014..0.015 rows=2 loops=1)
Output: a_2.attrelid, a_2.attname, a_2.atttypid,
a_2.attstattarget, a_2.attlen, a_2.attnum, a_2.attndims,
a_2.attcacheoff, a_2.atttypmod, a_2.attbyval, a_2.attstorage,
a_2.attalign, a_2.attnotnull, a_2.atthasdef, a_2.atthasmissing,
a_2.attidentity, a_2.attgenerated, a_2.attisdropped, a_2.attislocal,
a_2.attinhcount, a_2.attcollation, a_2.attacl, a_2.attoptions,
a_2.attfdwoptions, a_2.attmissingval
Index Cond: ((a_2.attrelid = r_4.oid) AND
(a_2.attnum > 0))
Filter: (a_2.attnotnull AND (NOT
a_2.attisdropped))
Buffers: shared hit=3
->
Index Scan using pg_constraint_conname_nsp_index on
pg_catalog.pg_constraint con (cost=0.28..8.30 rows=1 width=80)
(actual time=0.004..0.004 rows=0 loops=5)
Output: con.oid, con.conname, con.connamespace, con.contype,
con.condeferrable, con.condeferred, con.convalidated, con.conrelid,
con.contypid, con.conindid, con.conparentid, con.confrelid,
con.confupdtype, con.confdeltype, con.confmatchtype, con.conislocal,
con.coninhcount, con.connoinherit, con.conkey, con.confkey,
con.conpfeqop, con.conppeqop, con.conffeqop, con.conexclop, con.conbin
Index Cond: (con.conname = ("*SELECT* 1".constraint_name)::name)
Filter: (con.contype = 'f'::"char")
Rows Removed by Filter: 0
Buffers: shared hit=13
->
Index Scan using pg_class_oid_index on pg_catalog.pg_class c
(cost=0.28..1.48 rows=1 width=4) (actual time=0.007..0.007 rows=1
loops=2)
Output: c.oid, c.relname, c.relnamespace, c.reltype, c.reloftype,
c.relowner, c.relam, c.relfilenode, c.reltablespace, c.relpages,
c.reltuples, c.relallvisible, c.reltoastrelid, c.relhasindex,
c.relisshared, c.relpersistence, c.relkind, c.relnatts, c.relchecks,
c.relhasrules, c.relhastriggers, c.relhassubclass, c.relrowsecurity,
c.relforcerowsecurity, c.relispopulated, c.relreplident,
c.relispartition, c.relrewrite, c.relfrozenxid, c.relminmxid,
c.relacl, c.reloptions, c.relpartbound
Index Cond: (c.oid = con.conrelid)
Filter: (pg_has_role(c.relowner, 'USAGE'::text) OR
has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE,
REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid,
'INSERT, UPDATE, REFERENCES'::text))
Buffers: shared hit=6
-> Seq Scan
on pg_catalog.pg_namespace ncon (cost=0.00..1.06 rows=6 width=4)
(actual time=0.002..0.002 rows=6 loops=2)
Output:
ncon.oid, ncon.nspname, ncon.nspowner, ncon.nspacl
Buffers:
shared hit=2
-> Index Scan using
pg_depend_depender_index on pg_catalog.pg_depend d1 (cost=0.29..1.97
rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=2)
Output:
d1.classid, d1.objid, d1.objsubid, d1.refclassid, d1.refobjid,
d1.refobjsubid, d1.deptype
Index Cond:
((d1.classid = '2606'::oid) AND (d1.objid = con.oid))
Filter:
((d1.refclassid = '1259'::oid) AND (d1.refobjsubid = 0))
Rows Removed
by Filter: 2
Buffers: shared hit=6
-> Index Scan using
pg_depend_depender_index on pg_catalog.pg_depend d2 (cost=0.29..1.85
rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=2)
Output: d2.classid,
d2.objid, d2.objsubid, d2.refclassid, d2.refobjid, d2.refobjsubid,
d2.deptype
Index Cond:
((d2.classid = '1259'::oid) AND (d2.objid = d1.refobjid) AND
(d2.objsubid = 0))
Filter:
((d2.refclassid = '2606'::oid) AND (d2.deptype = 'i'::"char"))
Buffers: shared hit=6
-> Index Scan using
pg_constraint_conrelid_contypid_conname_index on
pg_catalog.pg_constraint pkc (cost=0.28..0.64 rows=1 width=76)
(actual time=0.007..0.007 rows=1 loops=2)
Output: pkc.oid,
pkc.conname, pkc.connamespace, pkc.contype, pkc.condeferrable,
pkc.condeferred, pkc.convalidated, pkc.conrelid, pkc.contypid,
pkc.conindid, pkc.conparentid, pkc.confrelid, pkc.confupdtype,
pkc.confdeltype, pkc.confmatchtype, pkc.conislocal, pkc.coninhcount,
pkc.connoinherit, pkc.conkey, pkc.confkey, pkc.conpfeqop,
pkc.conppeqop, pkc.conffeqop, pkc.conexclop, pkc.conbin
Index Cond: (pkc.conrelid
= con.confrelid)
Filter: (pkc.contype = ANY
('{p,u}'::"char"[]))
Rows Removed by Filter: 2
Buffers: shared hit=8
-> Append (cost=417.66..2272.67 rows=733
width=128) (actual time=0.011..9.830 rows=4287 loops=1672)
Buffers: shared hit=1913319
-> Subquery Scan on "*SELECT* 1_1"
(cost=417.66..500.03 rows=175 width=128) (actual time=0.010..1.720
rows=1707 loops=1672)
Output: "*SELECT* 1_1".table_name,
"*SELECT* 1_1".constraint_name
Buffers: shared hit=87220
-> Hash Join (cost=417.66..498.28
rows=175 width=512) (actual time=0.010..1.584 rows=1707 loops=1672)
Output:
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(c_5.conname)::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(r_5.relname)::information_schema.sql_identifier,
NULL::information_schema.character_data,
NULL::information_schema.yes_or_no,
NULL::information_schema.yes_or_no, NULL::information_schema.yes_or_no
Inner Unique: true
Hash Cond: (c_5.connamespace = nc_4.oid)
Buffers: shared hit=87220
-> Hash Join
(cost=416.52..496.36 rows=175 width=132) (actual time=0.008..1.190
rows=1707 loops=1672)
Output: r_5.relname,
c_5.conname, c_5.connamespace
Inner Unique: true
Hash Cond:
(r_5.relnamespace = nr_5.oid)
Buffers: shared hit=87219
-> Hash Join
(cost=415.40..494.06 rows=263 width=136) (actual time=0.007..0.869
rows=1707 loops=1672)
Output: c_5.conname,
c_5.connamespace, r_5.relname, r_5.relnamespace
Inner Unique: true
Hash Cond:
(c_5.conrelid = r_5.oid)
Buffers: shared hit=87218
-> Seq Scan on
pg_catalog.pg_constraint c_5 (cost=0.00..74.03 rows=1762 width=72)
(actual time=0.004..0.379 rows=1709 loops=1672)
Output:
c_5.oid, c_5.conname, c_5.connamespace, c_5.contype,
c_5.condeferrable, c_5.condeferred, c_5.convalidated, c_5.conrelid,
c_5.contypid, c_5.conindid, c_5.conparentid, c_5.confrelid,
c_5.confupdtype, c_5.confdeltype, c_5.confmatchtype, c_5.conislocal,
c_5.coninhcount, c_5.connoinherit, c_5.conkey, c_5.confkey,
c_5.conpfeqop, c_5.conppeqop, c_5.conffeqop, c_5.conexclop, c_5.conbin
Filter:
(c_5.contype <> ALL ('{t,x}'::"char"[]))
Buffers:
shared hit=86944
-> Hash
(cost=409.45..409.45 rows=476 width=72) (actual time=1.244..1.245
rows=694 loops=1)
Output:
r_5.relname, r_5.relnamespace, r_5.oid
Buckets: 1024
Batches: 1 Memory Usage: 79kB
Buffers: shared hit=274
-> Seq Scan
on pg_catalog.pg_class r_5 (cost=0.00..409.45 rows=476 width=72)
(actual time=0.011..1.118 rows=694 loops=1)
Output:
r_5.relname, r_5.relnamespace, r_5.oid
Filter:
((r_5.relkind = ANY ('{r,p}'::"char"[])) AND
(pg_has_role(r_5.relowner, 'USAGE'::text) OR
has_table_privilege(r_5.oid, 'INSERT, UPDATE, DELETE, TRUNCATE,
REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r_5.oid,
'INSERT, UPDATE, REFERENCES'::text)))
Rows
Removed by Filter: 2559
Buffers:
shared hit=274
-> Hash (cost=1.07..1.07
rows=4 width=4) (actual time=0.019..0.019 rows=7 loops=1)
Output: nr_5.oid
Buckets: 1024
Batches: 1 Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on
pg_catalog.pg_namespace nr_5 (cost=0.00..1.07 rows=4 width=4) (actual
time=0.004..0.008 rows=7 loops=1)
Output: nr_5.oid
Filter: (NOT
pg_is_other_temp_schema(nr_5.oid))
Rows Removed
by Filter: 2
Buffers: shared hit=1
-> Hash (cost=1.06..1.06
rows=6 width=4) (actual time=0.015..0.016 rows=9 loops=1)
Output: nc_4.oid
Buckets: 1024 Batches: 1
Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on
pg_catalog.pg_namespace nc_4 (cost=0.00..1.06 rows=6 width=4) (actual
time=0.010..0.011 rows=9 loops=1)
Output: nc_4.oid
Buffers: shared hit=1
-> Subquery Scan on "*SELECT* 2_1"
(cost=416.52..1768.97 rows=558 width=128) (actual time=0.010..7.839
rows=2580 loops=1672)
Output: "*SELECT* 2_1".table_name,
"*SELECT* 2_1".constraint_name
Buffers: shared hit=1826099
-> Hash Join (cost=416.52..1763.39
rows=558 width=512) (actual time=0.009..7.622 rows=2580 loops=1672)
Output:
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier, (((((((nr_6.oid)::text ||
'_'::text) || (r_6.oid)::text) || '_'::text) || (a_3.attnum)::text) ||
'_not_null'::text))::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(r_6.relname)::information_schema.sql_identifier,
NULL::information_schema.character_data,
NULL::information_schema.yes_or_no,
NULL::information_schema.yes_or_no, NULL::information_schema.yes_or_no
Inner Unique: true
Hash Cond: (r_6.relnamespace = nr_6.oid)
Buffers: shared hit=1826099
-> Hash Join
(cost=415.40..1741.77 rows=837 width=74) (actual time=0.004..5.410
rows=2580 loops=1672)
Output: r_6.oid,
r_6.relname, r_6.relnamespace, a_3.attnum
Inner Unique: true
Hash Cond: (a_3.attrelid = r_6.oid)
Buffers: shared hit=1826098
-> Seq Scan on
pg_catalog.pg_attribute a_3 (cost=0.00..1311.64 rows=5606 width=6)
(actual time=0.002..4.792 rows=2598 loops=1672)
Output:
a_3.attrelid, a_3.attname, a_3.atttypid, a_3.attstattarget,
a_3.attlen, a_3.attnum, a_3.attndims, a_3.attcacheoff, a_3.atttypmod,
a_3.attbyval, a_3.attstorage, a_3.attalign, a_3.attnotnull,
a_3.atthasdef, a_3.atthasmissing, a_3.attidentity, a_3.attgenerated,
a_3.attisdropped, a_3.attislocal, a_3.attinhcount, a_3.attcollation,
a_3.attacl, a_3.attoptions, a_3.attfdwoptions, a_3.attmissingval
Filter:
(a_3.attnotnull AND (NOT a_3.attisdropped) AND (a_3.attnum > 0))
Rows Removed by Filter: 15396
Buffers: shared hit=1825824
-> Hash
(cost=409.45..409.45 rows=476 width=72) (actual time=1.227..1.227
rows=694 loops=1)
Output: r_6.oid,
r_6.relname, r_6.relnamespace
Buckets: 1024
Batches: 1 Memory Usage: 79kB
Buffers: shared hit=274
-> Seq Scan on
pg_catalog.pg_class r_6 (cost=0.00..409.45 rows=476 width=72) (actual
time=0.011..1.087 rows=694 loops=1)
Output:
r_6.oid, r_6.relname, r_6.relnamespace
Filter:
((r_6.relkind = ANY ('{r,p}'::"char"[])) AND
(pg_has_role(r_6.relowner, 'USAGE'::text) OR
has_table_privilege(r_6.oid, 'INSERT, UPDATE, DELETE, TRUNCATE,
REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r_6.oid,
'INSERT, UPDATE, REFERENCES'::text)))
Rows Removed
by Filter: 2559
Buffers: shared hit=274
-> Hash (cost=1.07..1.07
rows=4 width=4) (actual time=0.015..0.015 rows=7 loops=1)
Output: nr_6.oid
Buckets: 1024 Batches: 1
Memory Usage: 9kB
Buffers: shared hit=1
-> Seq Scan on
pg_catalog.pg_namespace nr_6 (cost=0.00..1.07 rows=4 width=4) (actual
time=0.008..0.011 rows=7 loops=1)
Output: nr_6.oid
Filter: (NOT
pg_is_other_temp_schema(nr_6.oid))
Rows Removed by Filter: 2
Buffers: shared hit=1
-> Index Scan using pg_attribute_relid_attnum_index on
pg_catalog.pg_attribute a (cost=0.29..0.33 rows=1 width=70) (actual
time=0.019..0.019 rows=1 loops=2)
Output: a.attrelid, a.attname, a.atttypid,
a.attstattarget, a.attlen, a.attnum, a.attndims, a.attcacheoff,
a.atttypmod, a.attbyval, a.attstorage, a.attalign, a.attnotnull,
a.atthasdef, a.atthasmissing, a.attidentity, a.attgenerated,
a.attisdropped, a.attislocal, a.attinhcount, a.attcollation, a.attacl,
a.attoptions, a.attfdwoptions, a.attmissingval
Index Cond: ((a.attrelid = r_2.oid) AND (a.attnum =
((information_schema._pg_expandarray(c_3.conkey))).x))
Filter: ((NOT a.attisdropped) AND
(pg_has_role(r_2.relowner, 'USAGE'::text) OR
has_column_privilege(r_2.oid, a.attnum, 'SELECT, INSERT, UPDATE,
REFERENCES'::text)))
Buffers: shared hit=6
-> Index Scan using pg_attribute_relid_attnum_index on
pg_catalog.pg_attribute a_1 (cost=0.29..0.33 rows=1 width=70) (actual
time=0.007..0.007 rows=1 loops=2)
Output: a_1.attrelid, a_1.attname, a_1.atttypid,
a_1.attstattarget, a_1.attlen, a_1.attnum, a_1.attndims,
a_1.attcacheoff, a_1.atttypmod, a_1.attbyval, a_1.attstorage,
a_1.attalign, a_1.attnotnull, a_1.atthasdef, a_1.atthasmissing,
a_1.attidentity, a_1.attgenerated, a_1.attisdropped, a_1.attislocal,
a_1.attinhcount, a_1.attcollation, a_1.attacl, a_1.attoptions,
a_1.attfdwoptions, a_1.attmissingval
Index Cond: ((a_1.attrelid = r.oid) AND (a_1.attnum =
((information_schema._pg_expandarray(c_1.conkey))).x))
Filter: ((NOT a_1.attisdropped) AND (pg_has_role(r.relowner,
'USAGE'::text) OR has_column_privilege(r.oid, a_1.attnum, 'SELECT,
INSERT, UPDATE, REFERENCES'::text)))
Buffers: shared hit=6
Planning Time: 8.688 ms
Execution Time: 26311.005 ms
index scan
set enable_hashjoin = 0;
SELECT
FK.TABLE_NAME as "TABLE_NAME"
, CU.COLUMN_NAME as "COLUMN_NAME"
, PK.TABLE_NAME as "REFERENCED_TABLE_NAME"
, PT.COLUMN_NAME as "REFERENCED_COLUMN_NAME"
, C.CONSTRAINT_NAME as "CONSTRAINT_NAME"
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON
C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME
= CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME
, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE
lower(FK.TABLE_NAME)='secrole_condcollection'
Nested Loop (cost=1736.10..18890.44 rows=1 width=320) (actual
time=30.780..79.572 rows=2 loops=1)
Output: "*SELECT* 1".table_name,
(a.attname)::information_schema.sql_identifier, "*SELECT*
1_1".table_name, (a_1.attname)::information_schema.sql_identifier,
(con.conname)::information_schema.sql_identifier
Inner Unique: true
Buffers: shared hit=9018
-> Nested Loop (cost=1735.81..18890.10 rows=1 width=296) (actual
time=30.752..79.531 rows=2 loops=1)
Output: con.conname, "*SELECT* 1".table_name, "*SELECT*
1_1".table_name, a.attname, r_6.oid,
(information_schema._pg_expandarray(c_5.conkey)), r_6.relowner
Join Filter: (("*SELECT* 1_2".constraint_name)::name = c_5.conname)
Rows Removed by Join Filter: 3964
Buffers: shared hit=9012
-> Nested Loop (cost=1170.86..11411.63 rows=1 width=320)
(actual time=18.709..57.524 rows=2 loops=1)
Output: con.conname, "*SELECT* 1".table_name, "*SELECT*
1_1".table_name, a.attname, "*SELECT* 1_2".constraint_name
Join Filter: (("*SELECT* 1_1".table_name)::name =
("*SELECT* 1_2".table_name)::name)
Rows Removed by Join Filter: 1188
Buffers: shared hit=8684
-> Nested Loop (cost=1170.58..11238.29 rows=1
width=256) (actual time=16.937..45.450 rows=2 loops=1)
Output: con.conname, "*SELECT* 1".table_name,
"*SELECT* 1_1".table_name, a.attname
Inner Unique: true
Buffers: shared hit=2630
-> Nested Loop (cost=1170.30..11237.95 rows=1
width=232) (actual time=16.909..45.398 rows=2 loops=1)
Output: con.conname, "*SELECT*
1".table_name, "*SELECT* 1_1".table_name, r_4.oid,
(information_schema._pg_expandarray(c_3.conkey)), r_4.relowner
Join Filter: (con.conname = c_3.conname)
Rows Removed by Join Filter: 3964
Buffers: shared hit=2624
-> Nested Loop (cost=605.35..3759.48
rows=1 width=256) (actual time=5.769..23.698 rows=2 loops=1)
Output: con.conname, "*SELECT*
1".table_name, "*SELECT* 1".constraint_name, "*SELECT* 1_1".table_name
Join Filter: (pkc.conname = ("*SELECT*
1_1".constraint_name)::name)
Rows Removed by Join Filter: 8572
Buffers: shared hit=2296
-> Nested Loop (cost=5.71..933.65
rows=1 width=256) (actual time=1.324..2.731 rows=2 loops=1)
Output: con.conname,
pkc.conname, "*SELECT* 1".table_name, "*SELECT* 1".constraint_name
Inner Unique: true
Join Filter: (d2.refobjid = pkc.oid)
Buffers: shared hit=601
-> Nested Loop
(cost=5.43..933.00 rows=1 width=200) (actual time=1.315..2.713 rows=2
loops=1)
Output: con.conname,
con.confrelid, d2.refobjid, "*SELECT* 1".table_name, "*SELECT*
1".constraint_name
Buffers: shared hit=593
-> Nested Loop
(cost=5.15..931.14 rows=1 width=200) (actual time=1.305..2.687 rows=2
loops=1)
Output: con.conname,
con.confrelid, d1.refobjid, "*SELECT* 1".table_name, "*SELECT*
1".constraint_name
Buffers: shared hit=587
-> Nested Loop
(cost=4.86..929.16 rows=1 width=200) (actual time=1.292..2.662 rows=2
loops=1)
Output:
con.conname, con.oid, con.confrelid, "*SELECT* 1".table_name,
"*SELECT* 1".constraint_name
Inner Unique: true
Join Filter:
(con.connamespace = ncon.oid)
Rows Removed
by Join Filter: 10
Buffers: shared hit=581
-> Nested
Loop (cost=4.86..928.02 rows=1 width=204) (actual time=1.288..2.652
rows=2 loops=1)
Output:
con.conname, con.connamespace, con.oid, con.confrelid, "*SELECT*
1".table_name, "*SELECT* 1".constraint_name
Inner Unique: true
Buffers:
shared hit=579
->
Nested Loop (cost=4.58..925.06 rows=2 width=208) (actual
time=1.273..2.626 rows=2 loops=1)
Output: con.conname, con.connamespace, con.conrelid, con.oid,
con.confrelid, "*SELECT* 1".table_name, "*SELECT* 1".constraint_name
Buffers: shared hit=573
->
Append (cost=4.30..900.14 rows=3 width=128) (actual
time=1.250..2.586 rows=5 loops=1)
Buffers: shared hit=560
-> Subquery Scan on "*SELECT* 1" (cost=4.30..449.91 rows=1
width=128) (actual time=1.249..1.283 rows=3 loops=1)
Output: "*SELECT* 1".table_name, "*SELECT* 1".constraint_name
Buffers: shared hit=282
-> Nested Loop (cost=4.30..449.90 rows=1 width=512) (actual
time=1.249..1.280 rows=3 loops=1)
Output: NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(c_1.conname)::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(r.relname)::information_schema.sql_identifier,
NULL::information_schema.character_data,
NULL::information_schema.yes_or_no,
NULL::information_schema.yes_or_no, NULL::information_schema.yes_or_no
Inner Unique: true
Join Filter: (c_1.connamespace = nc.oid)
Rows Removed by Join Filter: 15
Buffers: shared hit=282
-> Nested Loop (cost=4.30..448.76 rows=1 width=132)
(actual time=1.242..1.257 rows=3 loops=1)
Output: r.relname, c_1.conname, c_1.connamespace
Buffers: shared hit=279
-> Nested Loop (cost=0.00..434.55 rows=1
width=68) (actual time=1.217..1.225 rows=1 loops=1)
Output: r.relname, r.oid
Join Filter: (nr.oid = r.relnamespace)
Rows Removed by Join Filter: 6
Buffers: shared hit=275
-> Seq Scan on pg_catalog.pg_namespace nr
(cost=0.00..1.07 rows=4 width=4) (actual time=0.010..0.017 rows=7
loops=1)
Output: nr.oid, nr.nspname,
nr.nspowner, nr.nspacl
Filter: (NOT
pg_is_other_temp_schema(nr.oid))
Rows Removed by Filter: 2
Buffers: shared hit=1
-> Materialize (cost=0.00..433.36 rows=2
width=72) (actual time=0.004..0.172 rows=1 loops=7)
Output: r.relname, r.relnamespace,
r.oid
Buffers: shared hit=274
-> Seq Scan on pg_catalog.pg_class r
(cost=0.00..433.35 rows=2 width=72) (actual time=0.028..1.198 rows=1
loops=1)
Output: r.relname,
r.relnamespace, r.oid
Filter: ((r.relkind = ANY
('{r,p}'::"char"[])) AND
(lower(((r.relname)::information_schema.sql_identifier)::text) =
'secrole_condcollection'::text) AND (pg_has_role(r.relowner,
'USAGE'::text) OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE,
TRUNCATE, REFERENCES, TRIGGER'::text) OR
has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES'::text)))
Rows Removed by Filter: 3252
Buffers: shared hit=274
-> Bitmap Heap Scan on pg_catalog.pg_constraint
c_1 (cost=4.30..14.18 rows=3 width=72) (actual time=0.020..0.026
rows=3 loops=1)
Output: c_1.oid, c_1.conname,
c_1.connamespace, c_1.contype, c_1.condeferrable, c_1.condeferred,
c_1.convalidated, c_1.conrelid, c_1.contypid, c_1.conindid,
c_1.conparentid, c_1.confrelid, c_1.confupdtype, c_1.confdeltype,
c_1.confmatchtype, c_1.conislocal, c_1.coninhcount, c_1.connoinherit,
c_1.conkey, c_1.confkey, c_1.conpfeqop, c_1.conppeqop, c_1.conffeqop,
c_1.conexclop, c_1.conbin
Recheck Cond: (c_1.conrelid = r.oid)
Filter: (c_1.contype <> ALL
('{t,x}'::"char"[]))
Heap Blocks: exact=2
Buffers: shared hit=4
-> Bitmap Index Scan on
pg_constraint_conrelid_contypid_conname_index (cost=0.00..4.30 rows=3
width=0) (actual time=0.016..0.016 rows=3 loops=1)
Index Cond: (c_1.conrelid = r.oid)
Buffers: shared hit=2
-> Seq Scan on pg_catalog.pg_namespace nc
(cost=0.00..1.06 rows=6 width=4) (actual time=0.002..0.003 rows=6
loops=3)
Output: nc.oid, nc.nspname, nc.nspowner,
nc.nspacl
Buffers: shared hit=3
-> Subquery Scan on "*SELECT* 2" (cost=0.29..450.21 rows=2
width=128) (actual time=1.294..1.300 rows=2 loops=1)
Output: "*SELECT* 2".table_name, "*SELECT* 2".constraint_name
Buffers: shared hit=278
-> Nested Loop (cost=0.29..450.19 rows=2 width=512) (actual
time=1.294..1.299 rows=2 loops=1)
Output: NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier, (((((((nr_1.oid)::text ||
'_'::text) || (r_1.oid)::text) || '_'::text) || (a_2.attnum)::text) ||
'_not_null'::text))::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(r_1.relname)::information_schema.sql_identifier,
NULL::information_schema.character_data,
NULL::information_schema.yes_or_no,
NULL::information_schema.yes_or_no, NULL::information_schema.yes_or_no
Buffers: shared hit=278
-> Nested Loop (cost=0.00..434.55 rows=1 width=72)
(actual time=1.273..1.276 rows=1 loops=1)
Output: nr_1.oid, r_1.oid, r_1.relname
Join Filter: (nr_1.oid = r_1.relnamespace)
Rows Removed by Join Filter: 6
Buffers: shared hit=275
-> Seq Scan on pg_catalog.pg_namespace nr_1
(cost=0.00..1.07 rows=4 width=4) (actual time=0.013..0.017 rows=7
loops=1)
Output: nr_1.oid, nr_1.nspname,
nr_1.nspowner, nr_1.nspacl
Filter: (NOT
pg_is_other_temp_schema(nr_1.oid))
Rows Removed by Filter: 2
Buffers: shared hit=1
-> Materialize (cost=0.00..433.36 rows=2
width=72) (actual time=0.006..0.179 rows=1 loops=7)
Output: r_1.oid, r_1.relname,
r_1.relnamespace
Buffers: shared hit=274
-> Seq Scan on pg_catalog.pg_class r_1
(cost=0.00..433.35 rows=2 width=72) (actual time=0.030..1.245 rows=1
loops=1)
Output: r_1.oid, r_1.relname,
r_1.relnamespace
Filter: ((r_1.relkind = ANY
('{r,p}'::"char"[])) AND
(lower(((r_1.relname)::information_schema.sql_identifier)::text) =
'secrole_condcollection'::text) AND (pg_has_role(r_1.relowner,
'USAGE'::text) OR has_table_privilege(r_1.oid, 'INSERT, UPDATE,
DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR
has_any_column_privilege(r_1.oid, 'INSERT, UPDATE,
REFERENCES'::text)))
Rows Removed by Filter: 3252
Buffers: shared hit=274
-> Index Scan using pg_attribute_relid_attnum_index on
pg_catalog.pg_attribute a_2 (cost=0.29..15.56 rows=2 width=6) (actual
time=0.015..0.016 rows=2 loops=1)
Output: a_2.attrelid, a_2.attname, a_2.atttypid,
a_2.attstattarget, a_2.attlen, a_2.attnum, a_2.attndims,
a_2.attcacheoff, a_2.atttypmod, a_2.attbyval, a_2.attstorage,
a_2.attalign, a_2.attnotnull, a_2.atthasdef, a_2.atthasmissing,
a_2.attidentity, a_2.attgenerated, a_2.attisdropped, a_2.attislocal,
a_2.attinhcount, a_2.attcollation, a_2.attacl, a_2.attoptions,
a_2.attfdwoptions, a_2.attmissingval
Index Cond: ((a_2.attrelid = r_1.oid) AND
(a_2.attnum > 0))
Filter: (a_2.attnotnull AND (NOT
a_2.attisdropped))
Buffers: shared hit=3
->
Index Scan using pg_constraint_conname_nsp_index on
pg_catalog.pg_constraint con (cost=0.28..8.30 rows=1 width=80)
(actual time=0.006..0.007 rows=0 loops=5)
Output: con.oid, con.conname, con.connamespace, con.contype,
con.condeferrable, con.condeferred, con.convalidated, con.conrelid,
con.contypid, con.conindid, con.conparentid, con.confrelid,
con.confupdtype, con.confdeltype, con.confmatchtype, con.conislocal,
con.coninhcount, con.connoinherit, con.conkey, con.confkey,
con.conpfeqop, con.conppeqop, con.conffeqop, con.conexclop, con.conbin
Index Cond: (con.conname = ("*SELECT* 1".constraint_name)::name)
Filter: (con.contype = 'f'::"char")
Rows Removed by Filter: 0
Buffers: shared hit=13
->
Index Scan using pg_class_oid_index on pg_catalog.pg_class c
(cost=0.28..1.48 rows=1 width=4) (actual time=0.010..0.011 rows=1
loops=2)
Output: c.oid, c.relname, c.relnamespace, c.reltype, c.reloftype,
c.relowner, c.relam, c.relfilenode, c.reltablespace, c.relpages,
c.reltuples, c.relallvisible, c.reltoastrelid, c.relhasindex,
c.relisshared, c.relpersistence, c.relkind, c.relnatts, c.relchecks,
c.relhasrules, c.relhastriggers, c.relhassubclass, c.relrowsecurity,
c.relforcerowsecurity, c.relispopulated, c.relreplident,
c.relispartition, c.relrewrite, c.relfrozenxid, c.relminmxid,
c.relacl, c.reloptions, c.relpartbound
Index Cond: (c.oid = con.conrelid)
Filter: (pg_has_role(c.relowner, 'USAGE'::text) OR
has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE,
REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid,
'INSERT, UPDATE, REFERENCES'::text))
Buffers: shared hit=6
-> Seq Scan
on pg_catalog.pg_namespace ncon (cost=0.00..1.06 rows=6 width=4)
(actual time=0.002..0.002 rows=6 loops=2)
Output:
ncon.oid, ncon.nspname, ncon.nspowner, ncon.nspacl
Buffers:
shared hit=2
-> Index Scan using
pg_depend_depender_index on pg_catalog.pg_depend d1 (cost=0.29..1.97
rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=2)
Output:
d1.classid, d1.objid, d1.objsubid, d1.refclassid, d1.refobjid,
d1.refobjsubid, d1.deptype
Index Cond:
((d1.classid = '2606'::oid) AND (d1.objid = con.oid))
Filter:
((d1.refclassid = '1259'::oid) AND (d1.refobjsubid = 0))
Rows Removed
by Filter: 2
Buffers: shared hit=6
-> Index Scan using
pg_depend_depender_index on pg_catalog.pg_depend d2 (cost=0.29..1.85
rows=1 width=8) (actual time=0.006..0.010 rows=1 loops=2)
Output: d2.classid,
d2.objid, d2.objsubid, d2.refclassid, d2.refobjid, d2.refobjsubid,
d2.deptype
Index Cond:
((d2.classid = '1259'::oid) AND (d2.objid = d1.refobjid) AND
(d2.objsubid = 0))
Filter:
((d2.refclassid = '2606'::oid) AND (d2.deptype = 'i'::"char"))
Buffers: shared hit=6
-> Index Scan using
pg_constraint_conrelid_contypid_conname_index on
pg_catalog.pg_constraint pkc (cost=0.28..0.64 rows=1 width=76)
(actual time=0.007..0.007 rows=1 loops=2)
Output: pkc.oid,
pkc.conname, pkc.connamespace, pkc.contype, pkc.condeferrable,
pkc.condeferred, pkc.convalidated, pkc.conrelid, pkc.contypid,
pkc.conindid, pkc.conparentid, pkc.confrelid, pkc.confupdtype,
pkc.confdeltype, pkc.confmatchtype, pkc.conislocal, pkc.coninhcount,
pkc.connoinherit, pkc.conkey, pkc.confkey, pkc.conpfeqop,
pkc.conppeqop, pkc.conffeqop, pkc.conexclop, pkc.conbin
Index Cond: (pkc.conrelid
= con.confrelid)
Filter: (pkc.contype = ANY
('{p,u}'::"char"[]))
Rows Removed by Filter: 2
Buffers: shared hit=8
-> Append (cost=599.64..2816.66
rows=733 width=128) (actual time=1.033..10.237 rows=4287 loops=2)
Buffers: shared hit=1695
-> Subquery Scan on "*SELECT*
1_1" (cost=599.64..645.39 rows=175 width=128) (actual
time=1.032..3.966 rows=1707 loops=2)
Output: "*SELECT*
1_1".table_name, "*SELECT* 1_1".constraint_name
Buffers: shared hit=328
-> Nested Loop
(cost=599.64..643.64 rows=175 width=512) (actual time=1.032..3.842
rows=1707 loops=2)
Output:
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(c_2.conname)::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(r_2.relname)::information_schema.sql_identifier,
NULL::information_schema.character_data,
NULL::information_schema.yes_or_no,
NULL::information_schema.yes_or_no, NULL::information_schema.yes_or_no
Inner Unique: true
Join Filter:
(c_2.connamespace = nc_1.oid)
Rows Removed by Join
Filter: 8624
Buffers: shared hit=328
-> Nested Loop
(cost=599.64..628.68 rows=175 width=132) (actual time=1.028..2.578
rows=1707 loops=2)
Output:
r_2.relname, c_2.conname, c_2.connamespace
Inner Unique: true
Join Filter:
(r_2.relnamespace = nr_2.oid)
Rows Removed
by Join Filter: 5210
Buffers: shared hit=327
-> Merge Join
(cost=599.64..613.40 rows=263 width=136) (actual time=1.019..1.684
rows=1707 loops=2)
Output:
c_2.conname, c_2.connamespace, r_2.relname, r_2.relnamespace
Inner Unique: true
Merge
Cond: (c_2.conrelid = r_2.oid)
Buffers:
shared hit=326
-> Sort
(cost=169.02..173.43 rows=1762 width=72) (actual time=0.473..0.622
rows=1709 loops=2)
Output: c_2.conname, c_2.connamespace, c_2.conrelid
Sort Key: c_2.conrelid
Sort Method: quicksort Memory: 289kB
Buffers: shared hit=52
->
Seq Scan on pg_catalog.pg_constraint c_2 (cost=0.00..74.03 rows=1762
width=72) (actual time=0.005..0.469 rows=1709 loops=1)
Output: c_2.conname, c_2.connamespace, c_2.conrelid
Filter: (c_2.contype <> ALL ('{t,x}'::"char"[]))
Buffers: shared hit=52
-> Sort
(cost=430.62..431.81 rows=476 width=72) (actual time=0.533..0.604
rows=694 loops=2)
Output: r_2.relname, r_2.relnamespace, r_2.oid
Sort Key: r_2.oid
Sort Method: quicksort Memory: 122kB
Buffers: shared hit=274
->
Seq Scan on pg_catalog.pg_class r_2 (cost=0.00..409.45 rows=476
width=72) (actual time=0.007..0.882 rows=694 loops=1)
Output: r_2.relname, r_2.relnamespace, r_2.oid
Filter: ((r_2.relkind = ANY ('{r,p}'::"char"[])) AND
(pg_has_role(r_2.relowner, 'USAGE'::text) OR
has_table_privilege(r_2.oid, 'INSERT, UPDATE, DELETE, TRUNCATE,
REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r_2.oid,
'INSERT, UPDATE, REFERENCES'::text)))
Rows Removed by Filter: 2559
Buffers: shared hit=274
->
Materialize (cost=0.00..1.09 rows=4 width=4) (actual
time=0.000..0.000 rows=4 loops=3414)
Output: nr_2.oid
Buffers:
shared hit=1
-> Seq
Scan on pg_catalog.pg_namespace nr_2 (cost=0.00..1.07 rows=4 width=4)
(actual time=0.009..0.015 rows=7 loops=1)
Output: nr_2.oid
Filter: (NOT pg_is_other_temp_schema(nr_2.oid))
Rows Removed by Filter: 2
Buffers: shared hit=1
-> Materialize
(cost=0.00..1.09 rows=6 width=4) (actual time=0.000..0.000 rows=6
loops=3414)
Output: nc_1.oid
Buffers: shared hit=1
-> Seq Scan
on pg_catalog.pg_namespace nc_1 (cost=0.00..1.06 rows=6 width=4)
(actual time=0.003..0.004 rows=9 loops=1)
Output: nc_1.oid
Buffers:
shared hit=1
-> Subquery Scan on "*SELECT*
2_1" (cost=2110.11..2167.61 rows=558 width=128) (actual
time=3.730..6.052 rows=2580 loops=2)
Output: "*SELECT*
2_1".table_name, "*SELECT* 2_1".constraint_name
Buffers: shared hit=1367
-> Merge Join
(cost=2110.11..2162.03 rows=558 width=512) (actual time=3.729..5.866
rows=2580 loops=2)
Output:
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier, (((((((nr_3.oid)::text ||
'_'::text) || (r_3.oid)::text) || '_'::text) || (a_3.attnum)::text) ||
'_not_null'::text))::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(r_3.relname)::information_schema.sql_identifier,
NULL::information_schema.character_data,
NULL::information_schema.yes_or_no,
NULL::information_schema.yes_or_no, NULL::information_schema.yes_or_no
Merge Cond: (r_3.oid
= a_3.attrelid)
Buffers: shared hit=1367
-> Sort
(cost=449.42..450.21 rows=317 width=72) (actual time=0.900..0.965
rows=694 loops=2)
Output:
nr_3.oid, r_3.oid, r_3.relname
Sort Key: r_3.oid
Sort Method:
quicksort Memory: 122kB
Buffers: shared hit=275
-> Nested
Loop (cost=0.00..436.25 rows=317 width=72) (actual time=0.038..1.605
rows=694 loops=1)
Output:
nr_3.oid, r_3.oid, r_3.relname
Inner Unique: true
Join
Filter: (nr_3.oid = r_3.relnamespace)
Rows
Removed by Join Filter: 2013
Buffers:
shared hit=275
-> Seq
Scan on pg_catalog.pg_class r_3 (cost=0.00..409.45 rows=476 width=72)
(actual time=0.022..1.227 rows=694 loops=1)
Output: r_3.oid, r_3.relname, r_3.relnamespace, r_3.reltype,
r_3.reloftype, r_3.relowner, r_3.relam, r_3.relfilenode,
r_3.reltablespace, r_3.relpages, r_3.reltuples, r_3.relallvisible,
r_3.reltoastrelid, r_3.relhasindex, r_3.relisshared,
r_3.relpersistence, r_3.relkind, r_3.relnatts, r_3.relchecks,
r_3.relhasrules, r_3.relhastriggers, r_3.relhassubclass,
r_3.relrowsecurity, r_3.relforcerowsecurity, r_3.relispopulated,
r_3.relreplident, r_3.relispartition, r_3.relrewrite,
r_3.relfrozenxid, r_3.relminmxid, r_3.relacl, r_3.reloptions,
r_3.relpartbound
Filter: ((r_3.relkind = ANY ('{r,p}'::"char"[])) AND
(pg_has_role(r_3.relowner, 'USAGE'::text) OR
has_table_privilege(r_3.oid, 'INSERT, UPDATE, DELETE, TRUNCATE,
REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r_3.oid,
'INSERT, UPDATE, REFERENCES'::text)))
Rows Removed by Filter: 2559
Buffers: shared hit=274
->
Materialize (cost=0.00..1.09 rows=4 width=4) (actual
time=0.000..0.000 rows=4 loops=694)
Output: nr_3.oid
Buffers: shared hit=1
->
Seq Scan on pg_catalog.pg_namespace nr_3 (cost=0.00..1.07 rows=4
width=4) (actual time=0.006..0.009 rows=7 loops=1)
Output: nr_3.oid
Filter: (NOT pg_is_other_temp_schema(nr_3.oid))
Rows Removed by Filter: 2
Buffers: shared hit=1
-> Sort
(cost=1660.69..1674.70 rows=5606 width=6) (actual time=2.822..2.946
rows=2598 loops=2)
Output:
a_3.attnum, a_3.attrelid
Sort Key: a_3.attrelid
Sort Method:
quicksort Memory: 218kB
Buffers: shared hit=1092
-> Seq Scan
on pg_catalog.pg_attribute a_3 (cost=0.00..1311.64 rows=5606 width=6)
(actual time=0.008..5.054 rows=2598 loops=1)
Output:
a_3.attnum, a_3.attrelid
Filter:
(a_3.attnotnull AND (NOT a_3.attisdropped) AND (a_3.attnum > 0))
Rows
Removed by Filter: 15396
Buffers:
shared hit=1092
-> ProjectSet (cost=564.95..1875.97
rows=249000 width=341) (actual time=2.154..10.656 rows=1983 loops=2)
Output: r_4.oid, NULL::name,
r_4.relowner, NULL::name, NULL::name, NULL::oid, c_3.conname,
NULL::"char", NULL::oid, NULL::smallint[], NULL::oid,
information_schema._pg_expandarray(c_3.conkey)
Buffers: shared hit=328
-> Merge Join (cost=564.95..567.48
rows=249 width=95) (actual time=2.034..2.481 rows=1707 loops=2)
Output: c_3.conkey, r_4.oid,
r_4.relowner, c_3.conname
Inner Unique: true
Merge Cond: (c_3.connamespace = nc_2.oid)
Buffers: shared hit=328
-> Sort (cost=563.80..564.43
rows=249 width=99) (actual time=2.026..2.119 rows=1707 loops=2)
Output: r_4.oid,
r_4.relowner, c_3.conname, c_3.conkey, c_3.connamespace
Sort Key: c_3.connamespace
Sort Method: quicksort
Memory: 289kB
Buffers: shared hit=327
-> Nested Loop
(cost=516.77..553.89 rows=249 width=99) (actual time=2.080..3.571
rows=1707 loops=1)
Output: r_4.oid,
r_4.relowner, c_3.conname, c_3.conkey, c_3.connamespace
Inner Unique: true
Join Filter:
(r_4.relnamespace = nr_4.oid)
Rows Removed by Join
Filter: 5210
Buffers: shared hit=327
-> Merge Join
(cost=516.77..532.60 rows=374 width=103) (actual time=2.065..2.631
rows=1707 loops=1)
Output:
r_4.oid, r_4.relowner, r_4.relnamespace, c_3.conname, c_3.conkey,
c_3.connamespace
Merge Cond:
(r_4.oid = c_3.conrelid)
Buffers: shared hit=326
-> Sort
(cost=345.67..347.36 rows=677 width=12) (actual time=0.999..1.034
rows=694 loops=1)
Output:
r_4.oid, r_4.relowner, r_4.relnamespace
Sort Key: r_4.oid
Sort
Method: quicksort Memory: 57kB
Buffers:
shared hit=274
-> Seq
Scan on pg_catalog.pg_class r_4 (cost=0.00..313.84 rows=677 width=12)
(actual time=0.014..0.848 rows=694 loops=1)
Output: r_4.oid, r_4.relowner, r_4.relnamespace
Filter: (r_4.relkind = ANY ('{r,p}'::"char"[]))
Rows Removed by Filter: 2559
Buffers: shared hit=274
-> Sort
(cost=171.10..175.50 rows=1760 width=95) (actual time=1.056..1.164
rows=1707 loops=1)
Output:
c_3.conname, c_3.conkey, c_3.conrelid, c_3.connamespace
Sort
Key: c_3.conrelid
Sort
Method: quicksort Memory: 289kB
Buffers:
shared hit=52
-> Seq
Scan on pg_catalog.pg_constraint c_3 (cost=0.00..76.23 rows=1760
width=95) (actual time=0.009..0.519 rows=1707 loops=1)
Output: c_3.conname, c_3.conkey, c_3.conrelid, c_3.connamespace
Filter: (c_3.contype = ANY ('{p,u,f}'::"char"[]))
Rows Removed by Filter: 2
Buffers: shared hit=52
-> Materialize
(cost=0.00..1.09 rows=4 width=4) (actual time=0.000..0.000 rows=4
loops=1707)
Output: nr_4.oid
Buffers: shared hit=1
-> Seq Scan
on pg_catalog.pg_namespace nr_4 (cost=0.00..1.07 rows=4 width=4)
(actual time=0.007..0.011 rows=7 loops=1)
Output: nr_4.oid
Filter:
(NOT pg_is_other_temp_schema(nr_4.oid))
Rows
Removed by Filter: 2
Buffers:
shared hit=1
-> Sort (cost=1.14..1.15
rows=6 width=4) (actual time=0.006..0.008 rows=9 loops=2)
Output: nc_2.oid
Sort Key: nc_2.oid
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1
-> Seq Scan on
pg_catalog.pg_namespace nc_2 (cost=0.00..1.06 rows=6 width=4) (actual
time=0.006..0.007 rows=9 loops=1)
Output: nc_2.oid
Buffers: shared hit=1
-> Index Scan using
pg_attribute_relid_attnum_index on pg_catalog.pg_attribute a
(cost=0.29..0.33 rows=1 width=70) (actual time=0.020..0.020 rows=1
loops=2)
Output: a.attrelid, a.attname, a.atttypid,
a.attstattarget, a.attlen, a.attnum, a.attndims, a.attcacheoff,
a.atttypmod, a.attbyval, a.attstorage, a.attalign, a.attnotnull,
a.atthasdef, a.atthasmissing, a.attidentity, a.attgenerated,
a.attisdropped, a.attislocal, a.attinhcount, a.attcollation, a.attacl,
a.attoptions, a.attfdwoptions, a.attmissingval
Index Cond: ((a.attrelid = r_4.oid) AND
(a.attnum = ((information_schema._pg_expandarray(c_3.conkey))).x))
Filter: ((NOT a.attisdropped) AND
(pg_has_role(r_4.relowner, 'USAGE'::text) OR
has_column_privilege(r_4.oid, a.attnum, 'SELECT, INSERT, UPDATE,
REFERENCES'::text)))
Buffers: shared hit=6
-> Subquery Scan on "*SELECT* 1_2" (cost=0.28..173.32
rows=1 width=128) (actual time=0.040..5.978 rows=595 loops=2)
Output: "*SELECT* 1_2".constraint_name, "*SELECT*
1_2".table_name
Buffers: shared hit=6054
-> Nested Loop (cost=0.28..173.31 rows=1
width=512) (actual time=0.040..5.914 rows=595 loops=2)
Output:
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(c_4.conname)::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
NULL::information_schema.sql_identifier,
(r_5.relname)::information_schema.sql_identifier,
NULL::information_schema.character_data,
NULL::information_schema.yes_or_no,
NULL::information_schema.yes_or_no, NULL::information_schema.yes_or_no
Inner Unique: true
Join Filter: (r_5.relnamespace = nr_5.oid)
Rows Removed by Join Filter: 1836
Buffers: shared hit=6054
-> Nested Loop (cost=0.28..172.19 rows=1
width=132) (actual time=0.031..3.784 rows=595 loops=2)
Output: c_4.conname, r_5.relname,
r_5.relnamespace
Inner Unique: true
Join Filter: (c_4.connamespace = nc_3.oid)
Rows Removed by Join Filter: 3026
Buffers: shared hit=4864
-> Nested Loop (cost=0.28..171.05
rows=1 width=136) (actual time=0.024..1.976 rows=595 loops=2)
Output: c_4.conname,
c_4.connamespace, r_5.relname, r_5.relnamespace
Inner Unique: true
Buffers: shared hit=3674
-> Seq Scan on
pg_catalog.pg_constraint c_4 (cost=0.00..96.05 rows=9 width=72)
(actual time=0.012..0.489 rows=595 loops=2)
Output: c_4.oid,
c_4.conname, c_4.connamespace, c_4.contype, c_4.condeferrable,
c_4.condeferred, c_4.convalidated, c_4.conrelid, c_4.contypid,
c_4.conindid, c_4.conparentid, c_4.confrelid, c_4.confupdtype,
c_4.confdeltype, c_4.confmatchtype, c_4.conislocal, c_4.coninhcount,
c_4.connoinherit, c_4.conkey, c_4.confkey, c_4.conpfeqop,
c_4.conppeqop, c_4.conffeqop, c_4.conexclop, c_4.conbin
Filter: ((c_4.contype <>
ALL ('{t,x}'::"char"[])) AND ((CASE c_4.contype WHEN 'c'::"char" THEN
'CHECK'::text WHEN 'f'::"char" THEN 'FOREIGN KEY'::text WHEN
'p'::"char" THEN 'PRIMARY KEY'::text WHEN 'u'::"char" THEN
'UNIQUE'::text ELSE NULL::text END)::text = 'PRIMARY KEY'::text))
Rows Removed by Filter: 1114
Buffers: shared hit=104
-> Index Scan using
pg_class_oid_index on pg_catalog.pg_class r_5 (cost=0.28..8.33 rows=1
width=72) (actual time=0.002..0.002 rows=1 loops=1190)
Output: r_5.oid,
r_5.relname, r_5.relnamespace, r_5.reltype, r_5.reloftype,
r_5.relowner, r_5.relam, r_5.relfilenode, r_5.reltablespace,
r_5.relpages, r_5.reltuples, r_5.relallvisible, r_5.reltoastrelid,
r_5.relhasindex, r_5.relisshared, r_5.relpersistence, r_5.relkind,
r_5.relnatts, r_5.relchecks, r_5.relhasrules, r_5.relhastriggers,
r_5.relhassubclass, r_5.relrowsecurity, r_5.relforcerowsecurity,
r_5.relispopulated, r_5.relreplident, r_5.relispartition,
r_5.relrewrite, r_5.relfrozenxid, r_5.relminmxid, r_5.relacl,
r_5.reloptions, r_5.relpartbound
Index Cond: (r_5.oid = c_4.conrelid)
Filter: ((r_5.relkind =
ANY ('{r,p}'::"char"[])) AND (pg_has_role(r_5.relowner, 'USAGE'::text)
OR has_table_privilege(r_5.oid, 'INSERT, UPDATE, DELETE, TRUNCATE,
REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r_5.oid,
'INSERT, UPDATE, REFERENCES'::text)))
Buffers: shared hit=3570
-> Seq Scan on
pg_catalog.pg_namespace nc_3 (cost=0.00..1.06 rows=6 width=4) (actual
time=0.000..0.001 rows=6 loops=1190)
Output: nc_3.oid, nc_3.nspname,
nc_3.nspowner, nc_3.nspacl
Buffers: shared hit=1190
-> Seq Scan on pg_catalog.pg_namespace nr_5
(cost=0.00..1.07 rows=4 width=4) (actual time=0.001..0.002 rows=4
loops=1190)
Output: nr_5.oid, nr_5.nspname,
nr_5.nspowner, nr_5.nspacl
Filter: (NOT pg_is_other_temp_schema(nr_5.oid))
Rows Removed by Filter: 2
Buffers: shared hit=1190
-> ProjectSet (cost=564.95..1875.97 rows=249000 width=341)
(actual time=2.653..10.818 rows=1983 loops=2)
Output: r_6.oid, NULL::name, r_6.relowner, NULL::name,
NULL::name, NULL::oid, c_5.conname, NULL::"char", NULL::oid,
NULL::smallint[], NULL::oid,
information_schema._pg_expandarray(c_5.conkey)
Buffers: shared hit=328
-> Merge Join (cost=564.95..567.48 rows=249 width=95)
(actual time=2.571..3.014 rows=1707 loops=2)
Output: c_5.conkey, r_6.oid, r_6.relowner, c_5.conname
Inner Unique: true
Merge Cond: (c_5.connamespace = nc_4.oid)
Buffers: shared hit=328
-> Sort (cost=563.80..564.43 rows=249 width=99)
(actual time=2.557..2.654 rows=1707 loops=2)
Output: r_6.oid, r_6.relowner, c_5.conname,
c_5.conkey, c_5.connamespace
Sort Key: c_5.connamespace
Sort Method: quicksort Memory: 289kB
Buffers: shared hit=327
-> Nested Loop (cost=516.77..553.89
rows=249 width=99) (actual time=2.335..4.616 rows=1707 loops=1)
Output: r_6.oid, r_6.relowner,
c_5.conname, c_5.conkey, c_5.connamespace
Inner Unique: true
Join Filter: (r_6.relnamespace = nr_6.oid)
Rows Removed by Join Filter: 5210
Buffers: shared hit=327
-> Merge Join (cost=516.77..532.60
rows=374 width=103) (actual time=2.320..2.962 rows=1707 loops=1)
Output: r_6.oid, r_6.relowner,
r_6.relnamespace, c_5.conname, c_5.conkey, c_5.connamespace
Merge Cond: (r_6.oid = c_5.conrelid)
Buffers: shared hit=326
-> Sort (cost=345.67..347.36
rows=677 width=12) (actual time=1.185..1.231 rows=694 loops=1)
Output: r_6.oid,
r_6.relowner, r_6.relnamespace
Sort Key: r_6.oid
Sort Method: quicksort Memory: 57kB
Buffers: shared hit=274
-> Seq Scan on
pg_catalog.pg_class r_6 (cost=0.00..313.84 rows=677 width=12) (actual
time=0.008..1.020 rows=694 loops=1)
Output: r_6.oid,
r_6.relowner, r_6.relnamespace
Filter: (r_6.relkind
= ANY ('{r,p}'::"char"[]))
Rows Removed by Filter: 2559
Buffers: shared hit=274
-> Sort (cost=171.10..175.50
rows=1760 width=95) (actual time=1.124..1.233 rows=1707 loops=1)
Output: c_5.conname,
c_5.conkey, c_5.conrelid, c_5.connamespace
Sort Key: c_5.conrelid
Sort Method: quicksort
Memory: 289kB
Buffers: shared hit=52
-> Seq Scan on
pg_catalog.pg_constraint c_5 (cost=0.00..76.23 rows=1760 width=95)
(actual time=0.007..0.544 rows=1707 loops=1)
Output: c_5.conname,
c_5.conkey, c_5.conrelid, c_5.connamespace
Filter: (c_5.contype
= ANY ('{p,u,f}'::"char"[]))
Rows Removed by Filter: 2
Buffers: shared hit=52
-> Materialize (cost=0.00..1.09
rows=4 width=4) (actual time=0.000..0.001 rows=4 loops=1707)
Output: nr_6.oid
Buffers: shared hit=1
-> Seq Scan on
pg_catalog.pg_namespace nr_6 (cost=0.00..1.07 rows=4 width=4) (actual
time=0.006..0.013 rows=7 loops=1)
Output: nr_6.oid
Filter: (NOT
pg_is_other_temp_schema(nr_6.oid))
Rows Removed by Filter: 2
Buffers: shared hit=1
-> Sort (cost=1.14..1.15 rows=6 width=4) (actual
time=0.010..0.011 rows=9 loops=2)
Output: nc_4.oid
Sort Key: nc_4.oid
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=1
-> Seq Scan on pg_catalog.pg_namespace nc_4
(cost=0.00..1.06 rows=6 width=4) (actual time=0.013..0.014 rows=9
loops=1)
Output: nc_4.oid
Buffers: shared hit=1
-> Index Scan using pg_attribute_relid_attnum_index on
pg_catalog.pg_attribute a_1 (cost=0.29..0.33 rows=1 width=70) (actual
time=0.015..0.015 rows=1 loops=2)
Output: a_1.attrelid, a_1.attname, a_1.atttypid,
a_1.attstattarget, a_1.attlen, a_1.attnum, a_1.attndims,
a_1.attcacheoff, a_1.atttypmod, a_1.attbyval, a_1.attstorage,
a_1.attalign, a_1.attnotnull, a_1.atthasdef, a_1.atthasmissing,
a_1.attidentity, a_1.attgenerated, a_1.attisdropped, a_1.attislocal,
a_1.attinhcount, a_1.attcollation, a_1.attacl, a_1.attoptions,
a_1.attfdwoptions, a_1.attmissingval
Index Cond: ((a_1.attrelid = r_6.oid) AND (a_1.attnum =
((information_schema._pg_expandarray(c_5.conkey))).x))
Filter: ((NOT a_1.attisdropped) AND (pg_has_role(r_6.relowner,
'USAGE'::text) OR has_column_privilege(r_6.oid, a_1.attnum, 'SELECT,
INSERT, UPDATE, REFERENCES'::text)))
Buffers: shared hit=6
Planning Time: 7.329 ms
Execution Time: 80.546 ms
server parameters (everything except random_page_cost )
allow_system_table_mods off Allows modifications of the structure of
system tables.
application_name pgAdmin 4 - CONN:6043198 Sets the application name to
be reported in statistics and logs.
archive_cleanup_command Sets the shell command that will be executed
at every restart point.
archive_command (disabled) Sets the shell command that will be called
to archive a WAL file.
archive_mode off Allows archiving of WAL files using archive_command.
archive_timeout 0 Forces a switch to the next WAL file if a new file
has not been started within N seconds.
array_nulls on Enable input of NULL elements in arrays.
authentication_timeout 1min Sets the maximum allowed time to complete
client authentication.
autovacuum on Starts the autovacuum subprocess.
autovacuum_analyze_scale_factor 0.1 Number of tuple inserts, updates,
or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold 50 Minimum number of tuple inserts,
updates, or deletes prior to analyze.
autovacuum_freeze_max_age 200000000 Age at which to autovacuum a table
to prevent transaction ID wraparound.
autovacuum_max_workers 3 Sets the maximum number of simultaneously
running autovacuum worker processes.
autovacuum_multixact_freeze_max_age 400000000 Multixact age at which
to autovacuum a table to prevent multixact wraparound.
autovacuum_naptime 1min Time to sleep between autovacuum runs.
autovacuum_vacuum_cost_delay 2ms Vacuum cost delay in milliseconds,
for autovacuum.
autovacuum_vacuum_cost_limit -1 Vacuum cost amount available before
napping, for autovacuum.
autovacuum_vacuum_scale_factor 0.2 Number of tuple updates or deletes
prior to vacuum as a fraction of reltuples.
autovacuum_vacuum_threshold 50 Minimum number of tuple updates or
deletes prior to vacuum.
autovacuum_work_mem -1 Sets the maximum memory to be used by each
autovacuum worker process.
backend_flush_after 0 Number of pages after which previously performed
writes are flushed to disk.
backslash_quote safe_encoding Sets whether "\'" is allowed in string literals.
bgwriter_delay 200ms Background writer sleep time between rounds.
bgwriter_flush_after 0 Number of pages after which previously
performed writes are flushed to disk.
bgwriter_lru_maxpages 100 Background writer maximum number of LRU
pages to flush per round.
bgwriter_lru_multiplier 2 Multiple of the average buffer usage to free
per round.
block_size 8192 Shows the size of a disk block.
bonjour off Enables advertising the server via Bonjour.
bonjour_name Sets the Bonjour service name.
bytea_output hex Sets the output format for bytea.
check_function_bodies on Check function bodies during CREATE FUNCTION.
checkpoint_completion_target 0.5 Time spent flushing dirty buffers
during checkpoint, as fraction of checkpoint interval.
checkpoint_flush_after 0 Number of pages after which previously
performed writes are flushed to disk.
checkpoint_timeout 5min Sets the maximum time between automatic WAL checkpoints.
checkpoint_warning 30s Enables warnings if checkpoint segments are
filled more frequently than this.
client_encoding UNICODE Sets the client's character set encoding.
client_min_messages notice Sets the message levels that are sent to the client.
cluster_name Sets the name of the cluster, which is included in the
process title.
commit_delay 0 Sets the delay in microseconds between transaction
commit and flushing WAL to disk.
commit_siblings 5 Sets the minimum concurrent open transactions before
performing commit_delay.
config_file D:/ASCDB/postgresql.conf Sets the server's main configuration file.
constraint_exclusion partition Enables the planner to use constraints
to optimize queries.
cpu_index_tuple_cost 0.005 Sets the planner's estimate of the cost of
processing each index entry during an index scan.
cpu_operator_cost 0.0025 Sets the planner's estimate of the cost of
processing each operator or function call.
cpu_tuple_cost 0.01 Sets the planner's estimate of the cost of
processing each tuple (row).
cursor_tuple_fraction 0.1 Sets the planner's estimate of the fraction
of a cursor's rows that will be retrieved.
data_checksums off Shows whether data checksums are turned on for this cluster.
data_directory D:/ASCDB Sets the server's data directory.
data_directory_mode 0700 Mode of the data directory.
data_sync_retry off Whether to continue running after a failure to
sync data files.
DateStyle ISO, DMY Sets the display format for date and time values.
db_user_namespace off Enables per-database user names.
deadlock_timeout 1s Sets the time to wait on a lock before checking
for deadlock.
debug_assertions off Shows whether the running server has assertion
checks enabled.
debug_pretty_print on Indents parse and plan tree displays.
debug_print_parse off Logs each query's parse tree.
debug_print_plan off Logs each query's execution plan.
debug_print_rewritten off Logs each query's rewritten parse tree.
default_statistics_target 100 Sets the default statistics target.
default_table_access_method heap Sets the default table access method
for new tables.
default_tablespace Sets the default tablespace to create tables and indexes in.
default_text_search_config pg_catalog.english Sets default text search
configuration.
default_transaction_deferrable off Sets the default deferrable status
of new transactions.
default_transaction_isolation read committed Sets the transaction
isolation level of each new transaction.
default_transaction_read_only off Sets the default read-only status of
new transactions.
dynamic_library_path $libdir Sets the path for dynamically loadable modules.
dynamic_shared_memory_type windows Selects the dynamic shared memory
implementation used.
effective_cache_size 9GB Sets the planner's assumption about the total
size of the data caches.
effective_io_concurrency 0 Number of simultaneous requests that can be
handled efficiently by the disk subsystem.
enable_bitmapscan on Enables the planner's use of bitmap-scan plans.
enable_gathermerge on Enables the planner's use of gather merge plans.
enable_hashagg on Enables the planner's use of hashed aggregation plans.
enable_hashjoin on Enables the planner's use of hash join plans.
enable_indexonlyscan on Enables the planner's use of index-only-scan plans.
enable_indexscan on Enables the planner's use of index-scan plans.
enable_material on Enables the planner's use of materialization.
enable_mergejoin on Enables the planner's use of merge join plans.
enable_nestloop on Enables the planner's use of nested-loop join plans.
enable_parallel_append on Enables the planner's use of parallel append plans.
enable_parallel_hash on Enables the planner's use of parallel hash plans.
enable_partition_pruning on Enables plan-time and run-time partition pruning.
enable_partitionwise_aggregate off Enables partitionwise aggregation
and grouping.
enable_partitionwise_join off Enables partitionwise join.
enable_seqscan on Enables the planner's use of sequential-scan plans.
enable_sort on Enables the planner's use of explicit sort steps.
enable_tidscan on Enables the planner's use of TID scan plans.
escape_string_warning on Warn about backslash escapes in ordinary
string literals.
event_source PostgreSQL Sets the application name used to identify
PostgreSQL messages in the event log.
exit_on_error off Terminate session on any error.
external_pid_file Writes the postmaster PID to the specified file.
extra_float_digits 1 Sets the number of digits displayed for
floating-point values.
force_parallel_mode off Forces use of parallel query facilities.
from_collapse_limit 80 Sets the FROM-list size beyond which subqueries
are not collapsed.
fsync on Forces synchronization of updates to disk.
full_page_writes on Writes full pages to WAL when first modified after
a checkpoint.
geqo on Enables genetic query optimization.
geqo_effort 5 GEQO: effort is used to set the default for other GEQO parameters.
geqo_generations 0 GEQO: number of iterations of the algorithm.
geqo_pool_size 0 GEQO: number of individuals in the population.
geqo_seed 0 GEQO: seed for random path selection.
geqo_selection_bias 2 GEQO: selective pressure within the population.
geqo_threshold 12 Sets the threshold of FROM items beyond which GEQO is used.
gin_fuzzy_search_limit 0 Sets the maximum allowed result for exact
search by GIN.
gin_pending_list_limit 4MB Sets the maximum size of the pending list
for GIN index.
hba_file D:/ASCDB/pg_hba.conf Sets the server's "hba" configuration file.
hot_standby on Allows connections and queries during recovery.
hot_standby_feedback off Allows feedback from a hot standby to the
primary that will avoid query conflicts.
huge_pages try Use of huge pages on Linux or Windows.
ident_file D:/ASCDB/pg_ident.conf Sets the server's "ident" configuration file.
idle_in_transaction_session_timeout 0 Sets the maximum allowed
duration of any idling transaction.
ignore_checksum_failure off Continues processing after a checksum failure.
ignore_system_indexes off Disables reading from system indexes.
integer_datetimes on Datetimes are integer based.
IntervalStyle postgres Sets the display format for interval values.
jit on Allow JIT compilation.
jit_above_cost 100000 Perform JIT compilation if query is more expensive.
jit_debugging_support off Register JIT compiled function with debugger.
jit_dump_bitcode off Write out LLVM bitcode to facilitate JIT debugging.
jit_expressions on Allow JIT compilation of expressions.
jit_inline_above_cost 500000 Perform JIT inlining if query is more expensive.
jit_optimize_above_cost 500000 Optimize JITed functions if query is
more expensive.
jit_profiling_support off Register JIT compiled function with perf profiler.
jit_provider llvmjit JIT provider to use.
jit_tuple_deforming on Allow JIT compilation of tuple deforming.
join_collapse_limit 80 Sets the FROM-list size beyond which JOIN
constructs are not flattened.
krb_caseins_users off Sets whether Kerberos and GSSAPI user names
should be treated as case-insensitive.
krb_server_keyfile Sets the location of the Kerberos server key file.
lc_collate English_United Kingdom.1252 Shows the collation order locale.
lc_ctype English_United Kingdom.1252 Shows the character
classification and case conversion locale.
lc_messages English_United States.1252 Sets the language in which
messages are displayed.
lc_monetary English_United States.1252 Sets the locale for formatting
monetary amounts.
lc_numeric English_United States.1252 Sets the locale for formatting numbers.
lc_time English_United Kingdom.1252 Sets the locale for formatting
date and time values.
listen_addresses * Sets the host name or IP address(es) to listen to.
lo_compat_privileges off Enables backward compatibility mode for
privilege checks on large objects.
local_preload_libraries Lists unprivileged shared libraries to
preload into each backend.
lock_timeout 0 Sets the maximum allowed duration of any wait for a lock.
log_autovacuum_min_duration -1 Sets the minimum execution time above
which autovacuum actions will be logged.
log_checkpoints off Logs each checkpoint.
log_connections off Logs each successful connection.
log_destination stderr Sets the destination for server log output.
log_directory log Sets the destination directory for log files.
log_disconnections off Logs end of a session, including duration.
log_duration off Logs the duration of each completed SQL statement.
log_error_verbosity default Sets the verbosity of logged messages.
log_executor_stats off Writes executor performance statistics to the server log.
log_file_mode 0640 Sets the file permissions for log files.
log_filename postgresql-%Y-%m-%d_%H%M%S.log Sets the file name pattern
for log files.
log_hostname off Logs the host name in the connection logs.
log_line_prefix %m [%p] Controls information prefixed to each log line.
log_lock_waits off Logs long lock waits.
log_min_duration_statement -1 Sets the minimum execution time above
which statements will be logged.
log_min_error_statement error Causes all statements generating error
at or above this level to be logged.
log_min_messages warning Sets the message levels that are logged.
log_parser_stats off Writes parser performance statistics to the server log.
log_planner_stats off Writes planner performance statistics to the server log.
log_replication_commands off Logs each replication command.
log_rotation_age 1d Automatic log file rotation will occur after N minutes.
log_rotation_size 10MB Automatic log file rotation will occur after N kilobytes.
log_statement none Sets the type of statements logged.
log_statement_stats off Writes cumulative performance statistics to
the server log.
log_temp_files -1 Log the use of temporary files larger than this
number of kilobytes.
log_timezone Europe/London Sets the time zone to use in log messages.
log_transaction_sample_rate 0 Set the fraction of transactions to log
for new transactions.
log_truncate_on_rotation off Truncate existing log files of same name
during log rotation.
logging_collector on Start a subprocess to capture stderr output
and/or csvlogs into log files.
maintenance_work_mem 2047MB Sets the maximum memory to be used for
maintenance operations.
max_connections 140 Sets the maximum number of concurrent connections.
max_files_per_process 1000 Sets the maximum number of simultaneously
open files for each server process.
max_function_args 100 Shows the maximum number of function arguments.
max_identifier_length 63 Shows the maximum identifier length.
max_index_keys 32 Shows the maximum number of index keys.
max_locks_per_transaction 64 Sets the maximum number of locks per transaction.
max_logical_replication_workers 4 Maximum number of logical
replication worker processes.
max_parallel_maintenance_workers 2 Sets the maximum number of parallel
processes per maintenance operation.
max_parallel_workers 8 Sets the maximum number of parallel workers
that can be active at one time.
max_parallel_workers_per_gather 2 Sets the maximum number of parallel
processes per executor node.
max_pred_locks_per_page 2 Sets the maximum number of predicate-locked
tuples per page.
max_pred_locks_per_relation -2 Sets the maximum number of
predicate-locked pages and tuples per relation.
max_pred_locks_per_transaction 64 Sets the maximum number of predicate
locks per transaction.
max_prepared_transactions 0 Sets the maximum number of simultaneously
prepared transactions.
max_replication_slots 10 Sets the maximum number of simultaneously
defined replication slots.
max_stack_depth 2MB Sets the maximum stack depth, in kilobytes.
max_standby_archive_delay 30s Sets the maximum delay before canceling
queries when a hot standby server is processing archived WAL data.
max_standby_streaming_delay 30s Sets the maximum delay before
canceling queries when a hot standby server is processing streamed WAL
data.
max_sync_workers_per_subscription 2 Maximum number of table
synchronization workers per subscription.
max_wal_senders 10 Sets the maximum number of simultaneously running
WAL sender processes.
max_wal_size 2GB Sets the WAL size that triggers a checkpoint.
max_worker_processes 8 Maximum number of concurrent worker processes.
min_parallel_index_scan_size 512kB Sets the minimum amount of index
data for a parallel scan.
min_parallel_table_scan_size 8MB Sets the minimum amount of table data
for a parallel scan.
min_wal_size 1GB Sets the minimum size to shrink the WAL to.
old_snapshot_threshold -1 Time before a snapshot is too old to read
pages changed after the snapshot was taken.
operator_precedence_warning off Emit a warning for constructs that
changed meaning since PostgreSQL 9.4.
parallel_leader_participation on Controls whether Gather and Gather
Merge also run subplans.
parallel_setup_cost 1000 Sets the planner's estimate of the cost of
starting up worker processes for parallel query.
parallel_tuple_cost 0.1 Sets the planner's estimate of the cost of
passing each tuple (row) from worker to master backend.
password_encryption md5 Chooses the algorithm for encrypting passwords.
plan_cache_mode auto Controls the planner's selection of custom or generic plan.
port 5432 Sets the TCP port the server listens on.
post_auth_delay 0 Waits N seconds on connection startup after authentication.
pre_auth_delay 0 Waits N seconds on connection startup before authentication.
primary_conninfo Sets the connection string to be used to connect to
the sending server.
primary_slot_name Sets the name of the replication slot to use on the
sending server.
promote_trigger_file Specifies a file name whose presence ends
recovery in the standby.
quote_all_identifiers off When generating SQL fragments, quote all identifiers.
random_page_cost 4 Sets the planner's estimate of the cost of a
nonsequentially fetched disk page.
recovery_end_command Sets the shell command that will be executed
once at the end of recovery.
recovery_min_apply_delay 0 Sets the minimum delay for applying changes
during recovery.
recovery_target Set to "immediate" to end recovery as soon as a
consistent state is reached.
recovery_target_action pause Sets the action to perform upon reaching
the recovery target.
recovery_target_inclusive on Sets whether to include or exclude
transaction with recovery target.
recovery_target_lsn Sets the LSN of the write-ahead log location up
to which recovery will proceed.
recovery_target_name Sets the named restore point up to which
recovery will proceed.
recovery_target_time Sets the time stamp up to which recovery will proceed.
recovery_target_timeline latest Specifies the timeline to recover into.
recovery_target_xid Sets the transaction ID up to which recovery will proceed.
restart_after_crash on Reinitialize server after backend crash.
restore_command Sets the shell command that will retrieve an archived WAL file.
row_security on Enable row security.
search_path "$user", public Sets the schema search order for names
that are not schema-qualified.
segment_size 1GB Shows the number of pages per disk file.
seq_page_cost 1 Sets the planner's estimate of the cost of a
sequentially fetched disk page.
server_encoding UTF8 Sets the server (database) character set encoding.
server_version 12.5 Shows the server version.
server_version_num 120005 Shows the server version as an integer.
session_preload_libraries Lists shared libraries to preload into each backend.
session_replication_role origin Sets the session's behavior for
triggers and rewrite rules.
shared_buffers 5100MB Sets the number of shared memory buffers used by
the server.
shared_memory_type windows Selects the shared memory implementation
used for the main shared memory region.
shared_preload_libraries Lists shared libraries to preload into server.
ssl off Enables SSL connections.
ssl_ca_file Location of the SSL certificate authority file.
ssl_cert_file server.crt Location of the SSL server certificate file.
ssl_ciphers HIGH:MEDIUM:+3DES:!aNULL Sets the list of allowed SSL ciphers.
ssl_crl_file Location of the SSL certificate revocation list file.
ssl_dh_params_file Location of the SSL DH parameters file.
ssl_ecdh_curve prime256v1 Sets the curve to use for ECDH.
ssl_key_file server.key Location of the SSL server private key file.
ssl_library OpenSSL Name of the SSL library.
ssl_max_protocol_version Sets the maximum SSL/TLS protocol version to use.
ssl_min_protocol_version TLSv1 Sets the minimum SSL/TLS protocol version to use.
ssl_passphrase_command Command to obtain passphrases for SSL.
ssl_passphrase_command_supports_reload off Also use
ssl_passphrase_command during server reload.
ssl_prefer_server_ciphers on Give priority to server ciphersuite order.
standard_conforming_strings on Causes '...' strings to treat
backslashes literally.
statement_timeout 0 Sets the maximum allowed duration of any statement.
stats_temp_directory pg_stat_tmp Writes temporary statistics files to
the specified directory.
superuser_reserved_connections 3 Sets the number of connection slots
reserved for superusers.
synchronize_seqscans on Enable synchronized sequential scans.
synchronous_commit on Sets the current transaction's synchronization level.
synchronous_standby_names Number of synchronous standbys and list of
names of potential synchronous ones.
syslog_facility none Sets the syslog "facility" to be used when syslog enabled.
syslog_ident postgres Sets the program name used to identify
PostgreSQL messages in syslog.
syslog_sequence_numbers on Add sequence number to syslog messages to
avoid duplicate suppression.
syslog_split_messages on Split messages sent to syslog by lines and to
fit into 1024 bytes.
tcp_keepalives_count 0 Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle -1 Time between issuing TCP keepalives.
tcp_keepalives_interval -1 Time between TCP keepalive retransmits.
tcp_user_timeout 0 TCP user timeout.
temp_buffers 8MB Sets the maximum number of temporary buffers used by
each session.
temp_file_limit -1 Limits the total size of all temporary files used
by each process.
temp_tablespaces Sets the tablespace(s) to use for temporary tables
and sort files.
TimeZone Europe/London Sets the time zone for displaying and
interpreting time stamps.
timezone_abbreviations Default Selects a file of time zone abbreviations.
trace_notify off Generates debugging output for LISTEN and NOTIFY.
trace_recovery_messages log Enables logging of recovery-related
debugging information.
trace_sort off Emit information about resource usage in sorting.
track_activities on Collects information about executing commands.
track_activity_query_size 1kB Sets the size reserved for
pg_stat_activity.query, in bytes.
track_commit_timestamp off Collects transaction commit time.
track_counts on Collects statistics on database activity.
track_functions none Collects function-level statistics on database activity.
track_io_timing off Collects timing statistics for database I/O activity.
transaction_deferrable off Whether to defer a read-only serializable
transaction until it can be executed with no possible serialization
failures.
transaction_isolation read committed Sets the current transaction's
isolation level.
transaction_read_only off Sets the current transaction's read-only status.
transform_null_equals off Treats "expr=NULL" as "expr IS NULL".
unix_socket_directories Sets the directories where Unix-domain
sockets will be created.
unix_socket_group Sets the owning group of the Unix-domain socket.
unix_socket_permissions 0777 Sets the access permissions of the
Unix-domain socket.
update_process_title off Updates the process title to show the active
SQL command.
vacuum_cleanup_index_scale_factor 0.1 Number of tuple inserts prior to
index cleanup as a fraction of reltuples.
vacuum_cost_delay 0 Vacuum cost delay in milliseconds.
vacuum_cost_limit 200 Vacuum cost amount available before napping.
vacuum_cost_page_dirty 20 Vacuum cost for a page dirtied by vacuum.
vacuum_cost_page_hit 1 Vacuum cost for a page found in the buffer cache.
vacuum_cost_page_miss 10 Vacuum cost for a page not found in the buffer cache.
vacuum_defer_cleanup_age 0 Number of transactions by which VACUUM and
HOT cleanup should be deferred, if any.
vacuum_freeze_min_age 50000000 Minimum age at which VACUUM should
freeze a table row.
vacuum_freeze_table_age 150000000 Age at which VACUUM should scan
whole table to freeze tuples.
vacuum_multixact_freeze_min_age 5000000 Minimum age at which VACUUM
should freeze a MultiXactId in a table row.
vacuum_multixact_freeze_table_age 150000000 Multixact age at which
VACUUM should scan whole table to freeze tuples.
wal_block_size 8192 Shows the block size in the write ahead log.
wal_buffers 16MB Sets the number of disk-page buffers in shared memory for WAL.
wal_compression off Compresses full-page writes written in WAL file.
wal_consistency_checking Sets the WAL resource managers for which WAL
consistency checks are done.
wal_init_zero on Writes zeroes to new WAL files before first use.
wal_keep_segments 0 Sets the number of WAL files held for standby servers.
wal_level replica Set the level of information written to the WAL.
wal_log_hints off Writes full pages to WAL when first modified after a
checkpoint, even for a non-critical modifications.
wal_receiver_status_interval 10s Sets the maximum interval between WAL
receiver status reports to the sending server.
wal_receiver_timeout 1min Sets the maximum wait time to receive data
from the sending server.
wal_recycle on Recycles WAL files by renaming them.
wal_retrieve_retry_interval 5s Sets the time to wait before retrying
to retrieve WAL after a failed attempt.
wal_segment_size 16MB Shows the size of write ahead log segments.
wal_sender_timeout 1min Sets the maximum time to wait for WAL replication.
wal_sync_method open_datasync Selects the method used for forcing WAL
updates to disk.
wal_writer_delay 200ms Time between WAL flushes performed in the WAL writer.
wal_writer_flush_after 1MB Amount of WAL written out by WAL writer
that triggers a flush.
work_mem 256MB Sets the maximum memory to be used for query workspaces.
xmlbinary base64 Sets how binary values are to be encoded in XML.
xmloption content Sets whether XML data in implicit parsing and
serialization operations is to be considered as documents or content
fragments.
zero_damaged_pages off Continues processing past damaged page headers.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
@ 2021-09-24 05:33 Michael Lewis <mlewis@entrata.com>
parent: Arturas Mazeika <mazeika@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Lewis @ 2021-09-24 05:33 UTC (permalink / raw)
To: Arturas Mazeika <mazeika@gmail.com>; +Cc: pgsql-performance
I believe that this is a planning problem with the number of tables/joins
involved in the query you have written. If you take a look at the
definition of the views in information_schema that you are using and read
about from_collapse_limit/join_collapse_limit, you may see that this is a
bit painful for the planner. It might be cumbersome to use the actual
system tables underneath, but that would certainly lead to much better
performance. Otherwise, I would look at perhaps putting the view that has a
WHERE condition on it as the FROM to encourage the planner to perhaps
filter that set first and join the other tables after. If that didn't help,
I might even use a materialized CTE to force the issue.
Hopefully a real expert will chime in with a better explanation of the
challenges or preferred solution.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
@ 2021-09-27 10:09 Arturas Mazeika <mazeika@gmail.com>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 7+ messages in thread
From: Arturas Mazeika @ 2021-09-27 10:09 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: pgsql-performance
Hi Michael,
Thanks for the answer.
I agree that the tables behind the views makes the query processing
challenging. What makes it even more challenging to us is that this query
is generated by a third party library that we use to operationalize the
schema changes.
I am trying to figure out what went wrong with query planning that
hashjoins perform worse compared to index/sort joins. It looks to me that
this is mostly because (1) the temporal space for creating a hashtable is a
lot larger compared to sort/index joins and (2) it is *not *that the
predicted selectivity is way off compared to the actual selectivity. W.r.t
(1) in almost all cases the IOs needed to do hashing is way bigger compared
to indexes (see in red if your email client supports html formatting, only
in one parameter the hash joins "win" against the index/sort joins see in
green, and the actual times are always worse, see in blue):
-> Hash Join (cost=415.40..494.06
rows=263 width=136) (actual time=0.007..0.869 rows=1707 loops=1672)
Output: c_5.conname,
c_5.connamespace, r_5.relname, r_5.relnamespace
Inner Unique: true
Hash Cond: (c_5.conrelid
= r_5.oid)
Buffers: shared hit=87218
vs. corresponding index/sort join:
-> Nested Loop (cost=0.28..171.05 rows=1
width=136) (actual time=0.024..1.976 rows=595 loops=2)
Output: c_4.conname,
c_4.connamespace, r_5.relname, r_5.relnamespace
Inner Unique: true
Buffers: shared hit=3674
or looking at the global level:
Nested Loop (cost=2174.36..13670.47 rows=1 width=320) (actual
time=5499.728..26310.137 rows=2 loops=1)
Output: "*SELECT* 1".table_name,
(a.attname)::information_schema.sql_identifier, "*SELECT* 1_1".table_name,
(a_1.attname)::information_schema.sql_identifier,
(con.conname)::information_schema.sql_identifier
Inner Unique: true
Buffers: shared hit=1961035
vs
Nested Loop (cost=1736.10..18890.44 rows=1 width=320) (actual
time=30.780..79.572 rows=2 loops=1)
Output: "*SELECT* 1".table_name,
(a.attname)::information_schema.sql_identifier, "*SELECT* 1_1".table_name,
(a_1.attname)::information_schema.sql_identifier,
(con.conname)::information_schema.sql_identifier
Inner Unique: true
Buffers: shared hit=9018
Which makes me wonder why hash join was chosen at all. Looks like a bug
somewhere in query optimization.
Cheers,
Arturas
On Fri, Sep 24, 2021 at 7:34 AM Michael Lewis <mlewis@entrata.com> wrote:
> I believe that this is a planning problem with the number of tables/joins
> involved in the query you have written. If you take a look at the
> definition of the views in information_schema that you are using and read
> about from_collapse_limit/join_collapse_limit, you may see that this is a
> bit painful for the planner. It might be cumbersome to use the actual
> system tables underneath, but that would certainly lead to much better
> performance. Otherwise, I would look at perhaps putting the view that has a
> WHERE condition on it as the FROM to encourage the planner to perhaps
> filter that set first and join the other tables after. If that didn't help,
> I might even use a materialized CTE to force the issue.
>
> Hopefully a real expert will chime in with a better explanation of the
> challenges or preferred solution.
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
@ 2021-09-27 14:12 Michael Lewis <mlewis@entrata.com>
parent: Arturas Mazeika <mazeika@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Lewis @ 2021-09-27 14:12 UTC (permalink / raw)
To: Arturas Mazeika <mazeika@gmail.com>; +Cc: pgsql-performance
I'm unclear what you changed to get the planner to choose one vs the other.
Did you disable hashjoins? Without the full plan to review, it is tough to
agre with any conclusion that these particular nodes are troublesome. It
might be that this was the right choice for that part of that plan, but
improper estimates at a earlier step were problematic.
What configs have you changed such as work_mem, random_page_cost, and such?
If random_page_cost & seq_page_cost are still default values, then the
planner will tend to do more seq scans I believe, and hash them to join
with large sets of data, rather than do nested loop index scans. I think
that's how that works. With the lack of flexibility to change the query,
you might be able to set a few configs for the user that runs these schema
checks. If you can find changes that make an overall improvement.
*Michael Lewis | Database Engineer*
*Entrata*
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
@ 2021-09-27 19:56 Arturas Mazeika <mazeika@gmail.com>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 7+ messages in thread
From: Arturas Mazeika @ 2021-09-27 19:56 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: pgsql-performance
I
Hi Michael,
Thanks a lot for having a look at the query once again in more detail. In
short, you are right, I fired the liquibase scripts and observed the exact
query that was hanging in pg_stats_activity. The query was:
SELECT
FK.TABLE_NAME as "TABLE_NAME"
, CU.COLUMN_NAME as "COLUMN_NAME"
, PK.TABLE_NAME as "REFERENCED_TABLE_NAME"
, PT.COLUMN_NAME as "REFERENCED_COLUMN_NAME"
, C.CONSTRAINT_NAME as "CONSTRAINT_NAME"
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON
C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME
= CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME
, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE
lower(FK.TABLE_NAME)='secrole_condcollection'
I rerun this query twice. Once with set enable_hashjoin = false; and set
enable_hashjoin = true; . I observed that the join order was very, very
similar between the hash and index plans. I reran the above two queries
with random_page_cost to 2, 1.5, or 1.0 and observed no difference
whatsoever, the planner was always choosing the hashjoins over sort/index
nested loops. the seq_page_cost is set to default value 1. The tables
behind the views do not have more than 10K rows, and do not exceed 400KB of
space. The work_mem parameter is set to 256MB, effective cache is 9GB, the
machine has something around 32-64GB of RAM, SSD as the primary drive, 140
default connections. The query planner, of course thinks that the overall
nested loop including hashes is better:
cost=2174.36..13670.47 (hash)
vs
cost=1736.10..18890.44 (index/sort join)
but I think there's a problem there, cause I don't think that one can reuse
the pre-computed hashes over and over again, while sort/index joins end up
hitting the same buffers, or am I wrong?
More details about the query plans as well as the complete set of settings
can be found in the original email at
https://www.postgresql.org/message-id/CAAUL%3DcFcvUo%3D7b4T-K5PqiqrF6etp59qcgv77DyK2Swa4VhYuQ%40mail...
If you could have another look into what's going on, I'd appreciate it a
lot. in postgres 9.6 our setup goes through the liquibase scripts in 5
minutes, and pg12 with hash joins may take up to 1.5 hours.
Cheers,
Arturas
On Mon, Sep 27, 2021 at 4:12 PM Michael Lewis <mlewis@entrata.com> wrote:
> I'm unclear what you changed to get the planner to choose one vs the
> other. Did you disable hashjoins? Without the full plan to review, it is
> tough to agre with any conclusion that these particular nodes are
> troublesome. It might be that this was the right choice for that part of
> that plan, but improper estimates at a earlier step were problematic.
>
> What configs have you changed such as work_mem, random_page_cost, and
> such? If random_page_cost & seq_page_cost are still default values,
> then the planner will tend to do more seq scans I believe, and hash them to
> join with large sets of data, rather than do nested loop index scans. I
> think that's how that works. With the lack of flexibility to change the
> query, you might be able to set a few configs for the user that runs these
> schema checks. If you can find changes that make an overall improvement.
>
>
> *Michael Lewis | Database Engineer*
> *Entrata*
>
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
@ 2021-09-28 14:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Arturas Mazeika <mazeika@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2021-09-28 14:13 UTC (permalink / raw)
To: Arturas Mazeika <mazeika@gmail.com>; +Cc: Michael Lewis <mlewis@entrata.com>; pgsql-performance
Arturas Mazeika <mazeika@gmail.com> writes:
> Thanks a lot for having a look at the query once again in more detail. In
> short, you are right, I fired the liquibase scripts and observed the exact
> query that was hanging in pg_stats_activity. The query was:
> SELECT
> FK.TABLE_NAME as "TABLE_NAME"
> , CU.COLUMN_NAME as "COLUMN_NAME"
> , PK.TABLE_NAME as "REFERENCED_TABLE_NAME"
> , PT.COLUMN_NAME as "REFERENCED_COLUMN_NAME"
> , C.CONSTRAINT_NAME as "CONSTRAINT_NAME"
> FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
> INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON
> C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
> INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
> C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
> INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME
> = CU.CONSTRAINT_NAME
> INNER JOIN (
> SELECT
> i1.TABLE_NAME
> , i2.COLUMN_NAME
> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
> INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
> i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
> WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
> ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE
> lower(FK.TABLE_NAME)='secrole_condcollection'
TBH, before worrying about performance you should be worrying about
correctness. constraint_name alone is not a sufficient join key
for these tables, so who's to say whether you're even getting the
right answers?
Per SQL spec, the join key to use is probably constraint_catalog
plus constraint_schema plus constraint_name. You might say you
don't need to compare constraint_catalog because that's fixed
within any one Postgres database, and that observation would be
correct. But you can't ignore the schema.
What's worse, the SQL-spec join keys are based on the assumption that
constraint names are unique within schemas, which is not enforced in
Postgres. Maybe you're all right here, because you're only looking
at primary key constraints, which are associated with indexes, which
being relations do indeed have unique-within-schema names. But you
still can't ignore the schema.
On the whole I don't think you're buying anything by going through
the SQL-spec information views, because this query is clearly pretty
dependent on Postgres-specific assumptions even if it looks like it's
portable. And you're definitely giving up a lot of performance, since
those views have so many complications from trying to map the spec's
view of whats-a-constraint onto the Postgres objects (not to mention
the spec's arbitrary opinions about which objects you're allowed to
see). This query would be probably be simpler, more correct, and a
lot faster if rewritten to query the Postgres catalogs directly.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: hashjoins, index loops to retrieve pk/ux constrains in pg12
@ 2021-09-29 13:05 Arturas Mazeika <mazeika@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Arturas Mazeika @ 2021-09-29 13:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Lewis <mlewis@entrata.com>; pgsql-performance
Hi Tom,
I agree that the query needs to be first correct, and second fast. I also
agree that this query works only if there are no duplicates among schemas
(if one chooses to create a table with the same names and index names and
constraint names in a different schema, this would not work). Provided the
assumptions are correct (what it is on our customer systems), we use
intermediate liquibase scripts to keep track of our database (schema)
changes, those intermediate scripts fire queries as mentioned above, i.e.,
we cannot directly influence how the query looks like.
Given these very hard constraints (i.e., the query is formulated using
information_schema, and not directly) is it possible to assess why the hash
joins plan is chosen? At the end of the day, the io block hit rate of this
query in hash joins is 3-4 orders of magnitude higher compared to
sort/index joins? Is there anything one can do on the configuration side to
avoid such hash-join pitfalls?
Cheers,
Arturas
On Tue, Sep 28, 2021 at 4:13 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Arturas Mazeika <mazeika@gmail.com> writes:
> > Thanks a lot for having a look at the query once again in more detail. In
> > short, you are right, I fired the liquibase scripts and observed the
> exact
> > query that was hanging in pg_stats_activity. The query was:
>
> > SELECT
> > FK.TABLE_NAME as "TABLE_NAME"
> > , CU.COLUMN_NAME as "COLUMN_NAME"
> > , PK.TABLE_NAME as "REFERENCED_TABLE_NAME"
> > , PT.COLUMN_NAME as "REFERENCED_COLUMN_NAME"
> > , C.CONSTRAINT_NAME as "CONSTRAINT_NAME"
> > FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
> > INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON
> > C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
> > INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON
> > C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
> > INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME
> > = CU.CONSTRAINT_NAME
> > INNER JOIN (
> > SELECT
> > i1.TABLE_NAME
> > , i2.COLUMN_NAME
> > FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
> > INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON
> > i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
> > WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
> > ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE
> > lower(FK.TABLE_NAME)='secrole_condcollection'
>
> TBH, before worrying about performance you should be worrying about
> correctness. constraint_name alone is not a sufficient join key
> for these tables, so who's to say whether you're even getting the
> right answers?
>
> Per SQL spec, the join key to use is probably constraint_catalog
> plus constraint_schema plus constraint_name. You might say you
> don't need to compare constraint_catalog because that's fixed
> within any one Postgres database, and that observation would be
> correct. But you can't ignore the schema.
>
> What's worse, the SQL-spec join keys are based on the assumption that
> constraint names are unique within schemas, which is not enforced in
> Postgres. Maybe you're all right here, because you're only looking
> at primary key constraints, which are associated with indexes, which
> being relations do indeed have unique-within-schema names. But you
> still can't ignore the schema.
>
> On the whole I don't think you're buying anything by going through
> the SQL-spec information views, because this query is clearly pretty
> dependent on Postgres-specific assumptions even if it looks like it's
> portable. And you're definitely giving up a lot of performance, since
> those views have so many complications from trying to map the spec's
> view of whats-a-constraint onto the Postgres objects (not to mention
> the spec's arbitrary opinions about which objects you're allowed to
> see). This query would be probably be simpler, more correct, and a
> lot faster if rewritten to query the Postgres catalogs directly.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-09-29 13:05 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-23 13:00 hashjoins, index loops to retrieve pk/ux constrains in pg12 Arturas Mazeika <mazeika@gmail.com>
2021-09-24 05:33 ` Michael Lewis <mlewis@entrata.com>
2021-09-27 10:09 ` Arturas Mazeika <mazeika@gmail.com>
2021-09-27 14:12 ` Michael Lewis <mlewis@entrata.com>
2021-09-27 19:56 ` Arturas Mazeika <mazeika@gmail.com>
2021-09-28 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-09-29 13:05 ` Arturas Mazeika <mazeika@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox