explain.depesz.com

PostgreSQL's explain analyze made readable

Result: E3NC

Settings
# exclusive inclusive rows x rows loops node
1. 3.772 8,162.634 ↑ 1,316.0 7 1

GroupAggregate (cost=393,632.56..394,070.13 rows=9,212 width=134) (actual time=8,145.373..8,162.634 rows=7 loops=1)

  • Group Key: data.cp_id, data.period, data.label
2.          

Initplan (forGroupAggregate)

3. 0.005 0.012 ↑ 1.0 1 1

Result (cost=22.39..22.40 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=1)

4.          

Initplan (forResult)

5. 0.001 0.007 ↓ 0.0 0 1

Nested Loop (cost=1.72..22.39 rows=4 width=4) (actual time=0.007..0.007 rows=0 loops=1)

6. 0.002 0.006 ↓ 0.0 0 1

Bitmap Heap Scan on secondary_data_access sda (cost=1.30..6.52 rows=6 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Recheck Cond: (user_id = 2526)
7. 0.004 0.004 ↓ 0.0 0 1

Bitmap Index Scan on idx_secondary_data_access_user_id_data_id (cost=0.00..1.30 rows=6 width=0) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (user_id = 2526)
8. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_entity_data on entity_data ed (cost=0.42..2.65 rows=1 width=8) (never executed)

  • Index Cond: (id = sda.data_id)
  • Filter: (entity_type_id = 61)
9. 0.002 0.005 ↑ 1.0 1 1

Result (cost=22.39..22.40 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1)

10.          

Initplan (forResult)

11. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=1.72..22.39 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)

12. 0.001 0.002 ↓ 0.0 0 1

