explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w7Z

Settings
# exclusive inclusive rows x rows loops node
1. 0.644 5,984.620 ↑ 1,439.0 7 1

GroupAggregate (cost=605,515.05..605,993.52 rows=10,073 width=134) (actual time=5,971.474..5,984.620 rows=7 loops=1)

  • Group Key: cp.start_date, cp.name, (((((((cp.name)::text || ' ('::text) || to_char(cp.start_date, 'DDMONYYYY'::text)) || ' - '::text) || to_char(cp.end_date, 'DDMONYYYY'::text)) || ')'::text))
2.          

Initplan (forGroupAggregate)

3. 0.008 0.015 ↑ 1.0 1 1

Result (cost=64.34..64.35 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=1)

4.          

Initplan (forResult)

5. 0.000 0.007 ↓ 0.0 0 1

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

6. 0.002 0.007 ↓ 0.0 0 1

Bitmap Heap Scan on secondary_data_access sda (cost=4.20..13.67 rows=6 width=4) (actual time=0.007..0.007 rows=0 loops=1)

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

Bitmap Index Scan on idx_secondary_data_access_user_id_data_id (cost=0.00..4.20 rows=6 width=0) (actual time=0.005..0.005 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..8.45 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=64.34..64.35 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1)

10.          

Initplan (forResult)

11. 0.001 0.002 ↓ 0.0 0 1

Nested Loop (cost=4.62..64.34 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1)

12. 0.000 0.001 ↓ 0.0 0 1

Bitmap Heap Scan on secondary_data_access sda_1 (cost=4.20..13.67 rows=6 width=4) (actual time=0.001..0.001 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..4.20 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..8.45 rows=1 width=8) (never executed)

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

Result (cost=3,211.63..3,211.64 rows=1 width=32) (actual time=11.966..11.967 rows=1 loops=1)

16.          

Initplan (forResult)

17. 1.963 11.778 ↓ 29.4 3,820 1

HashAggregate (cost=3,210.33..3,211.63 rows=130 width=4) (actual time=11.289..11.778 rows=3,820 loops=1)

  • Group Key: udr.entity_id
18. 0.322 9.815 ↓ 31.4 4,087 1

Append (cost=0.43..3,210.00 rows=130 width=4) (actual time=0.074..9.815 rows=4,087 loops=1)

19. 0.140 0.140 ↓ 1.7 5 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr (cost=0.43..16.04 rows=3 width=4) (actual time=0.073..0.140 rows=5 loops=1)

  • Index Cond: ((user_id = 2526) AND (entity_type_id = 18))
  • Heap Fetches: 5
20. 0.574 6.569 ↓ 5.5 262 1

Hash Join (cost=2,509.97..2,770.94 rows=48 width=4) (actual time=4.735..6.569 rows=262 loops=1)

  • Hash Cond: (et.contract_id = udr_1.entity_id)
21. 1.283 1.283 ↑ 1.0 4,037 1

Seq Scan on action_item_mgmt et (cost=0.00..250.37 rows=4,037 width=12) (actual time=0.003..1.283 rows=4,037 loops=1)

22. 1.545 4.712 ↓ 8.1 6,396 1

