explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2tuk

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=5,303.75..5,305.98 rows=1 width=457) (actual rows= loops=)

  • Group Key: ((c.relname)::information_schema.sql_identifier), ((a.attnum)::information_schema.cardinal_number), ((nc.nspname)::information_schema.sql_identifier), ((CASE WHEN (nc_1.oid = pg_my_temp_schema()) THEN 'LOCAL TEMPORARY'::text WHEN (c_1.relkind = ANY ('{r,p}'::"char"[])) THEN 'BASE TABLE'::text WHEN (c_1.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (c_1.relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text ELSE NULL::text END)::information_schema.character_data), ((a.attname)::information_schema.sql_identifier), t.typtype, ((COALESCE(bt.typname, t_1.typname))::information_schema.sql_identifier), ((information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t_1.*), information_schema._pg_truetypmod(a.*, t_1.*)))::information_schema.cardinal_number), ((pg_get_expr(ad.adbin, ad.adrelid))::information_schema.character_data), ((c_2.conname)::information_schema.sql_identifier)
2. 0.000 0.000 ↓ 0.0

Sort (cost=4,890.70..4,890.71 rows=1 width=1,659) (actual rows= loops=)

  • Sort Key: ((c.relname)::information_schema.sql_identifier), ((a.attnum)::information_schema.cardinal_number), ((nc.nspname)::information_schema.sql_identifier), ((CASE WHEN (nc_1.oid = pg_my_temp_schema()) THEN 'LOCAL TEMPORARY'::text WHEN (c_1.relkind = ANY ('{r,p}'::"char"[])) THEN 'BASE TABLE'::text WHEN (c_1.relkind = 'v'::"char") THEN 'VIEW'::text WHEN (c_1.relkind = 'f'::"char") THEN 'FOREIGN TABLE'::text ELSE NULL::text END)::information_schema.character_data), ((a.attname)::information_schema.sql_identifier), t.typtype, ((COALESCE(bt.typname, t_1.typname))::information_schema.sql_identifier), ((information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t_1.*), information_schema._pg_truetypmod(a.*, t_1.*)))::information_schema.cardinal_number), ((pg_get_expr(ad.adbin, ad.adrelid))::information_schema.character_data), ((c_2.conname)::information_schema.sql_identifier)
3. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,342.72..4,890.69 rows=1 width=1,659) (actual rows= loops=)

  • Hash Cond: ((((nr.nspname)::information_schema.sql_identifier)::text = ((nc.nspname)::information_schema.sql_identifier)::text) AND (((r.relname)::information_schema.sql_identifier)::text = ((c.relname)::information_schema.sql_identifier)::text) AND (((a_1.attname)::information_schema.sql_identifier)::text = ((a.attname)::information_schema.sql_identifier)::text))
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,571.57..4,061.95 rows=2,163 width=256) (actual rows= loops=)

  • Hash Cond: ((r.oid = a_1.attrelid) AND (((information_schema._pg_expandarray(c_2.conkey))).x = a_1.attnum))
  • Join Filter: (pg_has_role(r.relowner, 'USAGE'::text) OR has_column_privilege(r.oid, a_1.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text))
5. 0.000 0.000 ↓ 0.0

ProjectSet (cost=164.91..598.29 rows=68,000 width=341) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=164.91..240.95 rows=68 width=223) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=164.77..228.98 rows=68 width=227) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Hash Join (cost=164.63..203.23 rows=103 width=167) (actual rows= loops=)

  • Hash Cond: (c_2.conrelid = r.oid)
9. 0.000 0.000 ↓ 0.0

Seq Scan on pg_constraint c_2 (cost=0.00..30.21 rows=665 width=95) (actual rows= loops=)

  • Filter: (contype = ANY ('{p,u,f}'::"char"[]))
10. 0.000 0.000 ↓ 0.0

Hash (cost=159.19..159.19 rows=435 width=76) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Seq Scan on pg_class r (cost=0.00..159.19 rows=435 width=76) (actual rows= loops=)

  • Filter: (relkind = ANY ('{r,p}'::"char"[]))
12. 0.000 0.000 ↓ 0.0

