explain.depesz.com

PostgreSQL's explain analyze made readable

Result: n08n

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 131.978 ↑ 6.0 6 1

Subquery Scan on data (cost=165,137.09..165,139.25 rows=36 width=69) (actual time=131.679..131.978 rows=6 loops=1)

2. 0.000 131.972 ↑ 6.0 6 1

GroupAggregate (cost=165,137.09..165,138.80 rows=36 width=134) (actual time=131.673..131.972 rows=6 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))
3.          

Initplan (forGroupAggregate)

4. 0.044 0.502 ↑ 1.0 1 1

Result (cost=88.85..88.86 rows=1 width=32) (actual time=0.501..0.502 rows=1 loops=1)

5.          

Initplan (forResult)

6. 0.051 0.458 ↓ 16.0 16 1

Nested Loop (cost=4.77..88.85 rows=1 width=4) (actual time=0.156..0.458 rows=16 loops=1)

7. 0.051 0.119 ↓ 1.8 16 1

Bitmap Heap Scan on secondary_data_access sda (cost=4.34..12.87 rows=9 width=4) (actual time=0.088..0.119 rows=16 loops=1)

  • Recheck Cond: (user_id = 2649)
  • Heap Blocks: exact=2
8. 0.068 0.068 ↓ 1.8 16 1

Bitmap Index Scan on idx_secondary_data_access_user_id_data_id (cost=0.00..4.34 rows=9 width=0) (actual time=0.068..0.068 rows=16 loops=1)

  • Index Cond: (user_id = 2649)
9. 0.288 0.288 ↑ 1.0 1 16

Index Scan using pk_entity_data on entity_data ed (cost=0.42..8.44 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=16)

  • Index Cond: (id = sda.data_id)
  • Filter: (entity_type_id = 61)
10. 0.018 0.333 ↑ 1.0 1 1

Result (cost=88.85..88.86 rows=1 width=32) (actual time=0.332..0.333 rows=1 loops=1)

11.          

Initplan (forResult)

12. 0.038 0.315 ↓ 0.0 0 1

Nested Loop (cost=4.77..88.85 rows=1 width=4) (actual time=0.315..0.315 rows=0 loops=1)

13. 0.030 0.069 ↓ 1.8 16 1

Bitmap Heap Scan on secondary_data_access sda_1 (cost=4.34..12.87 rows=9 width=4) (actual time=0.049..0.069 rows=16 loops=1)

  • Recheck Cond: (user_id = 2649)
  • Heap Blocks: exact=2
14. 0.039 0.039 ↓ 1.8 16 1

Bitmap Index Scan on idx_secondary_data_access_user_id_data_id (cost=0.00..4.34 rows=9 width=0) (actual time=0.039..0.039 rows=16 loops=1)

  • Index Cond: (user_id = 2649)
15. 0.208 0.208 ↓ 0.0 0 16

Index Scan using pk_entity_data on entity_data ed_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=16)

  • Index Cond: (id = sda_1.data_id)
  • Filter: (entity_type_id = 1)
  • Rows Removed by Filter: 1
16. 1.366 41.003 ↑ 1.0 1 1

Result (cost=2,711.29..2,711.30 rows=1 width=32) (actual time=41.002..41.003 rows=1 loops=1)

17.          

Initplan (forResult)

18. 11.459 39.637 ↓ 13.4 4,245 1

HashAggregate (cost=2,708.12..2,711.29 rows=317 width=4) (actual time=36.586..39.637 rows=4,245 loops=1)

  • Group Key: udr.entity_id
19. 1.754 28.178 ↓ 15.2 4,811 1

Append (cost=0.43..2,707.33 rows=317 width=4) (actual time=0.079..28.178 rows=4,811 loops=1)

20. 0.199 0.199 ↓ 4.2 25 1

Index Scan using user_data_read_access_1 on user_data_read_access udr (cost=0.43..25.39 rows=6 width=4) (actual time=0.077..0.199 rows=25 loops=1)

  • Index Cond: ((user_id = 2649) AND (entity_type_id = 18))
