explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aeYz

Settings
# exclusive inclusive rows x rows loops node
1. 3.772 2,428.795 ↑ 1.0 1 1

Aggregate (cost=1,763.62..1,763.63 rows=1 width=32) (actual time=2,428.766..2,428.795 rows=1 loops=1)

2. 4.033 2,425.023 ↑ 3.3 134 1

Subquery Scan on info (cost=1,393.76..1,761.42 rows=439 width=152) (actual time=1,686.655..2,425.023 rows=134 loops=1)

3. 593.405 2,420.990 ↑ 3.3 134 1

GroupAggregate (cost=1,393.76..1,757.03 rows=439 width=324) (actual time=1,686.623..2,420.990 rows=134 loops=1)

  • Group Key: pgc.oid, pgn.nspname, pgc.relname, (CASE WHEN (pgc.relkind = 'r'::""char"") THEN 'TABLE'::text WHEN (pgc.relkind = 'f'::""char"") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::""char"") THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::""char"") THEN 'MATERIALIZED VIEW'::text WHEN (pgc.relkind = 'p'::""char"") THEN 'PARTITIONED TABLE'::text ELSE NULL::text END), (ROW(current_database(), nc_1.nspname, c_1.relname, CASE WHEN pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE NULL::text END, CASE WHEN ('check_option=cascaded'::text = ANY (c_1.reloptions)) THEN 'CASCADED'::text WHEN ('check_option=local'::text = ANY (c_1.reloptions)) THEN 'LOCAL'::text ELSE 'NONE'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN 'YES'::text ELSE 'NO'::text END))
4. 289.660 1,827.585 ↓ 22.1 9,711 1

Sort (cost=1,393.76..1,394.86 rows=439 width=454) (actual time=1,685.792..1,827.585 rows=9,711 loops=1)

  • Sort Key: pgc.oid, pgc.relname, (CASE WHEN (pgc.relkind = 'r'::""char"") THEN 'TABLE'::text WHEN (pgc.relkind = 'f'::""char"") THEN 'FOREIGN TABLE'::text WHEN (pgc.relkind = 'v'::""char"") THEN 'VIEW'::text WHEN (pgc.relkind = 'm'::""char"") THEN 'MATERIALIZED VIEW'::text WHEN (pgc.relkind = 'p'::""char"") THEN 'PARTITIONED TABLE'::text ELSE NULL::text END), (ROW(current_database(), nc_1.nspname, c_1.relname, CASE WHEN pg_has_role(c_1.relowner, 'USAGE'::text) THEN pg_get_viewdef(c_1.oid) ELSE NULL::text END, CASE WHEN ('check_option=cascaded'::text = ANY (c_1.reloptions)) THEN 'CASCADED'::text WHEN ('check_option=local'::text = ANY (c_1.reloptions)) THEN 'LOCAL'::text ELSE 'NONE'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 20) = 20) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN ((pg_relation_is_updatable((c_1.oid)::regclass, false) & 8) = 8) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 1 or hashed SubPlan 2) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 3 or hashed SubPlan 4) THEN 'YES'::text ELSE 'NO'::text END, CASE WHEN (alternatives: SubPlan 5 or hashed SubPlan 6) THEN 'YES'::text ELSE 'NO'::text END))
  • Sort Method: external merge Disk: 4,800kB
5. 133.700 1,537.925 ↓ 22.1 9,711 1

Hash Right Join (cost=590.61..1,374.49 rows=439 width=454) (actual time=1,404.248..1,537.925 rows=9,711 loops=1)

  • Hash Cond: ((nc_1.nspname = pgn.nspname) AND (c_1.relname = pgc.relname))
6. 1.716 4.085 ↓ 0.0 0 1

Nested Loop (cost=0.00..778.21 rows=15 width=160) (actual time=4.072..4.085 rows=0 loops=1)

  • Join Filter: (nc_1.oid = c_1.relnamespace)
  • Rows Removed by Join Filter: 130
7. 0.073 0.073 ↑ 1.0 1 1

Seq Scan on pg_namespace nc_1 (cost=0.00..1.09 rows=1 width=68) (actual time=0.039..0.073 rows=1 loops=1)

  • Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'public'::name))
  • Rows Removed by Filter: 7
8. 2.296 2.296 ↓ 1.4 130 1

