explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pWU0

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1,588.353 ↓ 5.0 20 1

Limit (cost=8,139,795.38..8,139,795.43 rows=4 width=950) (actual time=1,588.345..1,588.353 rows=20 loops=1)

2. 0.006 1,588.350 ↓ 5.0 20 1

Subquery Scan on lead_report (cost=8,139,795.38..8,139,795.43 rows=4 width=950) (actual time=1,588.344..1,588.350 rows=20 loops=1)

3. 0.000 1,588.344 ↓ 5.0 20 1

Sort (cost=8,139,795.38..8,139,795.39 rows=4 width=954) (actual time=1,588.342..1,588.344 rows=20 loops=1)

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

CTE cte_exists

5. 123.878 1,425.663 ↑ 2,673.0 77,609 1

Merge Join (cost=330,009.23..3,444,407.04 rows=207,448,592 width=4) (actual time=1,404.845..1,425.663 rows=77,609 loops=1)

  • Merge Cond: (wftlt.lead_time_id = wfelt.lead_time_id)
6. 3.621 9.449 ↑ 1.0 12,792 1

Sort (cost=1,592.32..1,624.33 rows=12,804 width=4) (actual time=8.495..9.449 rows=12,792 loops=1)

  • Sort Key: wftlt.lead_time_id
  • Sort Method: quicksort Memory: 985kB
7. 5.828 5.828 ↑ 1.0 12,804 1

Seq Scan on work_flow_task_lead_time wftlt (cost=0.00..718.81 rows=12,804 width=4) (actual time=0.019..5.828 rows=12,804 loops=1)

  • Filter: (lead_time_type = 3)
  • Rows Removed by Filter: 9,593
8. 103.148 1,292.336 ↓ 1.1 1,157,035 1

Materialize (cost=328,416.91..333,626.72 rows=1,041,961 width=8) (actual time=1,079.093..1,292.336 rows=1,157,035 loops=1)

9. 497.562 1,189.188 ↓ 1.0 1,079,746 1

Sort (cost=328,416.91..331,021.82 rows=1,041,961 width=8) (actual time=1,079.089..1,189.188 rows=1,079,746 loops=1)

  • Sort Key: wfelt.lead_time_id
  • Sort Method: external merge Disk: 19,024kB
10. 363.885 691.626 ↓ 1.0 1,079,746 1

Hash Left Join (cost=19,743.63..210,019.88 rows=1,041,961 width=8) (actual time=82.529..691.626 rows=1,079,746 loops=1)

  • Hash Cond: (wfelt.entity_id = action_1.id)
11. 247.953 326.095 ↓ 1.0 1,079,746 1

Bitmap Heap Scan on work_flow_entity_lead_time wfelt (cost=19,487.64..207,028.15 rows=1,041,961 width=8) (actual time=80.861..326.095 rows=1,079,746 loops=1)

  • Recheck Cond: (entity_type_id = 18)
  • Heap Blocks: exact=15,797
12. 78.142 78.142 ↓ 1.0 1,079,747 1

Bitmap Index Scan on idx_work_flow_entity_lead_time_entity (cost=0.00..19,227.15 rows=1,041,961 width=0) (actual time=78.142..78.142 rows=1,079,747 loops=1)

  • Index Cond: (entity_type_id = 18)
13. 0.667 1.646 ↓ 1.0 4,945 1

