explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6htc

Settings
# exclusive inclusive rows x rows loops node
1. 6.005 21,626.066 ↓ 799.0 799 1

WindowAgg (cost=1,088,246.34..1,088,246.38 rows=1 width=104) (actual time=21,620.703..21,626.066 rows=799 loops=1)

  • Functions: 156
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 16.229 ms, Inlining 68.838 ms, Optimization 932.384 ms, Emission 603.478 ms, Total 1620.929 ms
2. 1.575 21,620.061 ↓ 799.0 799 1

Sort (cost=1,088,246.34..1,088,246.35 rows=1 width=137) (actual time=21,619.553..21,620.061 rows=799 loops=1)

  • Sort Key: (sum(((attr_goal.complete)::numeric * ((attr_goal.weight)::numeric / '100'::numeric))))
  • Sort Method: quicksort Memory: 231kB
3. 2.278 21,618.486 ↓ 799.0 799 1

Nested Loop Left Join (cost=1,086,974.47..1,088,246.33 rows=1 width=137) (actual time=21,575.166..21,618.486 rows=799 loops=1)

4. 4.417 21,597.032 ↓ 799.0 799 1

Merge Join (cost=1,086,957.43..1,088,229.26 rows=1 width=113) (actual time=21,575.131..21,597.032 rows=799 loops=1)

  • Merge Cond: (person_department_rel.from_bo_instance_id = person_goal_rel.from_bo_instance_id)
5. 2.437 1,176.422 ↓ 826.0 826 1

Sort (cost=1,794.70..1,794.70 rows=1 width=41) (actual time=1,175.838..1,176.422 rows=826 loops=1)

  • Sort Key: person_department_rel.from_bo_instance_id
  • Sort Method: quicksort Memory: 273kB
6. 2.934 1,173.985 ↓ 2,255.0 2,255 1

Nested Loop (cost=10.67..1,794.69 rows=1 width=41) (actual time=1,167.411..1,173.985 rows=2,255 loops=1)

7. 1,167.375 1,167.403 ↑ 9.0 16 1

Bitmap Heap Scan on bo_instance department_i (cost=10.23..575.37 rows=144 width=33) (actual time=1,167.370..1,167.403 rows=16 loops=1)

  • Recheck Cond: (name = ANY ('{Administrative,Distirbution}'::text[]))
  • Filter: (NOT is_deleted)
  • Heap Blocks: exact=9
8. 0.028 0.028 ↑ 7.2 20 1

Bitmap Index Scan on idx_bo_instance_name (cost=0.00..10.20 rows=144 width=0) (actual time=0.027..0.028 rows=20 loops=1)

  • Index Cond: (name = ANY ('{Administrative,Distirbution}'::text[]))
9. 3.648 3.648 ↓ 141.0 141 16

Index Scan using boi_rel_to_bo_instance_id_boc_rel_type_id_idx on boi_rel person_department_rel (cost=0.44..8.46 rows=1 width=16) (actual time=0.009..0.228 rows=141 loops=16)

  • Index Cond: ((to_bo_instance_id = department_i.id) AND (boc_rel_type_id = 30))
  • Filter: (NOT is_deleted)
10. 10.069 20,416.193 ↓ 25.0 5,000 1

Unique (cost=1,085,162.73..1,086,432.04 rows=200 width=80) (actual time=20,399.229..20,416.193 rows=5,000 loops=1)

11. 16.224 20,406.124 ↑ 25.4 10,000 1

Sort (cost=1,085,162.73..1,085,797.39 rows=253,863 width=80) (actual time=20,399.226..20,406.124 rows=10,000 loops=1)

  • Sort Key: person_goal_rel.from_bo_instance_id
  • Sort Method: quicksort Memory: 1166kB
12. 12.522 20,389.900 ↑ 25.4 10,000 1

Append (cost=1,026,226.78..1,048,554.71 rows=253,863 width=80) (actual time=20,271.077..20,389.900 rows=10,000 loops=1)

13. 40.273 20,331.576 ↑ 49.9 5,000 1

GroupAggregate (cost=1,026,226.78..1,034,956.10 rows=249,409 width=73) (actual time=20,271.074..20,331.576 rows=5,000 loops=1)

  • Group Key: person_goal_rel.from_bo_instance_id, attr_person.user_id, person_i.name
14. 51.191 20,291.303 ↑ 8.3 30,024 1

