explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uou7

Settings
# exclusive inclusive rows x rows loops node
1. 2.519 51,309.671 ↓ 0.0 0 1

Nested Loop Semi Join (cost=4,625.64..5,327.96 rows=1 width=153) (actual time=51,309.671..51,309.671 rows=0 loops=1)

2.          

CTE rsatemp

3. 4.739 220.425 ↓ 11,952.0 11,952 1

Nested Loop (cost=6.78..4,599.67 rows=1 width=24) (actual time=1.191..220.425 rows=11,952 loops=1)

4. 0.216 0.216 ↓ 20.7 145 1

Index Scan using pk_rule_stops on rule_stops rs1 (cost=0.28..28.40 rows=7 width=16) (actual time=0.009..0.216 rows=145 loops=1)

  • Index Cond: (cid = 13695)
5. 203.435 215.470 ↓ 82.0 82 145

Bitmap Heap Scan on rule_stop_approvals rsa1 (cost=6.51..653.03 rows=1 width=20) (actual time=0.182..1.486 rows=82 loops=145)

  • Recheck Cond: (rule_stop_id = rs1.id)
  • Filter: ((approval_company_user_id IS NULL) AND (approval_datetime IS NULL) AND (approval_ip_address IS NULL) AND (cid = 13695))
  • Rows Removed by Filter: 443
  • Heap Blocks: exact=18202
6. 12.035 12.035 ↓ 1.9 526 145

Bitmap Index Scan on idx_rule_stop_approvals_rule_stop_id (cost=0.00..6.50 rows=278 width=0) (actual time=0.083..0.083 rows=526 loops=145)

  • Index Cond: (rule_stop_id = rs1.id)
7. 6.533 51,289.128 ↓ 6,008.0 6,008 1

Nested Loop Left Join (cost=17.39..719.63 rows=1 width=161) (actual time=242.084..51,289.128 rows=6,008 loops=1)

8. 9.015 50,874.051 ↓ 6,008.0 6,008 1

Nested Loop Left Join (cost=16.97..714.00 rows=1 width=161) (actual time=241.076..50,874.051 rows=6,008 loops=1)

  • Join Filter: ((rs2.order_num < rsatemp.order_num) AND (rs2.route_rule_id = rs.route_rule_id))
  • Rows Removed by Join Filter: 1
  • Filter: (rsa2.id IS NULL)
9. 24,127.109 50,786.932 ↓ 6,008.0 6,008 1

Nested Loop Left Join (cost=15.84..691.85 rows=1 width=169) (actual time=240.597..50,786.932 rows=6,008 loops=1)

  • Join Filter: ((rsatemp.order_num < rs.order_num) AND (rsatemp.cid = rsa.cid) AND (rsatemp.reference_number = rsa.reference_number) AND (rsatemp.route_rule_reference_type_id = rsa.route_rule_reference_type_id) AND (rsatemp.route_rule_id = rs.route_rule_id))
  • Rows Removed by Join Filter: 115990286
  • Filter: (rsatemp.id IS NULL)
  • Rows Removed by Filter: 3874
10. 5.455 126.353 ↓ 9,705.0 9,705 1

Nested Loop Left Join (cost=15.84..691.81 rows=1 width=169) (actual time=9.557..126.353 rows=9,705 loops=1)

11. 12.471 111.193 ↓ 9,705.0 9,705 1

Nested Loop Left Join (cost=15.56..691.36 rows=1 width=91) (actual time=9.552..111.193 rows=9,705 loops=1)

12. 7.143 79.312 ↓ 9,705.0 9,705 1

Nested Loop Left Join (cost=15.42..688.90 rows=1 width=52) (actual time=8.902..79.312 rows=9,705 loops=1)

13. 4.541 62.464 ↓ 9,705.0 9,705 1

Nested Loop (cost=15.13..680.58 rows=1 width=44) (actual time=8.896..62.464 rows=9,705 loops=1)

14. 1.451 5.143 ↓ 145.0 145 1

Nested Loop (cost=8.62..26.76 rows=1 width=24) (actual time=2.403..5.143 rows=145 loops=1)

  • Join Filter: (rs.route_rule_id = rr.id)
  • Rows Removed by Join Filter: 5945
15. 0.920 1.517 ↓ 20.7 145 1

Bitmap Heap Scan on rule_stops rs (cost=4.33..18.03 rows=7 width=24) (actual time=1.392..1.517 rows=145 loops=1)

  • Recheck Cond: (cid = 13695)
  • Heap Blocks: exact=9
16. 0.597 0.597 ↓ 20.7 145 1

Bitmap Index Scan on pk_rule_stops (cost=0.00..4.33 rows=7 width=0) (actual time=0.597..0.597 rows=145 loops=1)

  • Index Cond: (cid = 13695)
17. 1.125 2.175 ↓ 21.0 42 145

Materialize (cost=4.29..8.53 rows=2 width=8) (actual time=0.007..0.015 rows=42 loops=145)

18. 0.971 1.050 ↓ 21.0 42 1

Bitmap Heap Scan on route_rules rr (cost=4.29..8.52 rows=2 width=8) (actual time=0.932..1.050 rows=42 loops=1)

  • Recheck Cond: (cid = 13695)
  • Heap Blocks: exact=5
19. 0.079 0.079 ↓ 21.0 42 1