Index Scan using pg_namespace_oid_index on pg_namespace nr (cost=0.14..0.25 rows=1 width=68) (actual rows= loops=)

  • Index Cond: (oid = r.relnamespace)
  • Filter: (NOT pg_is_other_temp_schema(oid))
13. 0.000 0.000 ↓ 0.0

Index Only Scan using pg_namespace_oid_index on pg_namespace nc_2 (cost=0.14..0.18 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (oid = c_2.connamespace)
14. 0.000 0.000 ↓ 0.0

Hash (cost=932.10..932.10 rows=31,637 width=70) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on pg_attribute a_1 (cost=0.00..932.10 rows=31,637 width=70) (actual rows= loops=)

  • Filter: (NOT attisdropped)
16. 0.000 0.000 ↓ 0.0

Hash (cost=771.14..771.14 rows=1 width=1,363) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=524.62..771.14 rows=1 width=1,363) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Join (cost=524.48..770.88 rows=1 width=1,303) (actual rows= loops=)

  • Hash Cond: ((((nc.nspname)::information_schema.sql_identifier)::text = ((nc_1.nspname)::information_schema.sql_identifier)::text) AND (((c.relname)::information_schema.sql_identifier)::text = ((c_1.relname)::information_schema.sql_identifier)::text))
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=65.74..308.56 rows=53 width=1,298) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=65.46..294.93 rows=38 width=1,293) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=65.04..274.50 rows=38 width=1,297) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=64.60..223.28 rows=38 width=1,301) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=64.46..217.02 rows=38 width=1,305) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=64.19..205.86 rows=38 width=856) (actual rows= loops=)

  • Join Filter: (t_1.typtype = 'd'::"char")
25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=63.77..186.27 rows=38 width=797) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=63.49..174.83 rows=38 width=439) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=63.20..161.56 rows=8 width=136) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on pg_namespace nc (cost=0.00..34.96 rows=1 width=68) (actual rows= loops=)

  • Filter: ((NOT pg_is_other_temp_schema(oid)) AND (((nspname)::information_schema.sql_identifier)::text ~~ '_11_conception'::text))
29. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on pg_class c (cost=63.20..126.39 rows=21 width=76) (actual rows= loops=)

  • Recheck Cond: (relnamespace = nc.oid)
  • Filter: (relkind = ANY ('{r,v,f,p}'::"char"[]))
30. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on pg_class_relname_nsp_index (cost=0.00..63.19 rows=78 width=0) (actual rows= loops=)

  • Index Cond: (relnamespace = nc.oid)
31. 0.000 0.000 ↓ 0.0

Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.29..1.61 rows=5 width=307) (actual rows= loops=)

  • Index Cond: ((attrelid = c.oid) AND (attnum > 0))
  • Filter: ((NOT attisdropped) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_column_privilege(c.oid, attnum, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
32. 0.000 0.000 ↓ 0.0

Index Scan using pg_type_oid_index on pg_type t_1 (cost=0.28..0.30 rows=1 width=366) (actual rows= loops=)

  • Index Cond: (oid = a.atttypid)
33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..0.50 rows=1 width=68) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Index Scan using pg_type_oid_index on pg_type bt (cost=0.28..0.34 rows=1 width=72) (actual rows= loops=)

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

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

  • Index Cond: (oid = bt.typnamespace)
36. 0.000 0.000 ↓ 0.0

Index Scan using pg_attrdef_adrelid_adnum_index on pg_attrdef ad (cost=0.27..0.29 rows=1 width=455) (actual rows= loops=)

  • Index Cond: ((a.attrelid = adrelid) AND (a.attnum = adnum))
37. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (oid = t_1.typnamespace)
38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.43..1.34 rows=1 width=8) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Index Scan using pg_depend_reference_index on pg_depend dep (cost=0.29..1.15 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((refclassid = '1259'::oid) AND (refobjid = c.oid) AND (refobjsubid = a.attnum))
  • Filter: ((classid = '1259'::oid) AND (deptype = 'i'::"char"))
40. 0.000 0.000 ↓ 0.0

Index Only Scan using pg_sequence_seqrelid_index on pg_sequence seq (cost=0.14..0.18 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (seqrelid = dep.objid)
41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..0.53 rows=1 width=4) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Index Scan using pg_collation_oid_index on pg_collation co (cost=0.28..0.29 rows=1 width=72) (actual rows= loops=)

  • Index Cond: (a.attcollation = oid)
43. 0.000 0.000 ↓ 0.0

Index Scan using pg_namespace_oid_index on pg_namespace nco (cost=0.14..0.22 rows=1 width=68) (actual rows= loops=)

  • Index Cond: (oid = co.collnamespace)
  • Filter: ((nspname <> 'pg_catalog'::name) OR (co.collname <> 'default'::name))
44. 0.000 0.000 ↓ 0.0

Index Scan using pg_type_typname_nsp_index on pg_type t (cost=0.29..0.35 rows=1 width=69) (actual rows= loops=)

  • Index Cond: (((COALESCE(bt.typname, t_1.typname))::information_schema.sql_identifier)::name = typname)
45. 0.000 0.000 ↓ 0.0

Hash (cost=453.48..453.48 rows=350 width=133) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=191.39..453.48 rows=350 width=133) (actual rows= loops=)

  • Hash Cond: (c_1.reloftype = t_2.oid)
