explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 568S

Settings
# exclusive inclusive rows x rows loops node
1. 3.956 7,246.374 ↑ 1,316.0 7 1

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

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

Initplan (forGroupAggregate)

3. 0.006 0.017 ↑ 1.0 1 1

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

4.          

Initplan (forResult)

5. 0.001 0.011 ↓ 0.0 0 1

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

6. 0.003 0.010 ↓ 0.0 0 1

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

  • Recheck Cond: (user_id = 2526)
7. 0.007 0.007 ↓ 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.007..0.007 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.003 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.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=1.72..22.39 rows=1 width=4) (actual time=0.002..0.002 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.560 12.620 ↑ 1.0 1 1

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

16.          

Initplan (forResult)

17. 2.460 12.060 ↓ 3.3 3,821 1

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

  • Group Key: udr.entity_id
18. 1.043 9.600 ↓ 3.5 4,088 1

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

19. 0.030 0.030 ↑ 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.021..0.030 rows=5 loops=1)

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

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

  • Hash Cond: (et.contract_id = udr_1.entity_id)
21. 1.481 1.481 ↓ 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.002..1.481 rows=4,038 loops=1)

22. 1.337 2.865 ↓ 1.0 6,396 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 289kB
23. 1.528 1.528 ↓ 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.006..1.528 rows=6,396 loops=1)

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

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

  • Hash Cond: (et_1.relation_id = udr_2.entity_id)
25. 1.006 1.006 ↓ 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.002..1.006 rows=4,038 loops=1)

26. 0.064 0.163 ↑ 1.2 274 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
27. 0.099 0.099 ↑ 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.018..0.099 rows=274 loops=1)

  • Index Cond: ((user_id = 2526) AND (entity_type_id = 1))
  • Heap Fetches: 25
28. 18.609 7,229.748 ↓ 2.8 25,345 1

Sort (cost=391,845.61..391,868.64 rows=9,212 width=577) (actual time=7,225.686..7,229.748 rows=25,345 loops=1)

  • Sort Key: data.cp_id DESC, data.period, data.label
  • Sort Method: quicksort Memory: 4173kB
29. 12.395 7,211.139 ↓ 2.8 25,345 1

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

  • Hash Cond: (action.tier_id = tier.id)
30. 13.015 7,198.699 ↓ 2.8 25,345 1

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

  • Hash Cond: (action.relation_id = rel.id)
31. 287.315 7,185.425 ↓ 2.8 25,345 1

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

  • Merge Cond: ((data.entity_id = action.id) AND (data.month_year = elt.month_year))
32. 22.295 2,886.270 ↑ 24.5 25,345 1

Sort (cost=346,765.83..348,318.91 rows=621,232 width=63) (actual time=2,881.346..2,886.270 rows=25,345 loops=1)

  • Sort Key: data.entity_id, data.month_year
  • Sort Method: quicksort Memory: 4173kB
33. 8.020 2,863.975 ↑ 24.5 25,345 1

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

34. 492.252 2,855.955 ↑ 24.5 25,345 1

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

  • Group Key: elt_1.entity_id, cp.id, elt_1.entity_type_id, entity.dynamic_metadata
35. 1,786.806 2,363.703 ↑ 1.4 448,640 1

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

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

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

  • Hash Cond: (elt_1.entity_id = entity.id)
37. 141.144 363.487 ↑ 4.2 472,423 1

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

38. 0.100 0.407 ↓ 2.5 143 1

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

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

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

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

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

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

Index Scan using idx_entity_lead_time_3 on entity_lead_time elt_1 (cost=0.45..1,282.57 rows=34,780 width=12) (actual time=0.004..1.552 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.221 4.307 ↓ 1.0 3,623 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 687kB
43. 3.086 3.086 ↓ 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.046..3.086 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,484.034 4,011.840 ↓ 28.8 1,044,636 1

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

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

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

46. 50.403 50.403 ↓ 9.5 3,701 1

Seq Scan on action_item_mgmt action (cost=0.00..679.85 rows=389 width=20) (actual time=12.666..50.403 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,095.496 1,095.496 ↓ 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.011..0.296 rows=282 loops=3,701)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
49. 0.167 0.167 ↓ 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.013..0.167 rows=434 loops=1)

  • Heap Fetches: 163
50. 0.018 0.045 ↑ 1.0 38 1

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

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

Seq Scan on tier (cost=0.00..1.38 rows=38 width=520) (actual time=0.014..0.027 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)