explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RNvh

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 32,953.916 ↓ 0.0 0 1

Limit (cost=10,983.72..10,983.74 rows=1 width=199) (actual time=32,953.916..32,953.916 rows=0 loops=1)

2. 0.002 32,953.914 ↓ 0.0 0 1

Subquery Scan on lead_report (cost=10,983.72..10,983.74 rows=1 width=199) (actual time=32,953.913..32,953.914 rows=0 loops=1)

3. 0.000 32,953.912 ↓ 0.0 0 1

Sort (cost=10,983.72..10,983.73 rows=1 width=203) (actual time=32,953.911..32,953.912 rows=0 loops=1)

  • Sort Key: action.client_entity_seq_id DESC
  • Sort Method: quicksort Memory: 25kB
4.          

Initplan (for Sort)

5. 0.035 36.654 ↓ 12.5 25 1

Unique (cost=10,888.66..10,888.67 rows=2 width=4) (actual time=36.604..36.654 rows=25 loops=1)

6.          

CTE user_data_filter

7. 2.819 2.819 ↓ 1.1 2,805 1

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.56..2,857.70 rows=2,562 width=23) (actual time=0.014..2.819 rows=2,805 loops=1)

  • Index Cond: (user_id = 4,805)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 3
8. 0.039 36.619 ↓ 13.0 26 1

Sort (cost=8,030.96..8,030.97 rows=2 width=4) (actual time=36.602..36.619 rows=26 loops=1)

  • Sort Key: edl.entity_id
  • Sort Method: quicksort Memory: 26kB
9. 0.034 36.580 ↓ 13.0 26 1

Append (cost=1,847.83..8,030.95 rows=2 width=4) (actual time=14.135..36.580 rows=26 loops=1)

10. 3.094 19.540 ↓ 26.0 26 1

Merge Join (cost=1,847.83..1,870.95 rows=1 width=4) (actual time=14.133..19.540 rows=26 loops=1)

  • Merge Cond: ((edl.ancestor_id = da.entity_id) AND (edl.ancestor_type_id = da.entity_type_id))
11. 3.203 5.367 ↓ 1.1 1,931 1

Sort (cost=1,730.48..1,734.98 rows=1,800 width=12) (actual time=4.167..5.367 rows=1,931 loops=1)

  • Sort Key: edl.ancestor_id, edl.ancestor_type_id
  • Sort Method: quicksort Memory: 212kB
12. 2.164 2.164 ↓ 1.4 2,456 1

Index Scan using idx_entity_data_link_entity_type_id on entity_data_link edl (cost=0.43..1,633.15 rows=1,800 width=12) (actual time=0.015..2.164 rows=2,456 loops=1)

  • Index Cond: (entity_type_id = 18)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 33
13. 4.069 11.079 ↓ 2.2 2,829 1

Sort (cost=117.36..120.56 rows=1,281 width=8) (actual time=9.340..11.079 rows=2,829 loops=1)

  • Sort Key: da.entity_id, da.entity_type_id
  • Sort Method: quicksort Memory: 228kB
14. 7.010 7.010 ↓ 2.2 2,805 1

CTE Scan on user_data_filter da (cost=0.00..51.24 rows=1,281 width=8) (actual time=0.018..7.010 rows=2,805 loops=1)

  • Filter: self_access
15. 0.003 17.006 ↓ 0.0 0 1

Merge Join (cost=6,144.87..6,159.97 rows=1 width=4) (actual time=17.005..17.006 rows=0 loops=1)

  • Merge Cond: ((edl_1.ancestor_id = da_1.entity_id) AND (edl_1.ancestor_type_id = da_1.entity_type_id))
  • Join Filter: (edc.ancestor_type_id = ANY (da_1.access_type_ids))
16. 1.599 16.791 ↑ 730.0 1 1

Sort (cost=6,027.51..6,029.33 rows=730 width=16) (actual time=16.790..16.791 rows=1 loops=1)

  • Sort Key: edl_1.ancestor_id, edl_1.ancestor_type_id
  • Sort Method: quicksort Memory: 140kB
17. 4.247 15.192 ↓ 2.7 1,950 1