Bitmap Heap Scan on secondary_data_access sda_1 (cost=1.30..6.52 rows=6 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Recheck Cond: (user_id = 2526)
13. 0.001 0.001 ↓ 0.0 0 1

Bitmap Index Scan on idx_secondary_data_access_user_id_data_id (cost=0.00..1.30 rows=6 width=0) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (user_id = 2526)
14. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_entity_data on entity_data ed_1 (cost=0.42..2.65 rows=1 width=8) (never executed)

  • Index Cond: (id = sda_1.data_id)
  • Filter: (entity_type_id = 1)
15. 0.552 12.416 ↑ 1.0 1 1

Result (cost=1,742.14..1,742.15 rows=1 width=32) (actual time=12.416..12.416 rows=1 loops=1)

16.          

Initplan (forResult)

17. 2.203 11.864 ↓ 3.3 3,821 1

HashAggregate (cost=1,730.61..1,742.14 rows=1,153 width=4) (actual time=11.106..11.864 rows=3,821 loops=1)

  • Group Key: udr.entity_id
18. 1.038 9.661 ↓ 3.5 4,088 1

Append (cost=0.43..1,727.72 rows=1,153 width=4) (actual time=0.016..9.661 rows=4,088 loops=1)

19. 0.023 0.023 ↑ 5.4 5 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr (cost=0.43..4.24 rows=27 width=4) (actual time=0.016..0.023 rows=5 loops=1)

  • Index Cond: ((user_id = 2526) AND (entity_type_id = 18))
  • Heap Fetches: 3
20. 0.780 5.233 ↑ 1.2 262 1

Hash Join (cost=539.71..1,109.04 rows=312 width=4) (actual time=2.975..5.233 rows=262 loops=1)

  • Hash Cond: (et.contract_id = udr_1.entity_id)
21. 1.503 1.503 ↓ 1.0 4,038 1

Seq Scan on action_item_mgmt et (cost=0.00..518.37 rows=4,037 width=12) (actual time=0.001..1.503 rows=4,038 loops=1)

22. 1.404 2.950 ↓ 1.0 6,396 1

Hash (cost=461.29..461.29 rows=6,273 width=4) (actual time=2.950..2.950 rows=6,396 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 289kB
23. 1.546 1.546 ↓ 1.0 6,396 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_1 (cost=0.43..461.29 rows=6,273 width=4) (actual time=0.005..1.546 rows=6,396 loops=1)

  • Index Cond: ((user_id = 2526) AND (entity_type_id = 61))
  • Heap Fetches: 87
24. 2.155 3.367 ↓ 4.7 3,821 1

Hash Join (cost=33.59..602.91 rows=814 width=4) (actual time=0.183..3.367 rows=3,821 loops=1)

  • Hash Cond: (et_1.relation_id = udr_2.entity_id)
25. 1.044 1.044 ↓ 1.0 4,038 1

Seq Scan on action_item_mgmt et_1 (cost=0.00..518.37 rows=4,037 width=8) (actual time=0.003..1.044 rows=4,038 loops=1)

26. 0.070 0.168 ↑ 1.2 274 1

Hash (cost=29.50..29.50 rows=327 width=4) (actual time=0.168..0.168 rows=274 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
27. 0.098 0.098 ↑ 1.2 274 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_2 (cost=0.43..29.50 rows=327 width=4) (actual time=0.020..0.098 rows=274 loops=1)

  • Index Cond: ((user_id = 2526) AND (entity_type_id = 1))
  • Heap Fetches: 25
28. 15.755 8,146.401 ↓ 2.8 25,345 1

Sort (cost=391,845.61..391,868.64 rows=9,212 width=577) (actual time=8,142.343..8,146.401 rows=25,345 loops=1)

  • Sort Key: data.cp_id DESC, data.period, data.label
  • Sort Method: quicksort Memory: 4173kB
29. 10.157 8,130.646 ↓ 2.8 25,345 1

Hash Left Join (cost=384,583.51..389,836.83 rows=9,212 width=577) (actual time=7,696.301..8,130.646 rows=25,345 loops=1)

  • Hash Cond: (action.tier_id = tier.id)
30. 10.005 8,120.454 ↓ 2.8 25,345 1

Hash Join (cost=384,581.66..389,720.76 rows=9,212 width=65) (actual time=7,696.249..8,120.454 rows=25,345 loops=1)

  • Hash Cond: (action.relation_id = rel.id)
31. 214.522 8,110.038 ↓ 2.8 25,345 1

Merge Join (cost=384,564.06..389,587.05 rows=9,212 width=69) (actual time=7,695.827..8,110.038 rows=25,345 loops=1)

  • Merge Cond: ((data.entity_id = action.id) AND (data.month_year = elt.month_year))
32. 21.654 3,595.797 ↑ 24.5 25,345 1

Sort (cost=346,765.83..348,318.91 rows=621,232 width=63) (actual time=3,591.609..3,595.797 rows=25,345 loops=1)

  • Sort Key: data.entity_id, data.month_year
  • Sort Method: quicksort Memory: 4173kB
33. 7.991 3,574.143 ↑ 24.5 25,345 1

Subquery Scan on data (cost=240,692.11..273,306.79 rows=621,232 width=63) (actual time=2,544.727..3,574.143 rows=25,345 loops=1)

34. 484.409 3,566.152 ↑ 24.5 25,345 1

GroupAggregate (cost=240,692.11..267,094.47 rows=621,232 width=197) (actual time=2,544.726..3,566.152 rows=25,345 loops=1)

  • Group Key: elt1.entity_id, cp.id, elt1.entity_type_id, entity.dynamic_metadata
35. 2,496.519 3,081.743 ↑ 1.4 448,640 1

Sort (cost=240,692.11..242,245.19 rows=621,232 width=173) (actual time=2,544.700..3,081.743 rows=448,640 loops=1)

  • Sort Key: elt1.entity_id DESC, cp.id, entity.dynamic_metadata
  • Sort Method: external merge Disk: 87648kB
36. 216.354 585.224 ↑ 1.4 448,640 1

Hash Join (cost=608.22..118,730.06 rows=621,232 width=173) (actual time=5.525..585.224 rows=448,640 loops=1)

  • Hash Cond: (elt1.entity_id = entity.id)
37. 142.039 363.827 ↑ 4.2 472,423 1

Nested Loop (cost=0.45..92,985.45 rows=1,991,161 width=43) (actual time=0.455..363.827 rows=472,423 loops=1)

38. 0.102 0.424 ↓ 2.5 143 1

Nested Loop (cost=0.00..54.36 rows=57 width=31) (actual time=0.270..0.424 rows=143 loops=1)

  • Join Filter: (cp.calendar_id = cc.id)
  • Rows Removed by Join Filter: 312
39. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on calendar cc (cost=0.00..1.11 rows=1 width=4) (actual time=0.010..0.013 rows=1 loops=1)

  • Filter: ((calendar_type_id = ANY ('{1002}'::integer[])) AND (client_id = 1005))
  • Rows Removed by Filter: 7
40. 0.309 0.309 ↑ 1.0 455 1

Seq Scan on calendar_periods cp (cost=0.00..47.52 rows=458 width=35) (actual time=0.005..0.309 rows=455 loops=1)

  • Filter: ((client_id = 1005) AND (period_type = 1))
  • Rows Removed by Filter: 1180
41. 221.364 221.364 ↑ 10.5 3,304 143

Index Scan using idx_entity_lead_time_3 on entity_lead_time elt1 (cost=0.45..1,282.57 rows=34,780 width=12) (actual time=0.004..1.548 rows=3,304 loops=143)

  • Index Cond: ((entity_type_id = 18) AND (month_year >= (cp.start_date)::date) AND (month_year <= (cp.end_date)::date) AND (month_year < now()) AND (month_year > to_timestamp(to_char(('2018-12-09'::date)::timestamp with time zone, 'DDMMYYYY'::text), 'DDMMYYYY'::text)))
  • Filter: (ageing IS NOT NULL)
  • Rows Removed by Filter: 0
42. 1.528 5.043 ↓ 1.0 3,623 1

Hash (cost=563.79..563.79 rows=3,519 width=142) (actual time=5.043..5.043 rows=3,623 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 687kB
43. 3.515 3.515 ↓ 1.0 3,623 1

Seq Scan on action_item_mgmt entity (cost=0.00..563.79 rows=3,519 width=142) (actual time=0.082..3.515 rows=3,623 loops=1)

  • Filter: ((NOT deleted) AND (ageing IS NOT NULL) AND (client_id = 1005) AND (status_id = ANY ('{2236,2237,2238,2239,2,1,4}'::integer[])))
  • Rows Removed by Filter: 415
44. 2,810.666 4,299.719 ↓ 28.8 1,044,636 1

Sort (cost=37,798.23..37,888.77 rows=36,217 width=30) (actual time=4,103.965..4,299.719 rows=1,044,636 loops=1)

  • Sort Key: action.id, elt.month_year
  • Sort Method: external sort Disk: 46992kB
45. 380.169 1,489.053 ↓ 28.8 1,044,672 1

Nested Loop (cost=0.43..35,055.81 rows=36,217 width=30) (actual time=12.472..1,489.053 rows=1,044,672 loops=1)

46. 50.398 50.398 ↓ 9.5 3,701 1

Seq Scan on action_item_mgmt action (cost=0.00..679.85 rows=389 width=20) (actual time=12.454..50.398 rows=3,701 loops=1)

  • Filter: ((ageing IS NOT NULL) AND (client_id = 1005) AND ((contract_id = ANY ($3)) OR (relation_id = ANY ($6)) OR (id = ANY ($8))))
  • Rows Removed by Filter: 337
47. 1,058.486 1,058.486 ↓ 3.0 282 3,701

Index Scan using idx_entity_lead_time_1 on entity_lead_time elt (cost=0.43..87.44 rows=93 width=18) (actual time=0.010..0.286 rows=282 loops=3,701)

  • Index Cond: ((entity_type_id = 18) AND (entity_id = action.id))
48. 0.163 0.411 ↓ 1.0 434 1

Hash (cost=12.22..12.22 rows=430 width=4) (actual time=0.411..0.411 rows=434 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
49. 0.248 0.248 ↓ 1.0 434 1

Index Only Scan using pk_relation on relation rel (cost=0.27..12.22 rows=430 width=4) (actual time=0.021..0.248 rows=434 loops=1)

  • Heap Fetches: 163
50. 0.013 0.035 ↑ 1.0 38 1

Hash (cost=1.38..1.38 rows=38 width=520) (actual time=0.035..0.035 rows=38 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
51. 0.022 0.022 ↑ 1.0 38 1

Seq Scan on tier (cost=0.00..1.38 rows=38 width=520) (actual time=0.012..0.022 rows=38 loops=1)

52.          

SubPlan (forGroupAggregate)

53. 0.028 0.028 ↑ 1.0 1 7

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=7)