explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PHeP

Settings
# exclusive inclusive rows x rows loops node
1. 0.110 5,549.282 ↑ 2.1 121 1

GroupAggregate (cost=3,192.00..3,203.52 rows=256 width=160) (actual time=5,549.173..5,549.282 rows=121 loops=1)

  • Group Key: ((nc.nspname)::information_schema.sql_identifier), ((c.relname)::information_schema.sql_identifier), ((CASE WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN pg_get_viewdef(c.oid) ELSE NULL::text END)::information_schema.character_data), a.nom
2.          

CTE liste_schema

3. 0.100 2.429 ↑ 6.2 37 1

Unique (cost=618.49..619.64 rows=229 width=32) (actual time=2.251..2.429 rows=37 loops=1)

4. 0.260 2.329 ↓ 2.1 487 1

Sort (cost=618.49..619.07 rows=229 width=32) (actual time=2.250..2.329 rows=487 loops=1)

  • Sort Key: ((nc_1.nspname)::information_schema.sql_identifier)
  • Sort Method: quicksort Memory: 49kB
5. 0.114 2.069 ↓ 2.1 487 1

Hash Join (cost=14.28..609.52 rows=229 width=32) (actual time=0.193..2.069 rows=487 loops=1)

  • Hash Cond: (c_1.relnamespace = nc_1.oid)
6. 1.787 1.787 ↓ 1.4 487 1

Seq Scan on pg_class c_1 (cost=0.00..589.78 rows=343 width=4) (actual time=0.018..1.787 rows=487 loops=1)

  • Filter: ((relkind = 'v'::"char") AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
  • Rows Removed by Filter: 7296
7. 0.017 0.168 ↑ 3.4 69 1

Hash (cost=11.36..11.36 rows=233 width=68) (actual time=0.168..0.168 rows=69 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
8. 0.151 0.151 ↑ 3.4 69 1

Seq Scan on pg_namespace nc_1 (cost=0.00..11.36 rows=233 width=68) (actual time=0.014..0.151 rows=69 loops=1)

  • Filter: (NOT pg_is_other_temp_schema(oid))
  • Rows Removed by Filter: 280
9. 0.386 5,549.172 ↑ 2.0 125 1

Sort (cost=2,572.36..2,573.00 rows=256 width=160) (actual time=5,549.165..5,549.172 rows=125 loops=1)

  • Sort Key: ((nc.nspname)::information_schema.sql_identifier), ((c.relname)::information_schema.sql_identifier), ((CASE WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN pg_get_viewdef(c.oid) ELSE NULL::text END)::information_schema.character_data), a.nom
  • Sort Method: quicksort Memory: 322kB
10. 5,527.771 5,548.786 ↑ 2.0 125 1

Nested Loop (cost=22.08..2,562.12 rows=256 width=160) (actual time=14.799..5,548.786 rows=125 loops=1)

  • Join Filter: (((ls.table_schema)::text <> ((nc.nspname)::information_schema.sql_identifier)::text) AND (lower(((CASE WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN pg_get_viewdef(c.oid) ELSE NULL::text END)::information_schema.character_data)::text) ~~ concat('%', ls.table_schema, '%')))
  • Rows Removed by Join Filter: 13491
11. 2.478 2.478 ↑ 6.2 37 1

CTE Scan on liste_schema ls (cost=0.00..4.58 rows=229 width=32) (actual time=2.252..2.478 rows=37 loops=1)

12. 4.608 18.537 ↓ 1.6 368 37

Materialize (cost=22.08..750.25 rows=225 width=168) (actual time=0.006..0.501 rows=368 loops=37)

13. 10.214 13.929 ↓ 1.6 368 1

Nested Loop Left Join (cost=22.08..749.13 rows=225 width=168) (actual time=0.188..13.929 rows=368 loops=1)

  • Join Filter: (((nc.nspname)::information_schema.sql_identifier)::text ~~ concat('%', a.acronyme, '%'))
  • Rows Removed by Join Filter: 13273
14. 0.313 2.979 ↓ 1.6 368 1

Hash Join (cost=22.08..616.18 rows=225 width=136) (actual time=0.138..2.979 rows=368 loops=1)

  • Hash Cond: (c.relnamespace = nc.oid)
15. 2.543 2.543 ↓ 1.4 487 1

Seq Scan on pg_class c (cost=0.00..589.78 rows=343 width=76) (actual time=0.009..2.543 rows=487 loops=1)

  • Filter: ((relkind = 'v'::"char") AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
  • Rows Removed by Filter: 7296
16. 0.010 0.123 ↑ 3.5 65 1

Hash (cost=19.22..19.22 rows=229 width=68) (actual time=0.123..0.123 rows=65 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
17. 0.113 0.113 ↑ 3.5 65 1

Seq Scan on pg_namespace nc (cost=0.00..19.22 rows=229 width=68) (actual time=0.007..0.113 rows=65 loops=1)

  • Filter: ((NOT pg_is_other_temp_schema(oid)) AND (((nspname)::information_schema.sql_identifier)::text !~~ 'pg%'::text) AND (((nspname)::information_schema.sql_identifier)::text <> 'information_schema'::text) AND (((nspname)::information_schema.sql_identifier)::text <> 'administration_bd'::text))
  • Rows Removed by Filter: 284
18. 0.717 0.736 ↓ 1.4 37 368

Materialize (cost=0.00..1.39 rows=26 width=64) (actual time=0.000..0.002 rows=37 loops=368)

19. 0.019 0.019 ↓ 1.4 37 1

Seq Scan on application a (cost=0.00..1.26 rows=26 width=64) (actual time=0.014..0.019 rows=37 loops=1)

Planning time : 0.744 ms
Execution time : 5,549.432 ms