Bitmap Index Scan on pk_route_rules (cost=0.00..4.29 rows=2 width=0) (actual time=0.079..0.079 rows=42 loops=1)

  • Index Cond: (cid = 13695)
20. 42.050 52.780 ↓ 7.4 67 145

Bitmap Heap Scan on rule_stop_approvals rsa (cost=6.51..653.73 rows=9 width=20) (actual time=0.119..0.364 rows=67 loops=145)

  • Recheck Cond: (rule_stop_id = rs.id)
  • Filter: ((approval_company_user_id IS NULL) AND (route_rule_reference_type_id = ANY ('{1,2}'::integer[])) AND (cid = 13695))
  • Rows Removed by Filter: 459
  • Heap Blocks: exact=18202
21. 10.730 10.730 ↓ 1.9 526 145

Bitmap Index Scan on idx_rule_stop_approvals_rule_stop_id (cost=0.00..6.50 rows=278 width=0) (actual time=0.074..0.074 rows=526 loops=145)

  • Index Cond: (rule_stop_id = rs.id)
22. 9.705 9.705 ↓ 0.0 0 9,705

Index Scan using pk_company_users on company_users cu (cost=0.29..8.31 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=9,705)

  • Index Cond: ((cid = rs.cid) AND (cid = 13695) AND (id = rs.company_user_id))
  • Filter: (company_user_type_id = 2)
23. 19.410 19.410 ↑ 1.0 1 9,705

Index Scan using idx_company_groups_id on company_groups cg (cost=0.14..2.45 rows=1 width=51) (actual time=0.002..0.002 rows=1 loops=9,705)

  • Index Cond: (id = rs.company_group_id)
  • Filter: ((cid = 13695) AND (cid = rs.cid))
24. 9.705 9.705 ↓ 0.0 0 9,705

Index Scan using pk_company_employees on company_employees ce (cost=0.29..0.43 rows=1 width=94) (actual time=0.001..0.001 rows=0 loops=9,705)

  • Index Cond: ((cid = cu.cid) AND (cid = 13695) AND (id = cu.company_employee_id))
25. 26,533.470 26,533.470 ↓ 11,952.0 11,952 9,705

CTE Scan on rsatemp (cost=0.00..0.02 rows=1 width=24) (actual time=0.001..2.734 rows=11,952 loops=9,705)

  • Filter: (cid = 13695)
26. 0.000 78.104 ↓ 0.0 0 6,008

Nested Loop (cost=1.12..22.12 rows=2 width=24) (actual time=0.013..0.013 rows=0 loops=6,008)

27. 9.642 72.096 ↓ 0.0 0 6,008

Nested Loop (cost=0.70..5.62 rows=1 width=24) (actual time=0.011..0.012 rows=0 loops=6,008)

28. 60.080 60.080 ↓ 0.0 0 6,008

Index Scan using idx_rule_stop_approvals_reference_number on rule_stop_approvals rsa2 (cost=0.42..2.44 rows=1 width=20) (actual time=0.010..0.010 rows=0 loops=6,008)

  • Index Cond: (reference_number = rsa.reference_number)
  • Filter: ((approval_company_user_id IS NULL) AND (approval_datetime IS NULL) AND (approval_ip_address IS NULL) AND (cid = 13695) AND (route_rule_reference_type_id = 2) AND (cid = rsa.cid) AND (route_rule_reference_type_id = rsa.route_rule_reference_type_id))
  • Rows Removed by Filter: 2
29. 2.374 2.374 ↑ 1.0 1 1,187

Index Scan using pk_rule_stops on rule_stops rs2 (cost=0.28..3.17 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,187)

  • Index Cond: ((cid = 13695) AND (id = rsa2.rule_stop_id))
30. 8.309 8.309 ↓ 0.0 0 1,187

Index Only Scan using idx_ap_details_cid_apheaderid on ap_details ad1 (cost=0.42..16.47 rows=3 width=8) (actual time=0.007..0.007 rows=0 loops=1,187)

  • Index Cond: ((cid = 13695) AND (ap_header_id = rsa2.reference_number))
  • Heap Fetches: 1
31. 408.544 408.544 ↓ 0.0 0 6,008

Index Scan using idx_ap_details on ap_details ad (cost=0.42..5.62 rows=1 width=12) (actual time=0.067..0.068 rows=0 loops=6,008)

  • Index Cond: (id = rsa.reference_number)
  • Filter: ((cid = 13695) AND (cid = rsa.cid))
  • Rows Removed by Filter: 0
32. 16.969 18.024 ↓ 0.0 0 6,008

Index Scan using pk_rule_stops on rule_stops rs_1 (cost=8.58..8.62 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=6,008)

  • Index Cond: ((cid = 13695) AND (id = rsa.rule_stop_id))
  • Filter: ((company_user_id = 3806) OR (hashed SubPlan 2))
  • Rows Removed by Filter: 1
33.          

SubPlan (forIndex Scan)

34. 1.055 1.055 ↓ 0.0 0 1

Index Only Scan using uk_company_user_groups_cid_company_user_id_company_group_id on company_user_groups cug (cost=0.28..8.30 rows=1 width=4) (actual time=1.055..1.055 rows=0 loops=1)

  • Index Cond: ((cid = 13695) AND (company_user_id = 3806))
  • Heap Fetches: 0