Hash (cost=2,500.07..2,500.07 rows=792 width=4) (actual time=4.712..4.712 rows=6,396 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 289kB
23. 2.591 3.167 ↓ 8.1 6,396 1

Bitmap Heap Scan on user_data_read_access udr_1 (cost=24.55..2,500.07 rows=792 width=4) (actual time=0.616..3.167 rows=6,396 loops=1)

  • Recheck Cond: ((user_id = 2526) AND (entity_type_id = 61))
  • Heap Blocks: exact=247
24. 0.576 0.576 ↓ 8.1 6,396 1

Bitmap Index Scan on user_data_read_access_uk1 (cost=0.00..24.35 rows=792 width=0) (actual time=0.576..0.576 rows=6,396 loops=1)

  • Index Cond: ((user_id = 2526) AND (entity_type_id = 61))
25. 1.765 2.784 ↓ 48.4 3,820 1

Hash Join (cost=160.74..421.73 rows=79 width=4) (actual time=0.329..2.784 rows=3,820 loops=1)

  • Hash Cond: (et_1.relation_id = udr_2.entity_id)
26. 0.716 0.716 ↑ 1.0 4,037 1

Seq Scan on action_item_mgmt et_1 (cost=0.00..250.37 rows=4,037 width=8) (actual time=0.005..0.716 rows=4,037 loops=1)

27. 0.067 0.303 ↓ 6.7 274 1

Hash (cost=160.23..160.23 rows=41 width=4) (actual time=0.303..0.303 rows=274 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
28. 0.236 0.236 ↓ 6.7 274 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_2 (cost=0.43..160.23 rows=41 width=4) (actual time=0.045..0.236 rows=274 loops=1)

  • Index Cond: ((user_id = 2526) AND (entity_type_id = 1))
  • Heap Fetches: 274
29. 39.552 5,971.975 ↓ 2.5 25,345 1

Sort (cost=602,174.72..602,199.90 rows=10,073 width=577) (actual time=5,969.112..5,971.975 rows=25,345 loops=1)

  • Sort Key: cp.start_date DESC, cp.name, (((((((cp.name)::text || ' ('::text) || to_char(cp.start_date, 'DDMONYYYY'::text)) || ' - '::text) || to_char(cp.end_date, 'DDMONYYYY'::text)) || ')'::text))
  • Sort Method: external sort Disk: 2544kB
30. 9.057 5,932.423 ↓ 2.5 25,345 1

Hash Left Join (cost=340,916.90..598,886.95 rows=10,073 width=577) (actual time=1,383.483..5,932.423 rows=25,345 loops=1)

  • Hash Cond: (action.tier_id = tier.id)
31. 8.463 5,923.351 ↓ 2.5 25,345 1

Hash Join (cost=340,915.05..598,855.80 rows=10,073 width=65) (actual time=1,383.459..5,923.351 rows=25,345 loops=1)

  • Hash Cond: (action.relation_id = rel.id)
32. 6.020 5,914.412 ↓ 2.5 25,345 1

Nested Loop (cost=340,888.28..598,802.34 rows=10,073 width=69) (actual time=1,382.973..5,914.412 rows=25,345 loops=1)

  • Join Filter: (action.id = elt.entity_id)
33. 11.824 2,258.712 ↑ 2.7 25,345 1

Hash Join (cost=340,887.85..379,528.94 rows=67,541 width=75) (actual time=1,382.948..2,258.712 rows=25,345 loops=1)

  • Hash Cond: (elt_1.entity_id = action.id)
34. 449.969 2,199.040 ↑ 27.7 25,345 1

GroupAggregate (cost=340,471.14..370,260.96 rows=700,937 width=197) (actual time=1,335.084..2,199.040 rows=25,345 loops=1)

  • Group Key: elt_1.entity_id, cp.id, elt_1.entity_type_id, entity.dynamic_metadata
35. 1,386.678 1,749.071 ↑ 1.6 445,017 1

Sort (cost=340,471.14..342,223.48 rows=700,937 width=173) (actual time=1,335.068..1,749.071 rows=445,017 loops=1)

  • Sort Key: elt_1.entity_id DESC, cp.id, entity.dynamic_metadata
  • Sort Method: external merge Disk: 86952kB
36. 137.204 362.393 ↑ 1.6 445,017 1

Hash Join (cost=341.35..152,622.92 rows=700,937 width=173) (actual time=3.239..362.393 rows=445,017 loops=1)

  • Hash Cond: (elt_1.entity_id = entity.id)
37. 50.513 222.371 ↑ 4.9 468,599 1

Nested Loop (cost=1.57..146,311.39 rows=2,274,110 width=43) (actual time=0.406..222.371 rows=468,599 loops=1)

38. 0.087 0.401 ↓ 2.5 143 1

Hash Join (cost=1.12..50.48 rows=57 width=31) (actual time=0.245..0.401 rows=143 loops=1)

  • Hash Cond: (cp.calendar_id = cc.id)
39. 0.303 0.303 ↑ 1.0 455 1

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

  • Filter: ((client_id = 1005) AND (period_type = 1))
  • Rows Removed by Filter: 1180
40. 0.002 0.011 ↑ 1.0 1 1

Hash (cost=1.11..1.11 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 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.009..0.009 rows=1 loops=1)

  • Filter: ((calendar_type_id = ANY ('{1002}'::integer[])) AND (client_id = 1005))
  • Rows Removed by Filter: 7
42. 171.457 171.457 ↑ 12.1 3,277 143

Index Scan using idx_entity_lead_time_3 on entity_lead_time elt_1 (cost=0.45..2,168.76 rows=39,722 width=12) (actual time=0.005..1.199 rows=3,277 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
43. 0.781 2.818 ↓ 1.0 3,623 1

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

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

Seq Scan on action_item_mgmt entity (cost=0.00..295.79 rows=3,519 width=142) (actual time=0.033..2.037 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: 414
45. 0.951 47.848 ↓ 9.5 3,700 1

Hash (cost=411.85..411.85 rows=389 width=20) (actual time=47.848..47.848 rows=3,700 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 206kB
46. 46.897 46.897 ↓ 9.5 3,700 1

Seq Scan on action_item_mgmt action (cost=0.00..411.85 rows=389 width=20) (actual time=12.043..46.897 rows=3,700 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. 3,649.680 3,649.680 ↑ 1.0 1 25,345

Index Scan using idx_entity_lead_time_6 on entity_lead_time elt (cost=0.43..3.23 rows=1 width=18) (actual time=0.117..0.144 rows=1 loops=25,345)

  • Index Cond: ((entity_id = elt_1.entity_id) AND (entity_type_id = 18))
  • Filter: ((max(elt_1.month_year)) = month_year)
  • Rows Removed by Filter: 284
48. 0.106 0.476 ↑ 1.0 434 1

Hash (cost=21.34..21.34 rows=434 width=4) (actual time=0.476..0.476 rows=434 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
49. 0.370 0.370 ↑ 1.0 434 1

Seq Scan on relation rel (cost=0.00..21.34 rows=434 width=4) (actual time=0.024..0.370 rows=434 loops=1)

50. 0.006 0.015 ↑ 1.0 38 1

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

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

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

52.          

SubPlan (forGroupAggregate)

53. 0.014 0.014 ↑ 1.0 1 7

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

Planning time : 4.996 ms
Execution time : 5,999.383 ms