47. 0.000 0.000 ↓ 0.0

Hash Join (cost=35.04..292.32 rows=350 width=137) (actual rows= loops=)

  • Hash Cond: (c_1.relnamespace = nc_1.oid)
48. 0.000 0.000 ↓ 0.0

Seq Scan on pg_class c_1 (cost=0.00..250.68 rows=527 width=73) (actual rows= loops=)

  • Filter: ((relkind = ANY ('{r,v,f,p}'::"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)))
49. 0.000 0.000 ↓ 0.0

Hash (cost=34.23..34.23 rows=65 width=68) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Seq Scan on pg_namespace nc_1 (cost=0.00..34.23 rows=65 width=68) (actual rows= loops=)

  • Filter: (NOT pg_is_other_temp_schema(oid))
51. 0.000 0.000 ↓ 0.0

Hash (cost=128.34..128.34 rows=2,241 width=4) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Hash Join (cost=13.84..128.34 rows=2,241 width=4) (actual rows= loops=)

  • Hash Cond: (t_2.typnamespace = nt_1.oid)
53. 0.000 0.000 ↓ 0.0

Seq Scan on pg_type t_2 (cost=0.00..86.41 rows=2,241 width=8) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Hash (cost=12.61..12.61 rows=98 width=4) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Index Only Scan using pg_namespace_oid_index on pg_namespace nt_1 (cost=0.14..12.61 rows=98 width=4) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Index Only Scan using pg_enum_typid_label_index on pg_enum enu (cost=0.14..0.21 rows=4 width=68) (actual rows= loops=)

  • Index Cond: (enumtypid = t.oid)
57.          

SubPlan (for GroupAggregate)

58. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.98..412.76 rows=117 width=160) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..383.40 rows=118 width=72) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Merge Join (cost=0.56..284.59 rows=237 width=72) (actual rows= loops=)

  • Merge Cond: (x.indrelid = c_3.oid)
61. 0.000 0.000 ↓ 0.0

Index Scan using pg_index_indrelid_index on pg_index x (cost=0.28..66.31 rows=1,383 width=8) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Index Scan using pg_class_oid_index on pg_class c_3 (cost=0.28..211.24 rows=483 width=72) (actual rows= loops=)

  • Filter: (relkind = ANY ('{r,m}'::"char"[]))
63. 0.000 0.000 ↓ 0.0

Index Scan using pg_class_oid_index on pg_class i (cost=0.28..0.42 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (oid = x.indexrelid)
  • Filter: (relkind = 'i'::"char")
64. 0.000 0.000 ↓ 0.0

Index Scan using pg_namespace_oid_index on pg_namespace n (cost=0.14..0.24 rows=1 width=68) (actual rows= loops=)

  • Index Cond: (oid = c_3.relnamespace)" Filter: (nspname !~~ 'pg_catalog'::text)