explain.depesz.com

PostgreSQL's explain analyze made readable

Result: b58l

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 6,789.848 ↓ 5.5 11 1

Limit (cost=19,173.21..19,173.23 rows=2 width=194) (actual time=6,789.843..6,789.848 rows=11 loops=1)

2. 0.005 6,789.846 ↓ 5.5 11 1

Subquery Scan on lead_report (cost=19,173.21..19,173.23 rows=2 width=194) (actual time=6,789.842..6,789.846 rows=11 loops=1)

3. 0.000 6,789.841 ↓ 5.5 11 1

Sort (cost=19,173.21..19,173.21 rows=2 width=198) (actual time=6,789.839..6,789.841 rows=11 loops=1)

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

Initplan (for Sort)

5. 0.223 2.763 ↓ 84.5 507 1

HashAggregate (cost=4,278.84..4,278.90 rows=6 width=4) (actual time=2.708..2.763 rows=507 loops=1)

  • Group Key: edl.entity_id
6.          

CTE user_data_filter

7. 0.407 0.407 ↑ 1.1 940 1

Index Scan using idx_user_data_access_user_id on user_data_access (cost=0.43..1,138.63 rows=1,066 width=48) (actual time=0.020..0.407 rows=940 loops=1)

  • Index Cond: (user_id = 10,254)
  • Filter: (NOT deleted)
8. 0.043 2.540 ↓ 95.8 575 1

Append (cost=0.42..3,140.20 rows=6 width=4) (actual time=0.484..2.540 rows=575 loops=1)

9. 0.000 2.428 ↓ 115.0 575 1

Nested Loop (cost=0.42..1,334.37 rows=5 width=4) (actual time=0.484..2.428 rows=575 loops=1)

10. 0.634 0.634 ↓ 1.8 940 1

CTE Scan on user_data_filter da (cost=0.00..21.32 rows=533 width=8) (actual time=0.021..0.634 rows=940 loops=1)

  • Filter: self_access
11. 1.880 1.880 ↑ 1.0 1 940

Index Scan using idx_entity_data_link_entity_type_id_ancestor_type_id_ancestor_i on entity_data_link edl (cost=0.42..2.45 rows=1 width=12) (actual time=0.001..0.002 rows=1 loops=940)

  • Index Cond: ((entity_type_id = 18) AND (ancestor_type_id = da.entity_type_id) AND (ancestor_id = da.entity_id))
  • Filter: (NOT deleted)
  • Rows Removed by Filter: 0
12. 0.001 0.069 ↓ 0.0 0 1

Nested Loop (cost=0.85..1,805.74 rows=1 width=4) (actual time=0.069..0.069 rows=0 loops=1)

  • Join Filter: (edc.ancestor_type_id = ANY (da_1.access_type_ids))
13. 0.000 0.068 ↓ 0.0 0 1

Nested Loop (cost=0.42..1,785.80 rows=38 width=40) (actual time=0.068..0.068 rows=0 loops=1)

14. 0.068 0.068 ↓ 0.0 0 1

CTE Scan on user_data_filter da_1 (cost=0.00..21.32 rows=533 width=40) (actual time=0.068..0.068 rows=0 loops=1)

  • Filter: (NOT self_access)
  • Rows Removed by Filter: 940
15. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_entity_data_link_ancestor_type_id_ancestor_id on entity_data_link edl_1 (cost=0.42..3.30 rows=1 width=16) (never executed)

  • Index Cond: ((ancestor_type_id = da_1.entity_type_id) AND (ancestor_id = da_1.entity_id))
  • Filter: (parent AND (NOT deleted))
16. 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.50 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)
17. 0.076 6,789.812 ↓ 5.5 11 1

Nested Loop Left Join (cost=7.01..14,894.30 rows=2 width=198) (actual time=1,432.205..6,789.812 rows=11 loops=1)

18. 0.016 6,789.692 ↓ 5.5 11 1

Nested Loop Left Join (cost=6.72..14,889.20 rows=2 width=145) (actual time=1,432.171..6,789.692 rows=11 loops=1)

19. 0.019 6,789.643 ↓ 5.5 11 1

Nested Loop Left Join (cost=6.58..14,884.44 rows=2 width=143) (actual time=1,432.163..6,789.643 rows=11 loops=1)

20. 0.029 6,789.580 ↓ 5.5 11 1

Nested Loop Left Join (cost=6.29..14,879.44 rows=2 width=128) (actual time=1,432.152..6,789.580 rows=11 loops=1)

21. 0.023 6,789.507 ↓ 5.5 11 1

Nested Loop Left Join (cost=6.02..14,874.45 rows=2 width=111) (actual time=1,432.139..6,789.507 rows=11 loops=1)

22. 1.676 6,789.440 ↓ 5.5 11 1

Nested Loop Semi Join (cost=5.88..14,870.75 rows=2 width=99) (actual time=1,432.123..6,789.440 rows=11 loops=1)

23. 5.118 5.118 ↓ 253.5 507 1

Index Scan using action_item_mgmt_id_idx on action_item_mgmt action (cost=0.28..22.12 rows=2 width=99) (actual time=2.874..5.118 rows=507 loops=1)

  • Index Cond: (id = ANY ($7))
  • Filter: ((NOT deleted) AND (client_id = 1,002))
24. 1,446.822 6,782.646 ↓ 0.0 0 507

Hash Join (cost=5.60..14,653.59 rows=161 width=4) (actual time=13.378..13.378 rows=0 loops=507)

  • Hash Cond: (wftlt.lead_time_id = wfelt.lead_time_id)
25. 5,158.374 5,158.374 ↑ 1.0 92,850 246

Seq Scan on work_flow_task_lead_time wftlt (cost=0.00..13,918.04 rows=97,113 width=4) (actual time=0.011..20.969 rows=92,850 loops=246)

  • Filter: (lead_time_type = 3)
  • Rows Removed by Filter: 51,646
26. 17.745 177.450 ↓ 73.0 292 507

Hash (cost=5.55..5.55 rows=4 width=8) (actual time=0.350..0.350 rows=292 loops=507)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
27. 159.705 159.705 ↓ 73.0 292 507

Index Scan using idx_work_flow_entity_lead_time_entity on work_flow_entity_lead_time wfelt (cost=0.56..5.55 rows=4 width=8) (actual time=0.007..0.315 rows=292 loops=507)

  • Index Cond: ((entity_type_id = 18) AND (entity_id = action.id))
28. 0.044 0.044 ↑ 1.0 1 11

Index Scan using pk_time_zone on time_zone tz (cost=0.14..1.81 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=11)

  • Index Cond: (id = action.time_zone_id)
29. 0.044 0.044 ↑ 1.0 1 11

Index Scan using pk_relation on relation re (cost=0.28..2.50 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=11)

  • Index Cond: (action.relation_id = id)
30. 0.044 0.044 ↑ 1.0 1 11

Index Scan using pk_work_flow_status on work_flow_status ets (cost=0.28..2.50 rows=1 width=23) (actual time=0.004..0.004 rows=1 loops=11)

  • Index Cond: (action.status_id = id)
31. 0.033 0.033 ↑ 1.0 1 11

Index Scan using pk_tier on tier (cost=0.14..2.36 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=11)

  • Index Cond: (action.tier_id = id)
32. 0.044 0.044 ↓ 0.0 0 11

Index Scan using pk_governance_body_child on governance_body_child gbc (cost=0.29..2.51 rows=1 width=24) (actual time=0.004..0.004 rows=0 loops=11)

  • Index Cond: (action.governance_body_child_id = id)
Planning time : 3.218 ms
Execution time : 6,790.126 ms