explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VF5t

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 2,507.729 ↓ 0.0 0 1

Insert on catmaid_skeleton_summary (cost=2,499,036.38..2,501,002.95 rows=26,221 width=48) (actual time=2,507.728..2,507.729 rows=0 loops=1)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: catmaid_skeleton_summary_pkey
  • Tuples Inserted: 0
  • Conflicting Tuples: 0
  • Buffers: shared hit=174348
  • Functions: 149
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 19.858 ms, Inlining 64.425 ms, Optimization 1357.736 ms, Emission 945.817 ms, Total 2387.836 ms
2.          

CTE updated_parent_edge_data

3. 2.738 86.689 ↓ 0.0 0 1

Merge Join (cost=69,209.99..260,317.52 rows=424,581 width=100) (actual time=86.689..86.689 rows=0 loops=1)

  • Merge Cond: (t.id = ot.id)
  • Join Filter: ((ot.parent_id IS DISTINCT FROM t.parent_id) OR (ot.location_x <> t.location_x) OR (ot.location_y <> t.location_y) OR (ot.location_z <> t.location_z) OR (ot.skeleton_id <> t.skeleton_id))
  • Rows Removed by Join Filter: 9215
  • Buffers: shared hit=92876
4. 4.530 80.956 ↑ 1.0 9,215 1

Sort (cost=68,050.30..68,073.33 rows=9,215 width=68) (actual time=80.460..80.956 rows=9,215 loops=1)

  • Sort Key: t.id
  • Sort Method: quicksort Memory: 1680kB
  • Buffers: shared hit=92876
5. 9.603 76.426 ↑ 1.0 9,215 1

Nested Loop (cost=4.16..67,443.50 rows=9,215 width=68) (actual time=0.061..76.426 rows=9,215 loops=1)

  • Buffers: shared hit=92872
6. 2.318 2.318 ↑ 1.0 9,215 1

Named Tuplestore Scan (cost=0.00..552.90 rows=9,215 width=56) (actual time=0.008..2.318 rows=9,215 loops=1)

7. 18.430 64.505 ↑ 1.0 1 9,215