Sort (cost=1,026,226.78..1,026,850.31 rows=249,409 width=57) (actual time=20,271.028..20,291.303 rows=30,024 loops=1)

  • Sort Key: person_goal_rel.from_bo_instance_id, attr_person.user_id, person_i.name
  • Sort Method: quicksort Memory: 3224kB
15. 0.000 20,240.112 ↑ 8.3 30,024 1

Nested Loop (cost=674,579.32..994,489.57 rows=249,409 width=57) (actual time=19,756.217..20,240.112 rows=30,024 loops=1)

16. 0.019 0.019 ↑ 1.0 1 1

Index Scan using bo_instance_tenant_id_id_idx on bo_instance period_i (cost=0.43..8.45 rows=1 width=8) (actual time=0.013..0.019 rows=1 loops=1)

  • Index Cond: ((tenant_id = 360) AND (id = 16094692))
  • Filter: ((NOT is_deleted) AND (bo_class_id = 10))
17. 180.508 20,374.285 ↑ 8.3 30,024 1

Gather (cost=674,578.89..991,987.02 rows=249,409 width=65) (actual time=19,756.200..20,374.285 rows=30,024 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
18. 22.384 20,193.777 ↑ 9.8 15,012 2

Parallel Hash Left Join (cost=673,578.89..966,046.12 rows=146,711 width=65) (actual time=19,737.864..20,193.777 rows=15,012 loops=2)

  • Hash Cond: (person_i.id = attr_person.bo_instance_id)
19. 458.276 19,721.404 ↑ 9.8 15,012 2

Parallel Hash Join (cost=672,270.98..964,186.28 rows=146,711 width=65) (actual time=19,287.640..19,721.404 rows=15,012 loops=2)

  • Hash Cond: (person_goal_rel.from_bo_instance_id = person_i.id)
20. 178.464 9,746.219 ↑ 9.8 15,012 2

Parallel Hash Left Join (cost=401,631.65..666,630.79 rows=146,727 width=32) (actual time=9,591.741..9,746.219 rows=15,012 loops=2)

  • Hash Cond: (goal_i.id = attr_goal.bo_instance_id)
21. 473.322 6,831.493 ↑ 9.8 15,012 2

Parallel Hash Left Join (cost=250,535.30..505,161.59 rows=146,727 width=24) (actual time=6,392.220..6,831.493 rows=15,012 loops=2)

  • Hash Cond: (person_goal_rel.to_bo_instance_id = goal_i.id)
22. 113.806 135.021 ↑ 9.8 15,012 2

Nested Loop (cost=1.00..240,259.13 rows=146,727 width=24) (actual time=0.104..135.021 rows=15,012 loops=2)

23. 21.210 21.210 ↓ 3.5 15,012 2

Parallel Index Scan using idx_boi_rel_to_boi_id on boi_rel goal_period_rel (cost=0.44..12,154.06 rows=4,294 width=16) (actual time=0.078..21.210 rows=15,012 loops=2)

  • Index Cond: (to_bo_instance_id = 16094692)
  • Filter: ((NOT is_deleted) AND (boc_rel_type_id = 20))
  • Rows Removed by Filter: 1
24. 0.005 0.005 ↑ 35.0 1 30,024

Index Scan using boi_rel_is_deleted_to_bo_instance_id_boc_rel_type_id_idx on boi_rel person_goal_rel (cost=0.56..52.77 rows=35 width=16) (actual time=0.004..0.005 rows=1 loops=30,024)

  • Index Cond: ((is_deleted = false) AND (to_bo_instance_id = goal_period_rel.from_bo_instance_id) AND (boc_rel_type_id = 29))
25. 3,087.820 6,223.150 ↓ 1.2 3,768,618 2

Parallel Hash (cost=199,032.83..199,032.83 rows=3,139,078 width=8) (actual time=6,223.149..6,223.150 rows=3,768,618 loops=2)

  • Buckets: 524288 Batches: 32 Memory Usage: 13376kB
26. 3,135.330 3,135.330 ↓ 1.2 3,768,618 2

Parallel Seq Scan on bo_instance goal_i (cost=0.00..199,032.83 rows=3,139,078 width=8) (actual time=0.020..3,135.330 rows=3,768,618 loops=2)

  • Filter: (NOT is_deleted)
  • Rows Removed by Filter: 73
27. 1,360.599 2,736.262 ↓ 1.2 1,604,418 2

Parallel Hash (cost=126,525.82..126,525.82 rows=1,338,282 width=24) (actual time=2,736.261..2,736.262 rows=1,604,418 loops=2)

  • Buckets: 262144 Batches: 16 Memory Usage: 13088kB
28. 1,375.663 1,375.663 ↓ 1.2 1,604,418 2

Parallel Seq Scan on attrib_goal attr_goal (cost=0.00..126,525.82 rows=1,338,282 width=24) (actual time=0.037..1,375.663 rows=1,604,418 loops=2)

29. 3,450.962 9,516.909 ↓ 1.2 3,768,598 2

Parallel Hash (cost=206,880.78..206,880.78 rows=3,138,844 width=33) (actual time=9,516.908..9,516.909 rows=3,768,598 loops=2)

  • Buckets: 262144 Batches: 64 Memory Usage: 10112kB
30. 6,065.947 6,065.947 ↓ 1.2 3,768,598 2

Parallel Seq Scan on bo_instance person_i (cost=0.00..206,880.78 rows=3,138,844 width=33) (actual time=0.040..6,065.947 rows=3,768,598 loops=2)

  • Filter: ((NOT is_deleted) AND (name !~~* 'mta-user'::text))
  • Rows Removed by Filter: 94
31. 223.391 449.989 ↑ 1.2 7,750 2

Parallel Hash (cost=1,194.07..1,194.07 rows=9,107 width=16) (actual time=449.988..449.989 rows=7,750 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 896kB
32. 226.598 226.598 ↑ 1.2 7,750 2

Parallel Seq Scan on attrib_person attr_person (cost=0.00..1,194.07 rows=9,107 width=16) (actual time=219.580..226.598 rows=7,750 loops=2)

33. 6.719 45.802 ↓ 1.1 5,000 1

Subquery Scan on *SELECT* 2 (cost=8,481.07..9,835.21 rows=4,454 width=73) (actual time=11.333..45.802 rows=5,000 loops=1)

34. 16.560 39.083 ↓ 1.1 5,000 1

Hash Right Join (cost=8,481.07..9,779.53 rows=4,454 width=45) (actual time=11.329..39.083 rows=5,000 loops=1)

  • Hash Cond: (attr_person_1.bo_instance_id = person_i_1.id)
35. 11.231 11.231 ↓ 1.0 15,501 1

Seq Scan on attrib_person attr_person_1 (cost=0.00..1,257.82 rows=15,482 width=16) (actual time=0.010..11.231 rows=15,501 loops=1)

36. 3.763 11.292 ↓ 1.1 5,000 1

Hash (cost=8,425.40..8,425.40 rows=4,454 width=33) (actual time=11.291..11.292 rows=5,000 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 325kB
37. 7.529 7.529 ↓ 1.1 5,000 1

Index Scan using bo_instance_tenant_id_bo_class_id_idx2 on bo_instance person_i_1 (cost=0.43..8,425.40 rows=4,454 width=33) (actual time=0.057..7.529 rows=5,000 loops=1)

  • Index Cond: ((tenant_id = 360) AND (bo_class_id = 20))
  • Filter: ((NOT is_deleted) AND (name !~~* 'mta-user'::text))
  • Rows Removed by Filter: 1
38. 6.392 19.176 ↑ 1.0 1 799

Aggregate (cost=17.04..17.05 rows=1 width=32) (actual time=0.023..0.024 rows=1 loops=799)

39. 4.880 12.784 ↓ 2.0 2 799

Nested Loop Left Join (cost=0.99..17.03 rows=1 width=33) (actual time=0.008..0.016 rows=2 loops=799)

40. 4.794 4.794 ↓ 2.0 2 799

Index Scan using boi_rel_boc_rel_type_id_from_bo_instance_id_idx on boi_rel person_team_rel (cost=0.56..8.58 rows=1 width=8) (actual time=0.004..0.006 rows=2 loops=799)

  • Index Cond: ((boc_rel_type_id = 31) AND (from_bo_instance_id = person_goal_rel.from_bo_instance_id))
  • Filter: (NOT is_deleted)
41. 3.110 3.110 ↑ 1.0 1 1,555

Index Scan using bo_instance_pkey on bo_instance team_i (cost=0.43..8.45 rows=1 width=33) (actual time=0.002..0.002 rows=1 loops=1,555)

  • Index Cond: (id = person_team_rel.to_bo_instance_id)
  • Filter: (NOT is_deleted)
Planning time : 3.999 ms
Execution time : 21,813.146 ms