Hash (cost=194.31..194.31 rows=4,935 width=4) (actual time=1.646..1.646 rows=4,945 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 238kB
14. 0.979 0.979 ↓ 1.0 4,945 1

Index Only Scan using pk_action_item_mgmt on action_item_mgmt action_1 (cost=0.28..194.31 rows=4,935 width=4) (actual time=0.032..0.979 rows=4,945 loops=1)

  • Heap Fetches: 680
15.          

Initplan (for Sort)

16. 2.408 122.725 ↓ 79.0 4,264 1

HashAggregate (cost=27,618.71..27,619.25 rows=54 width=4) (actual time=122.154..122.725 rows=4,264 loops=1)

  • Group Key: edl.entity_id
17.          

CTE user_data_filter

18. 2.880 3.342 ↑ 1.0 7,183 1

Bitmap Heap Scan on user_data_access (cost=141.52..9,939.21 rows=7,359 width=23) (actual time=0.496..3.342 rows=7,183 loops=1)

  • Recheck Cond: (user_id = 2,649)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 5
  • Heap Blocks: exact=223
19. 0.462 0.462 ↑ 1.0 7,189 1

Bitmap Index Scan on idx_user_data_access_user_id (cost=0.00..139.68 rows=7,367 width=0) (actual time=0.462..0.462 rows=7,189 loops=1)

  • Index Cond: (user_id = 2,649)
20. 0.494 120.317 ↓ 160.6 8,674 1

Append (cost=4,729.83..17,679.37 rows=54 width=4) (actual time=24.706..120.317 rows=8,674 loops=1)

21. 3.705 30.239 ↓ 163.7 8,674 1

Merge Join (cost=4,729.83..4,918.13 rows=53 width=4) (actual time=24.705..30.239 rows=8,674 loops=1)

  • Merge Cond: ((edl.ancestor_id = da.entity_id) AND (edl.ancestor_type_id = da.entity_type_id))
22. 6.105 17.325 ↑ 1.0 20,361 1

Sort (cost=4,364.69..4,418.08 rows=21,356 width=12) (actual time=16.249..17.325 rows=20,361 loops=1)

  • Sort Key: edl.ancestor_id, edl.ancestor_type_id
  • Sort Method: quicksort Memory: 1,723kB
23. 9.971 11.220 ↑ 1.0 20,361 1

Bitmap Heap Scan on entity_data_link edl (cost=407.45..2,828.94 rows=21,356 width=12) (actual time=1.398..11.220 rows=20,361 loops=1)

  • Recheck Cond: (entity_type_id = 18)
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 243
  • Heap Blocks: exact=957
24. 1.249 1.249 ↑ 1.0 20,609 1

Bitmap Index Scan on idx_entity_data_link_entity_type_id_entity_id (cost=0.00..402.12 rows=21,559 width=0) (actual time=1.249..1.249 rows=20,609 loops=1)

  • Index Cond: (entity_type_id = 18)
25. 3.187 9.209 ↓ 4.2 15,582 1

Sort (cost=365.14..374.34 rows=3,680 width=8) (actual time=8.443..9.209 rows=15,582 loops=1)

  • Sort Key: da.entity_id, da.entity_type_id
  • Sort Method: quicksort Memory: 529kB
26. 6.022 6.022 ↓ 2.0 7,183 1

CTE Scan on user_data_filter da (cost=0.00..147.18 rows=3,680 width=8) (actual time=0.504..6.022 rows=7,183 loops=1)

  • Filter: self_access
27. 0.001 89.584 ↓ 0.0 0 1

Nested Loop (cost=12,039.56..12,760.70 rows=1 width=4) (actual time=89.584..89.584 rows=0 loops=1)

  • Join Filter: (edc.ancestor_type_id = ANY (da_1.access_type_ids))
28. 0.003 89.583 ↓ 0.0 0 1

Merge Join (cost=12,039.14..12,637.23 rows=190 width=40) (actual time=89.583..89.583 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))
29. 49.496 89.086 ↑ 75,812.0 1 1

Sort (cost=11,674.01..11,863.54 rows=75,812 width=16) (actual time=89.086..89.086 rows=1 loops=1)

  • Sort Key: edl_1.ancestor_id, edl_1.ancestor_type_id
  • Sort Method: external merge Disk: 1,968kB
30. 39.590 39.590 ↓ 1.0 77,261 1

Seq Scan on entity_data_link edl_1 (cost=0.00..5,529.39 rows=75,812 width=16) (actual time=0.020..39.590 rows=77,261 loops=1)

  • Filter: (parent AND (NOT deleted))
  • Rows Removed by Filter: 260,572
31. 0.009 0.494 ↓ 0.0 0 1

Sort (cost=365.14..374.34 rows=3,680 width=40) (actual time=0.494..0.494 rows=0 loops=1)

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