Gather (cost=1,027.10..5,992.79 rows=730 width=16) (actual time=1.361..15.192 rows=1,950 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
18. 3.057 10.945 ↓ 2.3 975 2 / 2

Nested Loop (cost=27.10..4,919.79 rows=429 width=16) (actual time=0.489..10.945 rows=975 loops=2)

19. 1.349 1.748 ↓ 1.2 1,228 2 / 2

Parallel Bitmap Heap Scan on entity_data_link edc (cost=26.67..2,286.00 rows=1,059 width=12) (actual time=0.441..1.748 rows=1,228 loops=2)

  • Recheck Cond: (entity_type_id = 18)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 16
  • Heap Blocks: exact=96
20. 0.400 0.400 ↓ 1.1 2,489 1 / 2

Bitmap Index Scan on idx_entity_data_link_entity_type_id_entity_id (cost=0.00..26.22 rows=2,265 width=0) (actual time=0.799..0.799 rows=2,489 loops=1)

  • Index Cond: (entity_type_id = 18)
21. 6.140 6.140 ↑ 1.0 1 2,456 / 2

Index Scan using idx_entity_data_link_entity_type_id_entity_id on entity_data_link edl_1 (cost=0.43..2.48 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=2,456)

  • Index Cond: ((entity_type_id = edc.ancestor_type_id) AND (entity_id = edc.ancestor_id))
  • Filter: (parent AND (NOT deleted))
  • Rows Removed by Filter: 2
22. 0.005 0.212 ↓ 0.0 0 1

Sort (cost=117.36..120.56 rows=1,281 width=40) (actual time=0.211..0.212 rows=0 loops=1)

  • Sort Key: da_1.entity_id, da_1.entity_type_id
  • Sort Method: quicksort Memory: 25kB
23. 0.207 0.207 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..51.24 rows=1,281 width=40) (actual time=0.206..0.207 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 2,805
24. 0.004 32,953.908 ↓ 0.0 0 1

Merge Semi Join (cost=41.04..95.04 rows=1 width=203) (actual time=32,953.907..32,953.908 rows=0 loops=1)

  • Merge Cond: (action.id = wfelt.entity_id)
25. 0.006 36.778 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.26..29.15 rows=1 width=581) (actual time=36.777..36.778 rows=1 loops=1)

26. 0.011 36.765 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.97..26.65 rows=1 width=549) (actual time=36.765..36.765 rows=1 loops=1)

  • Join Filter: (action.tier_id = tier.id)
  • Rows Removed by Join Filter: 12
27. 0.003 36.742 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.97..25.16 rows=1 width=547) (actual time=36.741..36.742 rows=1 loops=1)

28. 0.004 36.727 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.70..22.66 rows=1 width=531) (actual time=36.726..36.727 rows=1 loops=1)

29. 0.006 36.709 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.28..20.02 rows=1 width=507) (actual time=36.709..36.709 rows=1 loops=1)

  • Join Filter: (tz.id = action.time_zone_id)
  • Rows Removed by Join Filter: 2
30. 36.696 36.696 ↑ 1.0 1 1

Index Scan using action_item_mgmt_id_idx on action_item_mgmt action (cost=0.28..18.10 rows=1 width=495) (actual time=36.695..36.696 rows=1 loops=1)

  • Index Cond: (id = ANY ($4))
  • Filter: ((NOT deleted) AND (client_id = 1,003))
31. 0.007 0.007 ↑ 13.7 3 1

Seq Scan on time_zone tz (cost=0.00..1.41 rows=41 width=20) (actual time=0.005..0.007 rows=3 loops=1)

32. 0.014 0.014 ↑ 1.0 1 1

Index Scan using pk_relation on relation re (cost=0.42..2.64 rows=1 width=28) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (id = action.relation_id)
33. 0.012 0.012 ↑ 1.0 1 1

Index Scan using pk_work_flow_status on work_flow_status ets (cost=0.28..2.50 rows=1 width=24) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (id = action.status_id)
34. 0.012 0.012 ↑ 1.7 13 1

Seq Scan on tier (cost=0.00..1.22 rows=22 width=10) (actual time=0.004..0.012 rows=13 loops=1)

35. 0.007 0.007 ↓ 0.0 0 1

Index Scan using pk_governance_body_child on governance_body_child gbc (cost=0.28..2.50 rows=1 width=36) (actual time=0.006..0.007 rows=0 loops=1)

  • Index Cond: (id = action.governance_body_child_id)
36. 14,722.401 32,917.126 ↓ 0.0 0 1

Nested Loop (cost=0.71..6,277.46 rows=482 width=8) (actual time=32,917.126..32,917.126 rows=0 loops=1)

  • Join Filter: (wfelt.lead_time_id = wftlt.lead_time_id)
  • Rows Removed by Join Filter: 23,747,346
37. 0.536 2.181 ↓ 194.0 194 1

Nested Loop (cost=0.71..178.46 rows=1 width=12) (actual time=0.549..2.181 rows=194 loops=1)

38. 0.770 0.770 ↓ 1.5 25 1

Index Scan using action_item_mgmt_id_idx on action_item_mgmt action_1 (cost=0.28..151.90 rows=17 width=4) (actual time=0.524..0.770 rows=25 loops=1)

  • Filter: ((NOT deleted) AND (client_id = 1,003))
  • Rows Removed by Filter: 721
39. 0.875 0.875 ↓ 8.0 8 25

Index Only Scan using idx_work_flow_entity_lead_time_1 on work_flow_entity_lead_time wfelt (cost=0.43..1.55 rows=1 width=8) (actual time=0.013..0.035 rows=8 loops=25)

  • Index Cond: ((entity_type_id = 18) AND (entity_id = action_1.id))
  • Heap Fetches: 0
40. 18,192.544 18,192.544 ↑ 1.0 122,409 194

Seq Scan on work_flow_task_lead_time wftlt (cost=0.00..4,567.95 rows=122,484 width=4) (actual time=0.003..93.776 rows=122,409 loops=194)

  • Filter: (lead_time_type = 3)
  • Rows Removed by Filter: 7,667
Planning time : 3.322 ms
Execution time : 32,954.388 ms