explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 17Kp

Settings
# exclusive inclusive rows x rows loops node
1. 0.294 107,798.961 ↓ 24.0 24 1

Nested Loop Left Join (cost=5,031.82..11,828.96 rows=1 width=32) (actual time=399.397..107,798.961 rows=24 loops=1)

  • Output: concat(concat('ALTER TABLE ', (c.relname)::information_schema.sql_identifier), ' REPLICA IDENTITY FULL;')
2. 6,095.710 107,798.427 ↓ 24.0 24 1

Nested Loop Anti Join (cost=5,031.39..11,828.43 rows=1 width=68) (actual time=399.368..107,798.427 rows=24 loops=1)

  • Output: c.relname, c.reloftype
  • Join Filter: ((((ss.nr_nspname)::information_schema.sql_identifier)::text = ((nc.nspname)::information_schema.sql_identifier)::text) AND (((ss.relname)::information_schema.sql_identifier)::text = ((c.relname)::information_schema.sql_identifier)::text))
  • Rows Removed by Join Filter: 10128762
3. 2.547 35.218 ↓ 1,621.0 1,621 1

Nested Loop (cost=839.17..3,160.89 rows=1 width=132) (actual time=12.347..35.218 rows=1,621 loops=1)

  • Output: nc.nspname, c.relname, c.reloftype
4. 0.184 0.184 ↑ 1.0 1 1

Seq Scan on pg_catalog.pg_namespace nc (cost=0.00..46.52 rows=1 width=68) (actual time=0.049..0.184 rows=1 loops=1)

  • Output: nc.oid, nc.nspname
  • Filter: ((NOT pg_is_other_temp_schema(nc.oid)) AND (((nc.nspname)::information_schema.sql_identifier)::text = 'public'::text))
  • Rows Removed by Filter: 139
5. 26.332 32.487 ↓ 324.2 1,621 1