Seq Scan on pg_class c_1 (cost=0.00..64.48 rows=92 width=125) (actual time=0.034..2.296 rows=130 loops=1)

  • Filter: ((relkind = ANY ('{v,m}'::""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: 799
9.          

SubPlan (for Nested Loop)

10. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on pg_trigger (cost=4.32..15.78 rows=1 width=0) (never executed)

  • Recheck Cond: (tgrelid = c_1.oid)
  • Filter: (((tgtype)::integer & 81) = 81)
11. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pg_trigger_tgrelid_tgname_index (cost=0.00..4.32 rows=6 width=0) (never executed)

  • Index Cond: (tgrelid = c_1.oid)
12. 0.000 0.000 ↓ 0.0 0

Seq Scan on pg_trigger pg_trigger_1 (cost=0.00..27.06 rows=3 width=4) (never executed)

  • Filter: (((tgtype)::integer & 81) = 81)
13. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on pg_trigger pg_trigger_2 (cost=4.32..15.78 rows=1 width=0) (never executed)

  • Recheck Cond: (tgrelid = c_1.oid)
  • Filter: (((tgtype)::integer & 73) = 73)
14. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pg_trigger_tgrelid_tgname_index (cost=0.00..4.32 rows=6 width=0) (never executed)

  • Index Cond: (tgrelid = c_1.oid)
15. 0.000 0.000 ↓ 0.0 0

Seq Scan on pg_trigger pg_trigger_3 (cost=0.00..27.06 rows=3 width=4) (never executed)

  • Filter: (((tgtype)::integer & 73) = 73)
16. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on pg_trigger pg_trigger_4 (cost=4.32..15.78 rows=1 width=0) (never executed)

  • Recheck Cond: (tgrelid = c_1.oid)
  • Filter: (((tgtype)::integer & 69) = 69)
17. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pg_trigger_tgrelid_tgname_index (cost=0.00..4.32 rows=6 width=0) (never executed)

  • Index Cond: (tgrelid = c_1.oid)
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on pg_trigger pg_trigger_5 (cost=0.00..27.06 rows=3 width=4) (never executed)

  • Filter: (((tgtype)::integer & 69) = 69)
19. 145.101 1,400.140 ↓ 22.1 9,711 1

Hash (cost=584.03..584.03 rows=439 width=391) (actual time=1,400.127..1,400.140 rows=9,711 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 2 (originally 1) Memory Usage: 4,033kB
20. 148.862 1,255.039 ↓ 22.1 9,711 1

Hash Right Join (cost=390.27..584.03 rows=439 width=391) (actual time=887.445..1,255.039 rows=9,711 loops=1)

  • Hash Cond: ((nc.nspname = pgn.nspname) AND (c.relname = pgc.relname) AND (a.attname = pga.attname))
21. 39.445 361.048 ↓ 7.6 1,139 1

Hash Left Join (cost=147.05..339.10 rows=150 width=224) (actual time=142.276..361.048 rows=1,139 loops=1)

  • Hash Cond: (a.attcollation = co.oid)
22. 29.710 281.637 ↓ 7.6 1,139 1

Hash Join (cost=103.31..281.87 rows=150 width=862) (actual time=102.249..281.637 rows=1,139 loops=1)

  • Hash Cond: (t.typnamespace = nt.oid)
23. 29.691 251.642 ↓ 7.6 1,139 1

Hash Left Join (cost=102.18..280.06 rows=150 width=802) (actual time=101.922..251.642 rows=1,139 loops=1)

  • Hash Cond: ((a.attrelid = ad.adrelid) AND (a.attnum = ad.adnum))
24. 29.629 218.421 ↓ 7.6 1,139 1

Hash Left Join (cost=90.80..267.90 rows=150 width=426) (actual time=98.352..218.421 rows=1,139 loops=1)

  • Hash Cond: (t.typbasetype = bt.oid)
  • Join Filter: (t.typtype = 'd'::""char"")
25. 30.237 123.871 ↓ 7.6 1,139 1

Hash Join (cost=43.52..218.18 rows=150 width=292) (actual time=33.388..123.871 rows=1,139 loops=1)

  • Hash Cond: (a.atttypid = t.oid)
26. 32.678 66.429 ↓ 7.6 1,139 1

Nested Loop (cost=1.39..175.65 rows=150 width=212) (actual time=6.143..66.429 rows=1,139 loops=1)

27. 6.881 12.177 ↓ 2.2 134 1

Hash Join (cost=1.10..42.81 rows=60 width=137) (actual time=6.064..12.177 rows=134 loops=1)

  • Hash Cond: (c.relnamespace = nc.oid)
28. 5.205 5.205 ↑ 1.0 356 1

Seq Scan on pg_class c (cost=0.00..40.10 rows=358 width=77) (actual time=0.025..5.205 rows=356 loops=1)

  • Filter: (relkind = ANY ('{r,v,m,f,p}'::""char""[]))
  • Rows Removed by Filter: 573
29. 0.038 0.091 ↑ 1.0 1 1

Hash (cost=1.09..1.09 rows=1 width=68) (actual time=0.078..0.091 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
30. 0.053 0.053 ↑ 1.0 1 1

Seq Scan on pg_namespace nc (cost=0.00..1.09 rows=1 width=68) (actual time=0.025..0.053 rows=1 loops=1)

  • Filter: ((NOT pg_is_other_temp_schema(oid)) AND (nspname = 'public'::name))
  • Rows Removed by Filter: 7
31. 21.574 21.574 ↓ 2.7 8 134

Index Scan using pg_attribute_relid_attnum_index on pg_attribute a (cost=0.28..2.18 rows=3 width=79) (actual time=0.027..0.161 rows=8 loops=134)

  • 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. 13.873 27.205 ↑ 1.0 895 1

Hash (cost=30.95..30.95 rows=895 width=84) (actual time=27.192..27.205 rows=895 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 115kB
33. 13.332 13.332 ↑ 1.0 895 1

Seq Scan on pg_type t (cost=0.00..30.95 rows=895 width=84) (actual time=0.028..13.332 rows=895 loops=1)

34. 19.574 64.921 ↑ 1.0 895 1

Hash (cost=36.09..36.09 rows=895 width=138) (actual time=64.908..64.921 rows=895 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 157kB
35. 29.841 45.347 ↑ 1.0 895 1

Hash Join (cost=1.14..36.09 rows=895 width=138) (actual time=0.370..45.347 rows=895 loops=1)

  • Hash Cond: (bt.typnamespace = nbt.oid)
36. 15.205 15.205 ↑ 1.0 895 1

Seq Scan on pg_type bt (cost=0.00..30.95 rows=895 width=78) (actual time=0.030..15.205 rows=895 loops=1)

37. 0.149 0.301 ↓ 1.3 8 1

Hash (cost=1.06..1.06 rows=6 width=68) (actual time=0.288..0.301 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
38. 0.152 0.152 ↓ 1.3 8 1

Seq Scan on pg_namespace nbt (cost=0.00..1.06 rows=6 width=68) (actual time=0.025..0.152 rows=8 loops=1)

39. 1.785 3.530 ↑ 1.0 135 1

Hash (cost=9.35..9.35 rows=135 width=382) (actual time=3.519..3.530 rows=135 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 63kB
40. 1.745 1.745 ↑ 1.0 135 1

Seq Scan on pg_attrdef ad (cost=0.00..9.35 rows=135 width=382) (actual time=0.025..1.745 rows=135 loops=1)

41. 0.145 0.285 ↓ 1.3 8 1

Hash (cost=1.06..1.06 rows=6 width=68) (actual time=0.273..0.285 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
42. 0.140 0.140 ↓ 1.3 8 1

Seq Scan on pg_namespace nt (cost=0.00..1.06 rows=6 width=68) (actual time=0.029..0.140 rows=8 loops=1)

43. 9.880 39.966 ↑ 1.0 704 1

Hash (cost=34.92..34.92 rows=705 width=132) (actual time=39.953..39.966 rows=704 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 121kB
44. 19.981 30.086 ↑ 1.0 704 1

Hash Join (cost=1.14..34.92 rows=705 width=132) (actual time=0.333..30.086 rows=704 loops=1)

  • Hash Cond: (co.collnamespace = nco.oid)
  • Join Filter: ((nco.nspname <> 'pg_catalog'::name) OR (co.collname <> 'default'::name))
  • Rows Removed by Join Filter: 1
45. 9.851 9.851 ↑ 1.0 705 1

Seq Scan on pg_collation co (cost=0.00..30.05 rows=705 width=72) (actual time=0.022..9.851 rows=705 loops=1)

46. 0.125 0.254 ↓ 1.3 8 1

Hash (cost=1.06..1.06 rows=6 width=68) (actual time=0.241..0.254 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
47. 0.129 0.129 ↓ 1.3 8 1

Seq Scan on pg_namespace nco (cost=0.00..1.06 rows=6 width=68) (actual time=0.019..0.129 rows=8 loops=1)

48. 168.347 745.129 ↓ 22.1 9,711 1

Hash (cost=235.54..235.54 rows=439 width=359) (actual time=745.116..745.129 rows=9,711 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,514kB
49. 319.857 576.782 ↓ 22.1 9,711 1

Nested Loop Left Join (cost=74.90..235.54 rows=439 width=359) (actual time=18.216..576.782 rows=9,711 loops=1)

50. 24.779 75.930 ↓ 11.4 683 1

Hash Left Join (cost=74.62..100.44 rows=60 width=289) (actual time=18.152..75.930 rows=683 loops=1)

  • Hash Cond: ((pgn.nspname = (ist.event_object_schema)::name) AND (pgc.relname = (ist.event_object_table)::name) AND (pgt.tgname = (ist.trigger_name)::name))
51. 23.538 49.741 ↓ 11.4 683 1

Hash Right Join (cost=43.54..68.45 rows=60 width=201) (actual time=16.700..49.741 rows=683 loops=1)

  • Hash Cond: (pgt.tgrelid = pgc.oid)
52. 10.730 10.730 ↑ 1.0 689 1

Seq Scan on pg_trigger pgt (cost=0.00..21.89 rows=689 width=72) (actual time=0.022..10.730 rows=689 loops=1)

53. 2.312 15.473 ↓ 2.2 134 1

Hash (cost=42.79..42.79 rows=60 width=133) (actual time=15.459..15.473 rows=134 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 30kB
54. 7.323 13.161 ↓ 2.2 134 1

Hash Join (cost=1.09..42.79 rows=60 width=133) (actual time=7.335..13.161 rows=134 loops=1)

  • Hash Cond: (pgc.relnamespace = pgn.oid)
55. 5.752 5.752 ↑ 1.0 356 1

Seq Scan on pg_class pgc (cost=0.00..40.10 rows=358 width=73) (actual time=0.029..5.752 rows=356 loops=1)

  • Filter: (relkind = ANY ('{r,v,f,m,p}'::""char""[]))
  • Rows Removed by Filter: 573
56. 0.038 0.086 ↑ 1.0 1 1

Hash (cost=1.07..1.07 rows=1 width=68) (actual time=0.073..0.086 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
57. 0.048 0.048 ↑ 1.0 1 1

Seq Scan on pg_namespace pgn (cost=0.00..1.07 rows=1 width=68) (actual time=0.022..0.048 rows=1 loops=1)

  • Filter: (nspname = 'public'::name)
  • Rows Removed by Filter: 7
58. 0.024 1.410 ↓ 0.0 0 1

Hash (cost=31.06..31.06 rows=1 width=216) (actual time=1.398..1.410 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
59. 0.058 1.386 ↓ 0.0 0 1

Subquery Scan on ist (cost=30.57..31.06 rows=1 width=216) (actual time=1.373..1.386 rows=0 loops=1)

  • Filter: ((ist.event_object_schema)::name = 'public'::name)
  • Rows Removed by Filter: 2
60. 0.494 1.328 ↑ 1.5 2 1

WindowAgg (cost=30.57..31.02 rows=3 width=576) (actual time=1.157..1.328 rows=2 loops=1)

61. 0.100 0.834 ↑ 1.5 2 1

Sort (cost=30.57..30.57 rows=3 width=382) (actual time=0.792..0.834 rows=2 loops=1)

  • Sort Key: n.oid, c_2.oid, ""*VALUES*"".column1, (((t_1.tgtype)::integer & 1)), (((t_1.tgtype)::integer & 66)), t_1.tgname
  • Sort Method: quicksort Memory: 25kB
62. 0.156 0.734 ↑ 1.5 2 1

Nested Loop (cost=0.41..30.54 rows=3 width=382) (actual time=0.232..0.734 rows=2 loops=1)

  • Join Filter: (((t_1.tgtype)::integer & ""*VALUES*"".column1) <> 0)
  • Rows Removed by Join Filter: 1
63. 0.117 0.506 ↑ 1.0 1 1

Nested Loop (cost=0.41..30.42 rows=1 width=338) (actual time=0.178..0.506 rows=1 loops=1)

64. 0.117 0.346 ↑ 1.0 1 1

Nested Loop (cost=0.28..30.24 rows=1 width=274) (actual time=0.106..0.346 rows=1 loops=1)

65. 0.174 0.174 ↑ 1.0 1 1

Seq Scan on pg_trigger t_1 (cost=0.00..21.89 rows=1 width=202) (actual time=0.022..0.174 rows=1 loops=1)

  • Filter: (NOT tgisinternal)
  • Rows Removed by Filter: 688
66. 0.055 0.055 ↑ 1.0 1 1

Index Scan using pg_class_oid_index on pg_class c_2 (cost=0.28..8.32 rows=1 width=76) (actual time=0.041..0.055 rows=1 loops=1)

  • Index Cond: (oid = t_1.tgrelid)
  • Filter: (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(oid, 'INSERT, UPDATE, REFERENCES'::text))
67. 0.043 0.043 ↑ 1.0 1 1

Index Scan using pg_namespace_oid_index on pg_namespace n (cost=0.13..0.17 rows=1 width=68) (actual time=0.029..0.043 rows=1 loops=1)

  • Index Cond: (oid = c_2.relnamespace)
  • Filter: (NOT pg_is_other_temp_schema(oid))
68. 0.072 0.072 ↑ 1.0 3 1

Values Scan on ""*VALUES*"" (cost=0.00..0.04 rows=3 width=36) (actual time=0.013..0.072 rows=3 loops=1)

69. 180.995 180.995 ↓ 2.0 14 683

Index Scan using pg_attribute_relid_attnum_index on pg_attribute pga (cost=0.28..2.18 rows=7 width=70) (actual time=0.024..0.265 rows=14 loops=683)

  • Index Cond: (attrelid = pgc.oid)
Planning time : 25.605 ms
Execution time : 2,434.778 ms