explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7Cud

Settings
# exclusive inclusive rows x rows loops node
1. 0.059 92,544.215 ↓ 12.0 12 1

Nested Loop (cost=720.66..9,400.15 rows=1 width=544) (actual time=13,128.256..92,544.215 rows=12 loops=1)

2. 13.310 92,543.976 ↓ 12.0 12 1

Nested Loop (cost=720.37..9,399.82 rows=1 width=520) (actual time=13,128.227..92,543.976 rows=12 loops=1)

  • Join Filter: ((c_1.conname)::name = ("*SELECT* 1".constraint_name)::name)
  • Rows Removed by Join Filter: 39586
3. 1.164 48.014 ↓ 3,046.0 3,046 1

Subquery Scan on *SELECT* 1 (cost=0.29..386.73 rows=1 width=224) (actual time=27.451..48.014 rows=3,046 loops=1)

4. 12.221 46.850 ↓ 3,046.0 3,046 1

Nested Loop (cost=0.29..386.72 rows=1 width=512) (actual time=27.449..46.850 rows=3,046 loops=1)

  • Join Filter: (c.connamespace = nc.oid)
  • Rows Removed by Join Filter: 12184
5. 4.402 31.583 ↓ 3,046.0 3,046 1

Nested Loop (cost=0.29..385.46 rows=1 width=197) (actual time=27.426..31.583 rows=3,046 loops=1)

  • Join Filter: (r.relnamespace = nr.oid)
  • Rows Removed by Join Filter: 12184
6. 0.036 0.036 ↑ 1.4 5 1

Seq Scan on pg_namespace nr (cost=0.00..1.14 rows=7 width=68) (actual time=0.019..0.036 rows=5 loops=1)

  • Filter: (NOT pg_is_other_temp_schema(oid))
  • Rows Removed by Filter: 6
7. 5.117 27.145 ↓ 1,523.0 3,046 5

Materialize (cost=0.29..384.12 rows=2 width=137) (actual time=0.015..5.429 rows=3,046 loops=5)

8. 3.264 22.028 ↓ 1,523.0 3,046 1

Nested Loop (cost=0.29..384.11 rows=2 width=137) (actual time=0.058..22.028 rows=3,046 loops=1)

9. 3.534 3.534 ↓ 132.4 3,046 1