Bitmap Heap Scan on pg_catalog.pg_class c (cost=839.17..3,114.32 rows=5 width=73) (actual time=12.295..32.487 rows=1,621 loops=1)

  • Output: 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.relhasoids, c.relhaspkey, c.relhasrules, c.relhastriggers, c.relhassubclass, c.relrowsecurity, c.relforcerowsecurity, c.relispopulated, c.relreplident, c.relispartition, c.relfrozenxid, c.relminmxid, c.relacl, c.reloptions, c.relpartbound
  • Recheck Cond: (c.relnamespace = nc.oid)
  • Filter: ((c.relkind = ANY ('{r,v,f,p}'::"char"[])) AND (((CASE WHEN (nc.oid = pg_my_temp_schema()) THEN 'LOCAL TEMPORARY'::text WHEN (c.relkind = ANY ('{r,p}'::"char"[])) THEN 'BASE TABLE'::text WHEN (c.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (c.relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text ELSE NULL::text END)::information_schema.character_data)::text = 'BASE TABLE'::text) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
  • Rows Removed by Filter: 6233
  • Heap Blocks: exact=1511
6. 6.155 6.155 ↓ 2.7 18,483 1

Bitmap Index Scan on pg_class_relname_nsp_index (cost=0.00..839.17 rows=6,833 width=0) (actual time=6.155..6.155 rows=18,483 loops=1)

  • Index Cond: (c.relnamespace = nc.oid)
7. 43,389.307 101,667.499 ↓ 40.8 6,249 1,621

Merge Join (cost=4,192.22..8,662.19 rows=153 width=128) (actual time=3.099..62.719 rows=6,249 loops=1,621)

  • Output: ss.relname, ss.nr_nspname
  • Merge Cond: ((a.attrelid = ss.roid) AND (a.attnum = ((ss.x).x)))
  • Join Filter: (pg_has_role(ss.relowner, 'USAGE'::text) OR has_column_privilege(ss.roid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text))
8. 52,893.230 52,893.230 ↑ 2.1 47,158 1,621

Index Scan using pg_attribute_relid_attnum_index on pg_catalog.pg_attribute a (cost=0.29..3,877.68 rows=97,692 width=6) (actual time=0.008..32.630 rows=47,158 loops=1,621)

  • 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.attidentity, a.attisdropped, a.attislocal, a.attinhcount, a.attcollation, a.attacl, a.attoptions, a.attfdwoptions
  • Filter: (NOT a.attisdropped)
  • Rows Removed by Filter: 1
9. 5,179.566 5,384.962 ↑ 1.6 6,249 1,621

Materialize (cost=4,191.93..4,241.93 rows=10,000 width=168) (actual time=0.126..3.322 rows=6,249 loops=1,621)

  • Output: ss.roid, ss.x, ss.relowner, ss.relname, ss.nr_nspname, ((ss.x).x)
10. 27.615 205.396 ↓ 1.2 12,208 1

Sort (cost=4,191.93..4,216.93 rows=10,000 width=168) (actual time=198.139..205.396 rows=12,208 loops=1)

  • Output: ss.roid, ss.x, ss.relowner, ss.relname, ss.nr_nspname, ((ss.x).x)
  • Sort Key: ss.roid, ((ss.x).x)
  • Sort Method: external merge Disk: 2160kB
11. 10.746 177.781 ↓ 1.2 12,208 1

Subquery Scan on ss (cost=46.96..3,045.79 rows=10,000 width=168) (actual time=35.778..177.781 rows=12,208 loops=1)

  • Output: ss.roid, ss.x, ss.relowner, ss.relname, ss.nr_nspname, (ss.x).x
12. 69.181 167.035 ↓ 1.2 12,208 1

ProjectSet (cost=46.96..2,945.79 rows=10,000 width=341) (actual time=35.775..167.035 rows=12,208 loops=1)

  • Output: r.oid, r.relname, r.relowner, NULL::name, nr.nspname, NULL::oid, NULL::name, NULL::"char", NULL::oid, NULL::smallint[], NULL::oid, information_schema._pg_expandarray(c_1.conkey)
13. 13.777 97.854 ↓ 735.7 7,357 1

Nested Loop (cost=46.96..2,893.24 rows=10 width=160) (actual time=35.381..97.854 rows=7,357 loops=1)

  • Output: c_1.conkey, r.oid, r.relname, r.relowner, nr.nspname
  • Inner Unique: true
14. 9.402 54.649 ↓ 735.7 7,357 1

Nested Loop (cost=46.82..2,891.58 rows=10 width=164) (actual time=14.887..54.649 rows=7,357 loops=1)

  • Output: nr.nspname, r.oid, r.relname, r.relowner, c_1.conkey, c_1.connamespace
15. 2.242 22.553 ↓ 17.4 1,621 1

Hash Join (cost=46.53..2,848.95 rows=93 width=136) (actual time=3.364..22.553 rows=1,621 loops=1)

  • Output: nr.nspname, r.oid, r.relname, r.relowner
  • Inner Unique: true
  • Hash Cond: (r.relnamespace = nr.oid)
16. 19.511 19.511 ↑ 5.1 2,284 1

Seq Scan on pg_catalog.pg_class r (cost=0.00..2,770.59 rows=11,730 width=76) (actual time=0.007..19.511 rows=2,284 loops=1)

  • Output: r.oid, r.relname, r.relowner, r.relnamespace
  • Filter: (r.relkind = ANY ('{r,p}'::"char"[]))
  • Rows Removed by Filter: 12352
17. 0.004 0.800 ↑ 1.0 1 1

Hash (cost=46.52..46.52 rows=1 width=68) (actual time=0.800..0.800 rows=1 loops=1)

  • Output: nr.nspname, nr.oid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.796 0.796 ↑ 1.0 1 1

Seq Scan on pg_catalog.pg_namespace nr (cost=0.00..46.52 rows=1 width=68) (actual time=0.014..0.796 rows=1 loops=1)

  • Output: nr.nspname, nr.oid
  • Filter: ((NOT pg_is_other_temp_schema(nr.oid)) AND (((nr.nspname)::information_schema.sql_identifier)::text = 'public'::text))
  • Rows Removed by Filter: 139
19. 22.694 22.694 ↓ 1.2 5 1,621

Index Scan using pg_constraint_conrelid_index on pg_catalog.pg_constraint c_1 (cost=0.29..0.42 rows=4 width=32) (actual time=0.007..0.014 rows=5 loops=1,621)

  • Output: 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.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, c_1.consrc
  • Index Cond: (c_1.conrelid = r.oid)
  • Filter: (c_1.contype = ANY ('{p,u,f}'::"char"[]))
  • Rows Removed by Filter: 0
20. 29.428 29.428 ↑ 1.0 1 7,357

Index Only Scan using pg_namespace_oid_index on pg_catalog.pg_namespace nc_1 (cost=0.14..0.17 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=7,357)

  • Output: nc_1.oid
  • Index Cond: (nc_1.oid = c_1.connamespace)
  • Heap Fetches: 7357
21. 0.072 0.240 ↓ 0.0 0 24

Nested Loop (cost=0.43..0.51 rows=1 width=4) (actual time=0.010..0.010 rows=0 loops=24)

  • Output: t.oid
  • Inner Unique: true
22. 0.168 0.168 ↓ 0.0 0 24

Index Scan using pg_type_oid_index on pg_catalog.pg_type t (cost=0.29..0.35 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=24)

  • Output: t.typnamespace, t.oid
  • Index Cond: (c.reloftype = t.oid)
23. 0.000 0.000 ↓ 0.0 0

Index Only Scan using pg_namespace_oid_index on pg_catalog.pg_namespace nt (cost=0.14..0.16 rows=1 width=4) (never executed)

  • Output: nt.oid
  • Index Cond: (nt.oid = t.typnamespace)
  • Heap Fetches: 0