explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nxbz

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 53.960 518.889 ↓ 38,782.0 38,782 1

Nested Loop Left Join (cost=424.63..499.21 rows=1 width=96) (actual time=41.376..518.889 rows=38,782 loops=1)

2. 41.610 309.801 ↓ 38,782.0 38,782 1

Nested Loop Left Join (cost=424.35..498.21 rows=1 width=196) (actual time=41.353..309.801 rows=38,782 loops=1)

3. 28.355 190.627 ↓ 38,782.0 38,782 1

Nested Loop (cost=423.92..497.18 rows=1 width=200) (actual time=41.348..190.627 rows=38,782 loops=1)

4. 0.206 41.632 ↓ 80.0 80 1

Merge Join (cost=423.64..424.19 rows=1 width=136) (actual time=41.296..41.632 rows=80 loops=1)

  • Merge Cond: ((((nc.nspname)::information_schema.sql_identifier)::text) = (((nc_1.nspname)::information_schema.sql_identifier)::text))
5. 0.045 0.110 ↑ 2.3 23 1

Sort (cost=4.94..5.07 rows=53 width=68) (actual time=0.094..0.110 rows=23 loops=1)

  • Sort Key: (((nc.nspname)::information_schema.sql_identifier)::text)
  • Sort Method: quicksort Memory: 28kB
6. 0.065 0.065 ↑ 2.3 23 1

Seq Scan on pg_namespace nc (cost=0.00..3.42 rows=53 width=68) (actual time=0.018..0.065 rows=23 loops=1)

  • Filter: ((NOT pg_is_other_temp_schema(oid)) AND (((nspname)::information_schema.sql_identifier)::text <> ALL ('{information_schema,pg_catalog}'::text[])))
  • Rows Removed by Filter: 78
7. 0.258 41.316 ↓ 100.0 100 1

Sort (cost=418.71..418.71 rows=1 width=132) (actual time=41.187..41.316 rows=100 loops=1)

  • Sort Key: (((nc_1.nspname)::information_schema.sql_identifier)::text)
  • Sort Method: quicksort Memory: 51kB
8. 3.892 41.058 ↓ 100.0 100 1

Nested Loop (cost=4.68..418.70 rows=1 width=132) (actual time=1.388..41.058 rows=100 loops=1)

  • Join Filter: (t_1.typnamespace = nt_1.oid)
  • Rows Removed by Join Filter: 10,000
9. 0.125 33.266 ↓ 100.0 100 1

Nested Loop Left Join (cost=4.68..414.87 rows=1 width=136) (actual time=1.376..33.266 rows=100 loops=1)

  • Join Filter: (t_1.typtype = 'd'::"char")
10. 0.185 32.941 ↓ 100.0 100 1

Nested Loop (cost=4.26..414.26 rows=1 width=141) (actual time=1.371..32.941 rows=100 loops=1)

11. 1.685 32.556 ↓ 100.0 100 1

Nested Loop (cost=3.98..409.59 rows=1 width=136) (actual time=1.361..32.556 rows=100 loops=1)

12. 1.110 3.319 ↓ 262.4 1,312 1

Hash Join (cost=3.69..267.59 rows=5 width=72) (actual time=0.070..3.319 rows=1,312 loops=1)

  • Hash Cond: (c_1.relnamespace = nc_1.oid)
13. 2.154 2.154 ↓ 188.0 1,316 1

Seq Scan on pg_class c_1 (cost=0.00..263.82 rows=7 width=12) (actual time=0.009..2.154 rows=1,316 loops=1)

  • Filter: ((relkind = ANY ('{r,v,f}'::"char"[])) AND (((relname)::information_schema.sql_identifier)::text = ((relname)::information_schema.sql_identifier)::text))
  • Rows Removed by Filter: 4,464
14. 0.016 0.055 ↑ 2.2 25 1

Hash (cost=3.01..3.01 rows=54 width=68) (actual time=0.055..0.055 rows=25 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
15. 0.039 0.039 ↑ 2.2 25 1

Seq Scan on pg_namespace nc_1 (cost=0.00..3.01 rows=54 width=68) (actual time=0.005..0.039 rows=25 loops=1)

  • Filter: (NOT pg_is_other_temp_schema(oid))
  • Rows Removed by Filter: 76
16. 27.552 27.552 ↓ 0.0 0 1,312

Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.29..28.39 rows=1 width=78) (actual time=0.020..0.021 rows=0 loops=1,312)

  • Index Cond: ((attrelid = c_1.oid) AND (attnum > 0))
  • Filter: ((NOT attisdropped) AND (((attname)::information_schema.sql_identifier)::text ~~* '%index%'::text) AND (pg_has_role(c_1.relowner, 'USAGE'::text) OR has_column_privilege(c_1.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
  • Rows Removed by Filter: 21
17. 0.200 0.200 ↑ 1.0 1 100

Index Scan using pg_type_oid_index on pg_type t_1 (cost=0.28..4.66 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (oid = a.atttypid)
18. 0.100 0.200 ↓ 0.0 0 100

Nested Loop (cost=0.42..0.60 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=100)

19. 0.100 0.100 ↓ 0.0 0 100

Index Scan using pg_type_oid_index on pg_type bt (cost=0.28..0.42 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=100)

  • Index Cond: (t_1.typbasetype = oid)
20. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (oid = bt.typnamespace)
  • Heap Fetches: 0
21. 3.900 3.900 ↓ 1.2 101 100

Seq Scan on pg_namespace nt_1 (cost=0.00..2.81 rows=81 width=4) (actual time=0.002..0.039 rows=101 loops=100)

22. 120.640 120.640 ↓ 485.0 485 80

Index Scan using pg_class_relname_nsp_index on pg_class c (cost=0.28..72.98 rows=1 width=73) (actual time=0.019..1.508 rows=485 loops=80)

  • Index Cond: (relnamespace = nc.oid)
  • Filter: ((relkind = ANY ('{r,v,f}'::"char"[])) AND (((CASE WHEN (nc.oid = pg_my_temp_schema()) THEN 'LOCAL TEMPORARY'::text WHEN (relkind = 'r'::"char") THEN 'BASE TABLE'::text WHEN (relkind = 'v'::"char") THEN 'VIEW'::text WHEN (relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text ELSE NULL::text END)::information_schema.character_data)::text = 'BASE TABLE'::text) 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: 1,576
23. 38.782 77.564 ↓ 0.0 0 38,782

Nested Loop (cost=0.42..1.02 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=38,782)

24. 38.782 38.782 ↓ 0.0 0 38,782

Index Scan using pg_type_oid_index on pg_type t (cost=0.28..0.85 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=38,782)

  • Index Cond: (c.reloftype = oid)
25. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (oid = t.typnamespace)
  • Heap Fetches: 0
26. 83.630 155.128 ↓ 0.0 0 38,782

Nested Loop (cost=0.28..0.98 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=38,782)

27. 38.782 38.782 ↑ 1.0 1 38,782

Index Scan using pg_collation_oid_index on pg_collation co (cost=0.14..0.40 rows=1 width=72) (actual time=0.001..0.001 rows=1 loops=38,782)

  • Index Cond: (a.attcollation = oid)
28. 32.716 32.716 ↓ 0.0 0 32,716

Index Scan using pg_namespace_oid_index on pg_namespace nco (cost=0.14..0.58 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=32,716)

  • Index Cond: (oid = co.collnamespace)
  • Filter: ((nspname <> 'pg_catalog'::name) OR (co.collname <> 'default'::name))
  • Rows Removed by Filter: 1
Planning time : 1.176 ms
Execution time : 532.755 ms