Bitmap Heap Scan on treenode p (cost=4.16..7.23 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=9,215)

  • Recheck Cond: ((id = t.parent_id) OR (id = t.id))
  • Filter: (((t.parent_id IS NOT NULL) AND (id = t.parent_id)) OR ((t.parent_id IS NULL) AND (id = t.id)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=18958
  • Buffers: shared hit=92872
8. 0.000 46.075 ↓ 0.0 0 9,215

BitmapOr (cost=4.16..4.16 rows=2 width=0) (actual time=0.005..0.005 rows=0 loops=9,215)

  • Buffers: shared hit=73914
9. 27.645 27.645 ↓ 2.0 2 9,215

Bitmap Index Scan on treenode_pkey (cost=0.00..2.08 rows=1 width=0) (actual time=0.003..0.003 rows=2 loops=9,215)

  • Index Cond: (id = t.parent_id)
  • Buffers: shared hit=36957
10. 18.430 18.430 ↓ 2.0 2 9,215

Bitmap Index Scan on treenode_pkey (cost=0.00..2.08 rows=1 width=0) (actual time=0.002..0.002 rows=2 loops=9,215)

  • Index Cond: (id = t.id)
  • Buffers: shared hit=36957
11. 2.143 2.995 ↑ 1.0 9,215 1

Sort (cost=1,159.70..1,182.73 rows=9,215 width=48) (actual time=2.565..2.995 rows=9,215 loops=1)

  • Sort Key: ot.id
  • Sort Method: quicksort Memory: 1104kB
12. 0.852 0.852 ↑ 1.0 9,215 1

Named Tuplestore Scan (cost=0.00..552.90 rows=9,215 width=48) (actual time=0.004..0.852 rows=9,215 loops=1)

13.          

CTE updated_child_edge_data

14. 0.061 44.364 ↓ 0.0 0 1

Hash Join (cost=39,172.22..290,342.52 rows=622,566 width=100) (actual time=44.363..44.364 rows=0 loops=1)

  • Hash Cond: (ot_1.id = c.parent_id)
  • Buffers: shared hit=81466
15. 0.002 0.002 ↑ 9,215.0 1 1

Named Tuplestore Scan (cost=0.00..552.90 rows=9,215 width=20) (actual time=0.002..0.002 rows=1 loops=1)

16. 0.002 44.301 ↓ 0.0 0 1

Hash (cost=38,733.08..38,733.08 rows=13,512 width=80) (actual time=44.301..44.301 rows=0 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 128kB
  • Buffers: shared hit=81463
17. 1.861 44.299 ↓ 0.0 0 1

Merge Anti Join (cost=38,551.86..38,733.08 rows=13,512 width=80) (actual time=44.299..44.299 rows=0 loops=1)

  • Merge Cond: (c.id = c2.id)
  • Buffers: shared hit=81463
18. 4.626 40.351 ↑ 2.9 9,214 1

Sort (cost=37,392.16..37,459.72 rows=27,024 width=80) (actual time=39.892..40.351 rows=9,214 loops=1)

  • Sort Key: c.id
  • Sort Method: quicksort Memory: 1680kB
  • Buffers: shared hit=81463
19. 6.799 35.725 ↑ 2.9 9,214 1

Nested Loop (cost=0.57..35,402.93 rows=27,024 width=80) (actual time=0.032..35.725 rows=9,214 loops=1)

  • Buffers: shared hit=81463
20. 1.281 1.281 ↑ 1.0 9,215 1

Named Tuplestore Scan (cost=0.00..552.90 rows=9,215 width=24) (actual time=0.004..1.281 rows=9,215 loops=1)

21. 27.645 27.645 ↑ 3.0 1 9,215

Index Scan using treenode_parent_id on treenode c (cost=0.57..3.69 rows=3 width=56) (actual time=0.002..0.003 rows=1 loops=9,215)

  • Index Cond: (parent_id = e.id)
  • Buffers: shared hit=81463
22. 1.474 2.087 ↑ 1.0 9,215 1

Sort (cost=1,159.70..1,182.73 rows=9,215 width=8) (actual time=1.651..2.087 rows=9,215 loops=1)

  • Sort Key: c2.id
  • Sort Method: quicksort Memory: 816kB
23. 0.613 0.613 ↑ 1.0 9,215 1

Named Tuplestore Scan (cost=0.00..552.90 rows=9,215 width=8) (actual time=0.002..0.613 rows=9,215 loops=1)

24.          

CTE updated_edge_data

25. 0.004 2,499.999 ↓ 0.0 0 1

Append (cost=26,536.34..76,441.82 rows=104,715 width=100) (actual time=2,499.999..2,499.999 rows=0 loops=1)

  • Buffers: shared hit=174342
26. 2,368.912 2,455.616 ↓ 0.0 0 1

Limit (cost=26,536.34..29,083.82 rows=42,458 width=100) (actual time=2,455.616..2,455.616 rows=0 loops=1)

  • Buffers: shared hit=92876
27.          

Initplan (for Limit)

28. 0.011 86.704 ↑ 1.0 1 1

Aggregate (cost=26,536.31..26,536.34 rows=1 width=8) (actual time=86.704..86.704 rows=1 loops=1)

  • Buffers: shared hit=92876
29. 86.693 86.693 ↓ 0.0 0 1

CTE Scan on updated_parent_edge_data (cost=0.00..25,474.86 rows=424,581 width=0) (actual time=86.693..86.693 rows=0 loops=1)

  • Buffers: shared hit=92876
30. 0.000 0.000 ↓ 0.0 0

CTE Scan on updated_parent_edge_data updated_parent_edge_data_1 (cost=0.00..25,474.86 rows=424,581 width=100) (never executed)

31. 0.005 44.379 ↓ 0.0 0 1

Limit (cost=38,910.40..42,645.82 rows=62,257 width=100) (actual time=44.379..44.379 rows=0 loops=1)

  • Buffers: shared hit=81466
32.          

Initplan (for Limit)

33. 0.009 44.374 ↑ 1.0 1 1

Aggregate (cost=38,910.38..38,910.40 rows=1 width=8) (actual time=44.374..44.374 rows=1 loops=1)

  • Buffers: shared hit=81466
34. 44.365 44.365 ↓ 0.0 0 1

CTE Scan on updated_child_edge_data (cost=0.00..37,353.96 rows=622,566 width=0) (actual time=44.365..44.365 rows=0 loops=1)

  • Buffers: shared hit=81466
35. 0.000 0.000 ↓ 0.0 0

CTE Scan on updated_child_edge_data updated_child_edge_data_1 (cost=0.00..37,353.96 rows=622,566 width=100) (never executed)

36.          

CTE old_edge

37. 0.001 2,500.001 ↓ 0.0 0 1

Nested Loop (cost=0.57..380,346.34 rows=104,715 width=100) (actual time=2,500.001..2,500.001 rows=0 loops=1)

  • Buffers: shared hit=174342
38. 2,500.000 2,500.000 ↓ 0.0 0 1

CTE Scan on updated_edge_data t_1 (cost=0.00..6,282.90 rows=104,715 width=36) (actual time=2,500.000..2,500.000 rows=0 loops=1)

  • Buffers: shared hit=174342
39. 0.000 0.000 ↓ 0.0 0

Index Scan using treenode_edge_pkey on treenode_edge e_1 (cost=0.57..3.57 rows=1 width=72) (never executed)

  • Index Cond: (id = t_1.id)
40.          

CTE updated_edge

41. 0.001 0.002 ↓ 0.0 0 1

Update on treenode_edge e_2 (cost=0.57..380,608.13 rows=104,715 width=130) (actual time=0.002..0.002 rows=0 loops=1)

42. 0.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.57..380,608.13 rows=104,715 width=130) (actual time=0.001..0.001 rows=0 loops=1)

43. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on updated_edge_data ue (cost=0.00..6,282.90 rows=104,715 width=120) (actual time=0.001..0.001 rows=0 loops=1)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using treenode_edge_pkey on treenode_edge e_2 (cost=0.57..3.57 rows=1 width=18) (never executed)

  • Index Cond: (id = ue.id)
45.          

CTE new_edge

46. 0.002 4.323 ↓ 0.0 0 1

Append (cost=0.00..226,697.93 rows=2,517,349 width=68) (actual time=4.323..4.323 rows=0 loops=1)

47. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on updated_edge_data ue_1 (cost=0.00..6,282.90 rows=104,715 width=68) (actual time=0.000..0.001 rows=0 loops=1)

48. 0.008 4.320 ↓ 0.0 0 1

Merge Join (cost=25,810.55..110,275.78 rows=2,412,634 width=68) (actual time=4.320..4.320 rows=0 loops=1)

  • Merge Cond: (nt.id = oe.id)
49. 1.694 4.309 ↑ 4,608.0 1 1

Sort (cost=10,796.46..10,807.98 rows=4,608 width=36) (actual time=4.309..4.309 rows=1 loops=1)

  • Sort Key: nt.id
  • Sort Method: quicksort Memory: 1104kB
50. 1.753 2.615 ↓ 2.0 9,215 1

Hash Anti Join (cost=9,686.14..10,516.06 rows=4,608 width=36) (actual time=0.413..2.615 rows=9,215 loops=1)

  • Hash Cond: (nt.id = ue_2.id)
51. 0.861 0.861 ↑ 1.0 9,215 1

Named Tuplestore Scan (cost=0.00..552.90 rows=9,215 width=36) (actual time=0.004..0.861 rows=9,215 loops=1)

52. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=6,282.90..6,282.90 rows=104,715 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1024kB
53. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on updated_edge_data ue_2 (cost=0.00..6,282.90 rows=104,715 width=8) (actual time=0.001..0.001 rows=0 loops=1)

54. 0.003 0.003 ↓ 0.0 0 1

Sort (cost=15,014.09..15,275.88 rows=104,715 width=40) (actual time=0.003..0.003 rows=0 loops=1)

  • Sort Key: oe.id
  • Sort Method: quicksort Memory: 25kB
55. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on old_edge oe (cost=0.00..6,282.90 rows=104,715 width=40) (actual time=0.000..0.000 rows=0 loops=1)

56.          

CTE old_skeleton_data

57. 0.098 2,500.113 ↓ 0.0 0 1

HashAggregate (cost=29,674.19..30,040.71 rows=10,472 width=44) (actual time=2,500.113..2,500.113 rows=0 loops=1)

  • Group Key: old_edge.skeleton_id, old_edge.project_id, first_value(old_edge.editor_id) OVER (?)
  • Buffers: shared hit=174342
58. 0.004 2,500.015 ↓ 0.0 0 1

WindowAgg (cost=15,014.09..19,464.48 rows=104,715 width=60) (actual time=2,500.015..2,500.015 rows=0 loops=1)

  • Buffers: shared hit=174342
59. 0.007 2,500.011 ↓ 0.0 0 1

Sort (cost=15,014.09..15,275.88 rows=104,715 width=60) (actual time=2,500.011..2,500.011 rows=0 loops=1)

  • Sort Key: old_edge.skeleton_id, old_edge.project_id, old_edge.edition_time DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=174342
60. 2,500.004 2,500.004 ↓ 0.0 0 1

CTE Scan on old_edge (cost=0.00..6,282.90 rows=104,715 width=60) (actual time=2,500.004..2,500.004 rows=0 loops=1)

  • Buffers: shared hit=174342
61.          

CTE new_skeleton_data

62. 2.992 7.327 ↓ 0.0 0 1

HashAggregate (cost=771,107.72..778,659.77 rows=251,735 width=44) (actual time=7.327..7.327 rows=0 loops=1)

  • Group Key: new_edge.skeleton_id, new_edge.project_id, first_value(new_edge.editor_id) OVER (?)
63. 0.004 4.335 ↓ 0.0 0 1

WindowAgg (cost=418,678.86..525,666.20 rows=2,517,349 width=60) (actual time=4.335..4.335 rows=0 loops=1)

64. 0.006 4.331 ↓ 0.0 0 1

Sort (cost=418,678.86..424,972.24 rows=2,517,349 width=60) (actual time=4.331..4.331 rows=0 loops=1)

  • Sort Key: new_edge.skeleton_id, new_edge.project_id, new_edge.edition_time DESC
  • Sort Method: quicksort Memory: 25kB
65. 4.325 4.325 ↓ 0.0 0 1

CTE Scan on new_edge (cost=0.00..151,040.94 rows=2,517,349 width=60) (actual time=4.325..4.325 rows=0 loops=1)

66.          

CTE summary_update_delta

67. 0.188 2,507.723 ↓ 0.0 0 1

HashAggregate (cost=74,729.45..75,581.63 rows=26,221 width=68) (actual time=2,507.723..2,507.723 rows=0 loops=1)

  • Group Key: os.skeleton_id, os.project_id, first_value(os.last_editor_id) OVER (?)
  • Buffers: shared hit=174348
68. 0.003 2,507.535 ↓ 0.0 0 1

WindowAgg (cost=51,130.82..62,274.62 rows=262,207 width=44) (actual time=2,507.535..2,507.535 rows=0 loops=1)

  • Buffers: shared hit=174348
69. 0.083 2,507.532 ↓ 0.0 0 1

Sort (cost=51,130.82..51,786.34 rows=262,207 width=44) (actual time=2,507.532..2,507.532 rows=0 loops=1)

  • Sort Key: os.skeleton_id, os.project_id, os.max_edition_time DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=174348
70. 0.001 2,507.449 ↓ 0.0 0 1

Result (cost=0.00..27,531.73 rows=262,207 width=44) (actual time=2,507.449..2,507.449 rows=0 loops=1)

  • Buffers: shared hit=174342
71. 0.003 2,507.448 ↓ 0.0 0 1

Append (cost=0.00..19,665.52 rows=262,207 width=44) (actual time=2,507.447..2,507.448 rows=0 loops=1)

  • Buffers: shared hit=174342
72. 2,500.115 2,500.115 ↓ 0.0 0 1

CTE Scan on old_skeleton_data os (cost=0.00..628.32 rows=10,472 width=44) (actual time=2,500.115..2,500.115 rows=0 loops=1)

  • Buffers: shared hit=174342
73. 7.330 7.330 ↓ 0.0 0 1

CTE Scan on new_skeleton_data ns (cost=0.00..15,104.10 rows=251,735 width=44) (actual time=7.330..7.330 rows=0 loops=1)

74. 2,507.726 2,507.726 ↓ 0.0 0 1

CTE Scan on summary_update_delta s (cost=0.00..1,966.58 rows=26,221 width=48) (actual time=2,507.726..2,507.726 rows=0 loops=1)

  • Buffers: shared hit=174348