CTE Scan on user_data_filter da_1 (cost=0.00..147.18 rows=3,680 width=40) (actual time=0.484..0.485 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 7,183
33. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edc (cost=0.42..0.63 rows=1 width=12) (never executed)

  • Index Cond: ((entity_type_id = 18) AND (ancestor_type_id = edl_1.entity_type_id) AND (ancestor_id = edl_1.entity_id))
  • Filter: (NOT deleted)
34. 0.310 1,588.256 ↓ 29.5 118 1

Nested Loop Left Join (cost=4,667,639.49..4,667,769.05 rows=4 width=954) (actual time=1,583.576..1,588.256 rows=118 loops=1)

35. 0.348 1,587.828 ↓ 29.5 118 1

Nested Loop Left Join (cost=4,667,639.20..4,667,735.66 rows=4 width=988) (actual time=1,583.559..1,587.828 rows=118 loops=1)

  • Join Filter: (action.tier_id = tier.id)
  • Rows Removed by Join Filter: 4,810
36. 0.111 1,587.244 ↓ 29.5 118 1

Nested Loop Left Join (cost=4,667,639.20..4,667,731.11 rows=4 width=476) (actual time=1,583.536..1,587.244 rows=118 loops=1)

37. 0.041 1,587.015 ↓ 29.5 118 1

Nested Loop Left Join (cost=4,667,638.92..4,667,699.68 rows=4 width=462) (actual time=1,583.526..1,587.015 rows=118 loops=1)

38. 0.047 1,586.738 ↓ 29.5 118 1

Hash Left Join (cost=4,667,638.64..4,667,675.39 rows=4 width=447) (actual time=1,583.508..1,586.738 rows=118 loops=1)

  • Hash Cond: (action.time_zone_id = tz.id)
39. 0.410 1,586.664 ↓ 29.5 118 1

Hash Join (cost=4,667,636.72..4,667,673.45 rows=4 width=177) (actual time=1,583.471..1,586.664 rows=118 loops=1)

  • Hash Cond: (action.id = cte_exists.id)
40. 2.852 129.921 ↓ 473.8 4,264 1

Bitmap Heap Scan on action_item_mgmt action (cost=38.90..75.57 rows=9 width=177) (actual time=127.113..129.921 rows=4,264 loops=1)

  • Recheck Cond: (id = ANY ($4))
  • Filter: ((NOT deleted) AND (client_id = 1,005))
  • Heap Blocks: exact=385
41. 127.069 127.069 ↓ 426.4 4,264 1

Bitmap Index Scan on pk_action_item_mgmt (cost=0.00..38.90 rows=10 width=0) (actual time=127.069..127.069 rows=4,264 loops=1)

  • Index Cond: (id = ANY ($4))
42. 0.024 1,456.333 ↑ 1.3 157 1

Hash (cost=4,667,595.32..4,667,595.32 rows=200 width=4) (actual time=1,456.333..1,456.333 rows=157 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
43. 12.602 1,456.309 ↑ 1.3 157 1

HashAggregate (cost=4,667,593.32..4,667,595.32 rows=200 width=4) (actual time=1,456.291..1,456.309 rows=157 loops=1)

  • Group Key: cte_exists.id
44. 1,443.707 1,443.707 ↑ 2,673.0 77,609 1

CTE Scan on cte_exists (cost=0.00..4,148,971.84 rows=207,448,592 width=4) (actual time=1,404.847..1,443.707 rows=77,609 loops=1)

45. 0.007 0.027 ↑ 1.0 41 1

Hash (cost=1.41..1.41 rows=41 width=278) (actual time=0.027..0.027 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
46. 0.020 0.020 ↑ 1.0 41 1

Seq Scan on time_zone tz (cost=0.00..1.41 rows=41 width=278) (actual time=0.015..0.020 rows=41 loops=1)

47. 0.236 0.236 ↑ 1.0 1 118

Index Scan using pk_relation on relation re (cost=0.28..6.07 rows=1 width=19) (actual time=0.002..0.002 rows=1 loops=118)

  • Index Cond: (action.relation_id = id)
48. 0.118 0.118 ↑ 1.0 1 118

Index Scan using pk_work_flow_status on work_flow_status ets (cost=0.28..7.86 rows=1 width=22) (actual time=0.001..0.001 rows=1 loops=118)

  • Index Cond: (action.status_id = id)
49. 0.224 0.236 ↑ 1.2 42 118

Materialize (cost=0.00..1.73 rows=49 width=520) (actual time=0.000..0.002 rows=42 loops=118)

50. 0.012 0.012 ↑ 1.0 49 1

Seq Scan on tier (cost=0.00..1.49 rows=49 width=520) (actual time=0.007..0.012 rows=49 loops=1)

51. 0.118 0.118 ↓ 0.0 0 118

Index Scan using pk_governance_body_child on governance_body_child gbc (cost=0.29..8.30 rows=1 width=26) (actual time=0.001..0.001 rows=0 loops=118)

  • Index Cond: (action.governance_body_child_id = id)
Planning time : 6.971 ms
Execution time : 1,592.713 ms