explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4GY

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 13,703.606 ↑ 1,088.0 1 1

Unique (cost=10,454,002.83..10,454,010.99 rows=1,088 width=36) (actual time=13,703.605..13,703.606 rows=1 loops=1)

2. 0.012 13,703.605 ↑ 1,088.0 1 1

Sort (cost=10,454,002.83..10,454,005.55 rows=1,088 width=36) (actual time=13,703.605..13,703.605 rows=1 loops=1)

  • Sort Key: (get_translation_immutable(pj.name, 'en_US'::character varying)), r.project
  • Sort Method: quicksort Memory: 25kB
3. 0.023 13,703.593 ↑ 1,088.0 1 1

HashAggregate (cost=10,453,665.08..10,453,947.96 rows=1,088 width=36) (actual time=13,703.583..13,703.593 rows=1 loops=1)

  • Group Key: get_translation_immutable(pj.name, 'en_US'::character varying), r.project
4. 0.293 13,703.570 ↑ 12,871,043.7 3 1

Hash Join (cost=102,711.53..10,260,599.42 rows=38,613,131 width=36) (actual time=13,680.691..13,703.570 rows=3 loops=1)

  • Hash Cond: (jpr.reset_space = rsg.pk_val)
5. 0.030 81.321 ↑ 9,960.5 76 1

Nested Loop (cost=46.81..55,897.13 rows=757,000 width=40) (actual time=17.127..81.321 rows=76 loops=1)

6. 0.054 0.054 ↑ 58.2 13 1

Seq Scan on tb_map_changeset mcs (cost=0.00..21.57 rows=757 width=4) (actual time=0.007..0.054 rows=13 loops=1)

7. 0.063 81.237 ↑ 166.7 6 13

Hash Join (cost=46.81..110.56 rows=1,000 width=40) (actual time=6.198..6.249 rows=6 loops=13)

  • Hash Cond: (jpr.reset = r.reset)
8. 0.124 80.808 ↑ 166.7 6 13

Nested Loop (cost=10.29..50.29 rows=1,000 width=120) (actual time=6.163..6.216 rows=6 loops=13)

9.          

CTE tt_object_build

10. 79.898 79.898 ↑ 166.7 6 13

Function Scan on fn_get_map_object_change_helper (cost=0.29..10.29 rows=1,000 width=36) (actual time=6.145..6.146 rows=6 loops=13)

11. 79.937 79.937 ↑ 166.7 6 13

CTE Scan on tt_object_build tt (cost=0.00..20.00 rows=1,000 width=32) (actual time=6.146..6.149 rows=6 loops=13)

12. 0.747 0.747 ↑ 1.0 1 83

Function Scan on jsonb_populate_record jpr (cost=0.00..0.01 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=83)

13. 0.028 0.366 ↑ 2.1 212 1

Hash (cost=31.03..31.03 rows=439 width=40) (actual time=0.366..0.366 rows=212 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
14. 0.046 0.338 ↑ 2.1 212 1

Hash Join (cost=13.12..31.03 rows=439 width=40) (actual time=0.159..0.338 rows=212 loops=1)

  • Hash Cond: (r.project = pj.project)
15. 0.157 0.157 ↑ 2.1 212 1

Seq Scan on tb_reset r (cost=0.00..12.39 rows=439 width=8) (actual time=0.021..0.157 rows=212 loops=1)

16. 0.003 0.135 ↑ 34.0 8 1

Hash (cost=9.72..9.72 rows=272 width=36) (actual time=0.135..0.135 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.132 0.132 ↑ 34.0 8 1

Seq Scan on tb_project pj (cost=0.00..9.72 rows=272 width=36) (actual time=0.010..0.132 rows=8 loops=1)

18. 0.010 13,621.956 ↑ 4,090.7 3 1

Hash (cost=102,511.32..102,511.32 rows=12,272 width=4) (actual time=13,621.956..13,621.956 rows=3 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 129kB
19. 0.487 13,621.946 ↑ 4,090.7 3 1

Hash Join (cost=2,404.51..102,511.32 rows=12,272 width=4) (actual time=13,560.792..13,621.946 rows=3 loops=1)

  • Hash Cond: (rsn.node = ersn.node)
  • Join Filter: (SubPlan 1)
  • Rows Removed by Join Filter: 141
20. 280.486 280.486 ↑ 5.7 79 1

Seq Scan on tb_node rsn (cost=0.00..378.22 rows=449 width=8) (actual time=0.866..280.486 rows=79 loops=1)

  • Filter: ((node IS NOT NULL) AND fn_is_node_open(node))
  • Rows Removed by Filter: 120
21. 0.107 13,333.917 ↑ 511.3 144 1

Hash (cost=1,484.13..1,484.13 rows=73,630 width=12) (actual time=13,333.917..13,333.917 rows=144 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1031kB
22. 0.123 13,333.810 ↑ 511.3 144 1

Nested Loop (cost=1.44..1,484.13 rows=73,630 width=12) (actual time=314.065..13,333.810 rows=144 loops=1)

23. 0.070 0.247 ↑ 1.5 48 1

Merge Join (cost=1.19..7.58 rows=74 width=12) (actual time=0.071..0.247 rows=48 loops=1)

  • Merge Cond: (rsg.graph_type = rsgt.graph_type)
24. 0.166 0.166 ↑ 2.2 100 1

Index Scan using ix_graph_type_pk_val_parent_graph on tb_graph rsg (cost=0.14..11.09 rows=223 width=12) (actual time=0.007..0.166 rows=100 loops=1)

25. 0.003 0.011 ↑ 1.0 1 1

Sort (cost=1.04..1.05 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Sort Key: rsgt.graph_type
  • Sort Method: quicksort Memory: 25kB
26. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on tb_graph_type rsgt (cost=0.00..1.03 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=1)

  • Filter: ((table_name)::text = ANY ('{vw_reset_space_change}'::text[]))
  • Rows Removed by Filter: 7
27. 13,333.440 13,333.440 ↑ 331.7 3 48

Function Scan on fn_get_entity_nodes_by_graph_type ersn (cost=0.25..10.25 rows=995 width=4) (actual time=277.779..277.780 rows=3 loops=48)

  • Filter: (node IS NOT NULL)
28.          

SubPlan (forHash Join)

29. 7.056 7.056 ↑ 1,000.0 1 144

ProjectSet (cost=0.00..5.27 rows=1,000 width=4) (actual time=0.021..0.049 rows=1 loops=144)

30. 0.000 0.000 ↑ 1.0 1 144

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=144)