21. 3.334 11.951 ↓ 7.1 543 1

Hash Join (cost=1,790.00..2,087.26 rows=76 width=4) (actual time=5.193..11.951 rows=543 loops=1)

  • Hash Cond: (et.contract_id = udr_1.entity_id)
22. 3.526 3.526 ↑ 1.0 4,615 1

Seq Scan on action_item_mgmt et (cost=0.00..285.15 rows=4,615 width=12) (actual time=0.011..3.526 rows=4,615 loops=1)

23. 1.179 5.091 ↓ 2.7 1,485 1

Hash (cost=1,783.02..1,783.02 rows=558 width=4) (actual time=5.091..5.091 rows=1,485 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 69kB
24. 3.257 3.912 ↓ 2.7 1,485 1

Bitmap Heap Scan on user_data_read_access udr_1 (cost=14.15..1,783.02 rows=558 width=4) (actual time=0.936..3.912 rows=1,485 loops=1)

  • Recheck Cond: ((user_id = 2649) AND (entity_type_id = 61))
  • Heap Blocks: exact=441
25. 0.655 0.655 ↓ 2.7 1,488 1

Bitmap Index Scan on user_data_read_access_1 (cost=0.00..14.01 rows=558 width=0) (actual time=0.655..0.655 rows=1,488 loops=1)

  • Index Cond: ((user_id = 2649) AND (entity_type_id = 61))
26. 9.671 14.274 ↓ 18.1 4,243 1

Hash Join (cost=294.20..591.51 rows=235 width=4) (actual time=1.538..14.274 rows=4,243 loops=1)

  • Hash Cond: (et_1.relation_id = udr_2.entity_id)
27. 3.156 3.156 ↑ 1.0 4,615 1

Seq Scan on action_item_mgmt et_1 (cost=0.00..285.15 rows=4,615 width=8) (actual time=0.020..3.156 rows=4,615 loops=1)

28. 0.390 1.447 ↓ 3.9 334 1

Hash (cost=293.14..293.14 rows=85 width=4) (actual time=1.447..1.447 rows=334 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
29. 1.057 1.057 ↓ 3.9 334 1

Index Only Scan using user_data_read_access_uk1 on user_data_read_access udr_2 (cost=0.43..293.14 rows=85 width=4) (actual time=0.105..1.057 rows=334 loops=1)

  • Index Cond: ((user_id = 2649) AND (entity_type_id = 1))
  • Heap Fetches: 334
30. 0.282 131.633 ↓ 11.8 424 1

Sort (cost=162,248.06..162,248.15 rows=36 width=577) (actual time=131.613..131.633 rows=424 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: quicksort Memory: 84kB
31. 0.127 131.351 ↓ 11.8 424 1

Hash Left Join (cost=104,240.15..162,247.13 rows=36 width=577) (actual time=124.900..131.351 rows=424 loops=1)

  • Hash Cond: (action.tier_id = tier.id)
32. 0.308 131.206 ↓ 11.8 424 1

Nested Loop (cost=104,238.14..162,245.02 rows=36 width=65) (actual time=124.871..131.206 rows=424 loops=1)

33. 0.414 130.474 ↓ 11.8 424 1

Nested Loop (cost=104,237.86..162,221.98 rows=36 width=69) (actual time=124.862..130.474 rows=424 loops=1)

  • Join Filter: (action.id = elt.entity_id)
34. 0.245 126.244 ↑ 29.9 424 1

Hash Join (cost=104,237.43..114,608.11 rows=12,677 width=75) (actual time=124.852..126.244 rows=424 loops=1)

  • Hash Cond: (elt_1.entity_id = action.id)
35. 1.137 6.287 ↑ 443.7 424 1

GroupAggregate (cost=103,763.79..111,758.98 rows=188,122 width=193) (actual time=5.101..6.287 rows=424 loops=1)

  • Group Key: elt_1.entity_id, cp.id, elt_1.entity_type_id, entity.dynamic_metadata
36. 0.414 5.150 ↑ 360.4 522 1

Sort (cost=103,763.79..104,234.10 rows=188,122 width=169) (actual time=5.085..5.150 rows=522 loops=1)

  • Sort Key: elt_1.entity_id DESC, cp.id, entity.dynamic_metadata
  • Sort Method: quicksort Memory: 174kB
37. 0.156 4.736 ↑ 360.4 522 1

Hash Join (cost=382.59..71,207.57 rows=188,122 width=169) (actual time=3.477..4.736 rows=522 loops=1)

  • Hash Cond: (elt_1.entity_id = entity.id)
38. 0.396 1.562 ↑ 949.2 782 1

Nested Loop (cost=1.58..68,877.53 rows=742,263 width=43) (actual time=0.436..1.562 rows=782 loops=1)

39. 0.067 0.334 ↓ 3.5 208 1

Hash Join (cost=1.14..54.69 rows=59 width=31) (actual time=0.230..0.334 rows=208 loops=1)

  • Hash Cond: (cp.calendar_id = cc.id)
40. 0.259 0.259 ↑ 1.0 532 1

Seq Scan on calendar_periods cp (cost=0.00..51.49 rows=535 width=35) (actual time=0.007..0.259 rows=532 loops=1)

  • Filter: ((client_id = 1005) AND (period_type = 1))
  • Rows Removed by Filter: 1234
41. 0.003 0.008 ↑ 1.0 1 1

Hash (cost=1.12..1.12 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on calendar cc (cost=0.00..1.12 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)

  • Filter: ((calendar_type_id = ANY ('{1002}'::integer[])) AND (client_id = 1005))
  • Rows Removed by Filter: 8
43. 0.832 0.832 ↑ 3,121.8 4 208

Index Scan using idx_entity_lead_time_3 on entity_lead_time elt_1 (cost=0.45..1,041.62 rows=12,487 width=12) (actual time=0.003..0.004 rows=4 loops=208)

  • 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
44. 0.805 3.018 ↓ 1.1 3,800 1

Hash (cost=337.07..337.07 rows=3,515 width=138) (actual time=3.018..3.018 rows=3,800 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 710kB
45. 2.213 2.213 ↓ 1.1 3,800 1

Seq Scan on action_item_mgmt entity (cost=0.00..337.07 rows=3,515 width=138) (actual time=0.009..2.213 rows=3,800 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: 815
46. 1.777 119.712 ↓ 12.9 4,018 1

Hash (cost=469.75..469.75 rows=311 width=20) (actual time=119.712..119.712 rows=4,018 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 222kB
47. 117.935 117.935 ↓ 12.9 4,018 1

Seq Scan on action_item_mgmt action (cost=0.00..469.75 rows=311 width=20) (actual time=42.051..117.935 rows=4,018 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: 597
48. 3.816 3.816 ↑ 1.0 1 424

Index Scan using idx_entity_lead_time_6 on entity_lead_time elt (cost=0.43..3.74 rows=1 width=18) (actual time=0.009..0.009 rows=1 loops=424)

  • Index Cond: ((entity_id = elt_1.entity_id) AND (entity_type_id = elt_1.entity_type_id))
  • Filter: ((max(elt_1.month_year)) = month_year)
  • Rows Removed by Filter: 10
49. 0.424 0.424 ↑ 1.0 1 424

Index Only Scan using pk_relation on relation rel (cost=0.28..0.64 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=424)

  • Index Cond: (id = action.relation_id)
  • Heap Fetches: 424
50. 0.010 0.018 ↑ 1.0 45 1

Hash (cost=1.45..1.45 rows=45 width=520) (actual time=0.018..0.018 rows=45 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
51. 0.008 0.008 ↑ 1.0 45 1

Seq Scan on tier (cost=0.00..1.45 rows=45 width=520) (actual time=0.004..0.008 rows=45 loops=1)

52.          

SubPlan (forGroupAggregate)

53. 0.012 0.012 ↑ 1.0 1 6

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