Seq Scan on pg_constraint c (cost=0.00..328.00 rows=23 width=73) (actual time=0.018..3.534 rows=3,046 loops=1)

  • Filter: ((contype <> ALL ('{t,x}'::"char"[])) AND ((CASE contype WHEN 'c'::"char" THEN 'CHECK'::text WHEN 'f'::"char" THEN 'FOREIGN KEY'::text WHEN 'p'::"char" THEN
  • Rows Removed by Filter: 1594
10. 15.230 15.230 ↑ 1.0 1 3,046

Index Scan using pg_class_oid_index on pg_class r (cost=0.29..2.44 rows=1 width=72) (actual time=0.005..0.005 rows=1 loops=3,046)

  • Index Cond: (oid = c.conrelid)
  • Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TR
11. 3.046 3.046 ↑ 2.2 5 3,046

Seq Scan on pg_namespace nc (cost=0.00..1.11 rows=11 width=4) (actual time=0.001..0.001 rows=5 loops=3,046)

12. 3,357.211 92,482.652 ↓ 6.5 13 3,046

Hash Join (cost=720.09..9,013.06 rows=2 width=360) (actual time=4.912..30.362 rows=13 loops=3,046)

  • Hash Cond: ((c_1.conname)::name = ((("*SELECT* 1_1".conname)::information_schema.sql_identifier))::name)
13. 72,485.662 89,119.868 ↑ 85.7 4,910 3,046

ProjectSet (cost=416.08..2,920.29 rows=421,000 width=341) (actual time=0.010..29.258 rows=4,910 loops=3,046)

14. 3,417.594 16,634.206 ↓ 11.0 4,616 3,046

Hash Join (cost=416.08..707.94 rows=421 width=159) (actual time=0.004..5.461 rows=4,616 loops=3,046)

  • Hash Cond: (c_1.connamespace = nc_1.oid)
15. 3,411.499 13,216.594 ↓ 11.0 4,616 3,046

Hash Join (cost=414.84..705.16 rows=421 width=163) (actual time=0.004..4.339 rows=4,616 loops=3,046)

  • Hash Cond: (r_1.relnamespace = nr_1.oid)
16. 5,626.245 9,805.074 ↓ 7.0 4,616 3,046

Hash Join (cost=413.61..701.53 rows=662 width=167) (actual time=0.004..3.219 rows=4,616 loops=3,046)

  • Hash Cond: (c_1.conrelid = r_1.oid)
17. 4,173.020 4,173.020 ↑ 1.0 4,616 3,046

Seq Scan on pg_constraint c_1 (cost=0.00..275.80 rows=4,616 width=95) (actual time=0.001..1.370 rows=4,616 loops=3,046)

  • Filter: (contype = ANY ('{p,u,f}'::"char"[]))
  • Rows Removed by Filter: 24
18. 0.781 5.809 ↑ 1.0 1,462 1

Hash (cost=395.34..395.34 rows=1,462 width=76) (actual time=5.809..5.809 rows=1,462 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 171kB
19. 5.028 5.028 ↑ 1.0 1,462 1

Seq Scan on pg_class r_1 (cost=0.00..395.34 rows=1,462 width=76) (actual time=0.026..5.028 rows=1,462 loops=1)

  • Filter: (relkind = ANY ('{r,p}'::"char"[]))
  • Rows Removed by Filter: 8726
20. 0.004 0.021 ↑ 1.4 5 1

Hash (cost=1.14..1.14 rows=7 width=4) (actual time=0.021..0.021 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.017 0.017 ↑ 1.4 5 1

Seq Scan on pg_namespace nr_1 (cost=0.00..1.14 rows=7 width=4) (actual time=0.008..0.017 rows=5 loops=1)

  • Filter: (NOT pg_is_other_temp_schema(oid))
  • Rows Removed by Filter: 6
22. 0.007 0.018 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=4) (actual time=0.018..0.018 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.011 0.011 ↑ 1.0 11 1

Seq Scan on pg_namespace nc_1 (cost=0.00..1.11 rows=11 width=4) (actual time=0.007..0.011 rows=11 loops=1)

24. 0.031 5.573 ↓ 6.5 13 1

Hash (cost=303.98..303.98 rows=2 width=192) (actual time=5.573..5.573 rows=13 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
25. 0.045 5.542 ↓ 6.5 13 1

Sort (cost=303.95..303.96 rows=2 width=192) (actual time=5.539..5.542 rows=13 loops=1)

  • Sort Key: (("*SELECT* 1_1".conname)::information_schema.sql_identifier), (("*SELECT* 1_1".relname)::information_schema.sql_identifier), (("*SELECT* 1_1".attname)::information_schema.sql_identifier)
  • Sort Method: quicksort Memory: 28kB
26. 0.009 5.497 ↓ 6.5 13 1

Result (cost=9.62..303.94 rows=2 width=192) (actual time=1.314..5.497 rows=13 loops=1)

27. 0.009 5.488 ↓ 6.5 13 1

Append (cost=9.62..303.92 rows=2 width=192) (actual time=1.311..5.488 rows=13 loops=1)

28. 0.001 0.390 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1_1 (cost=9.62..9.65 rows=1 width=192) (actual time=0.390..0.390 rows=0 loops=1)

29. 0.001 0.389 ↓ 0.0 0 1

Unique (cost=9.62..9.64 rows=1 width=324) (actual time=0.389..0.389 rows=0 loops=1)

30. 0.012 0.388 ↓ 0.0 0 1

Sort (cost=9.62..9.62 rows=1 width=324) (actual time=0.388..0.388 rows=0 loops=1)

  • Sort Key: nr_2.nspname, r_2.relname, r_2.relowner, a_1.attname, nc_2.nspname, c_2.conname
  • Sort Method: quicksort Memory: 25kB
31. 0.001 0.376 ↓ 0.0 0 1

Nested Loop (cost=1.41..9.61 rows=1 width=324) (actual time=0.376..0.376 rows=0 loops=1)

32. 0.005 0.375 ↓ 0.0 0 1

Nested Loop (cost=1.27..9.29 rows=1 width=264) (actual time=0.375..0.375 rows=0 loops=1)

33. 0.019 0.268 ↓ 17.0 17 1

Nested Loop (cost=0.99..8.94 rows=1 width=200) (actual time=0.113..0.268 rows=17 loops=1)

  • Join Filter: (r_2.oid = a_1.attrelid)
34. 0.013 0.147 ↓ 17.0 17 1

Nested Loop (cost=0.70..8.36 rows=1 width=148) (actual time=0.084..0.147 rows=17 loops=1)

35. 0.004 0.046 ↑ 1.0 1 1

Nested Loop (cost=0.29..3.66 rows=1 width=136) (actual time=0.042..0.046 rows=1 loops=1)

  • Join Filter: (nr_2.oid = r_2.relnamespace)
36. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on pg_namespace nr_2 (cost=0.00..1.14 rows=1 width=68) (actual time=0.009..0.012 rows=1 loops=1)

  • Filter: ((nspname)::name = 'public'::name)
  • Rows Removed by Filter: 10
37. 0.030 0.030 ↑ 1.0 1 1

Index Scan using pg_class_relname_nsp_index on pg_class r_2 (cost=0.29..2.51 rows=1 width=76) (actual time=0.030..0.030 rows=1 loops=1)

  • Index Cond: ((relname)::name = 'd_zaradeniezamestnanecorganu'::name)
  • Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND pg_has_role(relowner, 'USAGE'::text))
38. 0.088 0.088 ↓ 17.0 17 1

Index Scan using pg_depend_reference_index on pg_depend d (cost=0.41..4.69 rows=1 width=12) (actual time=0.037..0.088 rows=17 loops=1)

  • Index Cond: ((refclassid = '1259'::oid) AND (refobjid = r_2.oid))
  • Filter: (classid = '2606'::oid)
  • Rows Removed by Filter: 23
39. 0.102 0.102 ↑ 1.0 1 17

Index Scan using pg_attribute_relid_attnum_index on pg_attribute a_1 (cost=0.29..0.57 rows=1 width=70) (actual time=0.006..0.006 rows=1 loops=17)

  • Index Cond: ((attrelid = d.refobjid) AND (attnum = d.refobjsubid))
  • Filter: (NOT attisdropped)
40. 0.102 0.102 ↓ 0.0 0 17

Index Scan using pg_constraint_oid_index on pg_constraint c_2 (cost=0.28..0.35 rows=1 width=72) (actual time=0.006..0.006 rows=0 loops=17)

  • Index Cond: (oid = d.objid)
  • Filter: (contype = 'c'::"char")
  • Rows Removed by Filter: 1
41. 0.000 0.000 ↓ 0.0 0

Index Scan using pg_namespace_oid_index on pg_namespace nc_2 (cost=0.14..0.29 rows=1 width=68) (never executed)

  • Index Cond: (oid = c_2.connamespace)
42. 0.009 5.089 ↓ 13.0 13 1

Subquery Scan on *SELECT* 2 (cost=2.81..294.26 rows=1 width=192) (actual time=0.917..5.089 rows=13 loops=1)

43. 0.064 5.080 ↓ 13.0 13 1

Nested Loop (cost=2.81..294.25 rows=1 width=324) (actual time=0.916..5.080 rows=13 loops=1)

  • Join Filter: (c_3.connamespace = nc_3.oid)
  • Rows Removed by Join Filter: 52
44. 0.035 4.990 ↓ 13.0 13 1

Nested Loop (cost=2.81..293.01 rows=1 width=196) (actual time=0.904..4.990 rows=13 loops=1)

  • Join Filter: (r_3.oid = a_2.attrelid)
45. 0.049 4.643 ↓ 13.0 13 1

Nested Loop (cost=2.52..292.16 rows=1 width=191) (actual time=0.862..4.643 rows=13 loops=1)

  • Join Filter: (r_3.relnamespace = nr_3.oid)
46. 1.932 4.555 ↓ 13.0 13 1

Hash Join (cost=2.52..291.01 rows=1 width=195) (actual time=0.848..4.555 rows=13 loops=1)

  • Hash Cond: (CASE c_3.contype WHEN 'f'::"char" THEN c_3.confrelid ELSE c_3.conrelid END = r_3.oid)
47. 2.590 2.590 ↑ 1.0 4,616 1

Seq Scan on pg_constraint c_3 (cost=0.00..275.80 rows=4,616 width=123) (actual time=0.011..2.590 rows=4,616 loops=1)

  • Filter: (contype = ANY ('{p,u,f}'::"char"[]))
  • Rows Removed by Filter: 24
48. 0.011 0.033 ↑ 1.0 1 1

Hash (cost=2.51..2.51 rows=1 width=72) (actual time=0.033..0.033 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
49. 0.022 0.022 ↑ 1.0 1 1

Index Scan using pg_class_relname_nsp_index on pg_class r_3 (cost=0.29..2.51 rows=1 width=72) (actual time=0.021..0.022 rows=1 loops=1)

  • Index Cond: ((relname)::name = 'd_zaradeniezamestnanecorganu'::name)
  • Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND pg_has_role(relowner, 'USAGE'::text))
50. 0.039 0.039 ↑ 1.0 1 13

Seq Scan on pg_namespace nr_3 (cost=0.00..1.14 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=13)

  • Filter: ((nspname)::name = 'public'::name)
  • Rows Removed by Filter: 4
51. 0.312 0.312 ↑ 1.0 1 13

Index Scan using pg_attribute_relid_attnum_index on pg_attribute a_2 (cost=0.29..0.83 rows=1 width=70) (actual time=0.014..0.024 rows=1 loops=13)

  • Index Cond: (attrelid = CASE c_3.contype WHEN 'f'::"char" THEN c_3.confrelid ELSE c_3.conrelid END)
  • Filter: ((NOT attisdropped) AND (attnum = ANY (CASE c_3.contype WHEN 'f'::"char" THEN c_3.confkey ELSE c_3.conkey END)))
  • Rows Removed by Filter: 16
52. 0.026 0.026 ↑ 2.2 5 13

Seq Scan on pg_namespace nc_3 (cost=0.00..1.11 rows=11 width=4) (actual time=0.001..0.002 rows=5 loops=13)

53. 0.180 0.180 ↑ 1.0 1 12

Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.29..0.32 rows=1 width=70) (actual time=0.015..0.015 rows=1 loops=12)

  • Index Cond: ((attrelid = r_1.oid) AND (attnum = ((information_schema._pg_expandarray(c_1.conkey))).x))
  • Filter: ((NOT attisdropped) AND (pg_has_role(r_1.relowner, 'USAGE'::text) OR has_column_privilege(r_1.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
Planning time : 11.055 ms
Execution time : 92,545.041 ms