explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tPoQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.210 2,101.649 ↓ 0.0 0 1

Subquery Scan on temp_rsr (cost=32,624.86..32,624.94 rows=1 width=208) (actual time=2,101.649..2,101.649 rows=0 loops=1)

  • Filter: ((temp_rsr.row_num_2 = 1) AND ((hashed SubPlan 1) OR (temp_rsr.rule_stop_company_user_id = 202742)))
  • Rows Removed by Filter: 2086
2. 1.363 2,101.404 ↓ 2,086.0 2,086 1

WindowAgg (cost=32,623.53..32,623.58 rows=1 width=208) (actual time=2,099.926..2,101.404 rows=2,086 loops=1)

3. 1.608 2,100.041 ↓ 2,086.0 2,086 1

Sort (cost=32,623.53..32,623.54 rows=1 width=200) (actual time=2,099.918..2,100.041 rows=2,086 loops=1)

  • Sort Key: trs.reference_id, trs.property_id, trs.order_num
  • Sort Method: quicksort Memory: 651kB
4. 108.025 2,098.433 ↓ 2,086.0 2,086 1

Subquery Scan on trs (cost=32,600.65..32,623.52 rows=1 width=200) (actual time=1,937.774..2,098.433 rows=2,086 loops=1)

  • Filter: ((trs.row_num_1 = 1) AND (trs.rule_stop_status_type_id = 1))
  • Rows Removed by Filter: 393546
5. 592.213 1,990.408 ↓ 648.6 395,632 1

Sort (cost=32,600.65..32,602.17 rows=610 width=204) (actual time=1,937.737..1,990.408 rows=395,632 loops=1)

  • Sort Key: rsr.property_id, rs.order_num, rsr.order_num
  • Sort Method: quicksort Memory: 115469kB
6. 271.931 1,398.195 ↓ 648.6 395,632 1

WindowAgg (cost=32,541.93..32,572.43 rows=610 width=204) (actual time=1,074.137..1,398.195 rows=395,632 loops=1)

7. 448.190 1,126.264 ↓ 648.6 395,632 1

Sort (cost=32,541.93..32,543.45 rows=610 width=182) (actual time=1,074.126..1,126.264 rows=395,632 loops=1)

  • Sort Key: rsr.reference_id, rsr.property_id, rs.order_num, rsr.id DESC
  • Sort Method: quicksort Memory: 114835kB
8. 0.000 678.074 ↓ 648.6 395,632 1

Nested Loop Left Join (cost=1,265.71..32,513.71 rows=610 width=182) (actual time=1.627..678.074 rows=395,632 loops=1)

9. 119.742 312.146 ↓ 648.6 395,632 1

Gather (cost=1,265.42..32,210.30 rows=610 width=172) (actual time=1.610..312.146 rows=395,632 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
10. 46.977 192.404 ↓ 519.2 131,877 3 / 3

Hash Join (cost=265.42..31,149.30 rows=254 width=172) (actual time=1.393..192.404 rows=131,877 loops=3)

  • Hash Cond: (rsr.rule_stop_id = rs.id)
11. 42.263 144.385 ↓ 61.1 131,877 3 / 3

Hash Join (cost=65.12..30,933.26 rows=2,158 width=128) (actual time=0.344..144.385 rows=131,877 loops=3)

  • Hash Cond: (rsr.property_id = p.id)
12. 101.914 101.914 ↓ 1.0 131,877 3 / 3

Parallel Index Scan using idx_rule_stop_results_route_type_id on rule_stop_results rsr (cost=0.42..30,522.41 rows=131,445 width=109) (actual time=0.039..101.914 rows=131,877 loops=3)

  • Index Cond: (route_type_id = 5)
  • Filter: ((NOT is_archived) AND (cid = 2547) AND (property_id = ANY ('{122325,505884,653352,553398,512056,101384,204268,518924,508248,93080,634856,518921,605906,630924,518925,634857,518930,518926,518927,518928,518929,627083,518922,518931,518932,518934,518935,518933,693051,518936,518937,518938,693050,197791,584764,600245,253688,122326,609927,216936,171954,216937,166145,148173,228008,518941,518940,518939,651590,253689,94968,222212,544133,228009,28971}'::integer[])))
  • Rows Removed by Filter: 1923
13. 0.012 0.208 ↑ 1.1 57 3 / 3

Hash (cost=62.74..62.74 rows=60 width=27) (actual time=0.208..0.208 rows=57 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
14. 0.196 0.196 ↑ 1.1 57 3 / 3

Index Scan using idx_properties_cid_id_is_disabled_remote_primary_key on properties p (cost=0.28..62.74 rows=60 width=27) (actual time=0.038..0.196 rows=57 loops=3)

  • Index Cond: ((cid = 2547) AND (is_disabled = 0))
15. 0.063 1.042 ↓ 4.5 279 3 / 3

Hash (cost=198.28..198.28 rows=62 width=52) (actual time=1.042..1.042 rows=279 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
16. 0.176 0.979 ↓ 4.5 279 3 / 3

Nested Loop Left Join (cost=28.78..198.28 rows=62 width=52) (actual time=0.283..0.979 rows=279 loops=3)

17. 0.065 0.524 ↓ 4.5 279 3 / 3

Hash Left Join (cost=28.36..50.19 rows=62 width=44) (actual time=0.277..0.524 rows=279 loops=3)

  • Hash Cond: ((rs.cid = cg.cid) AND (rs.company_group_id = cg.id))
18. 0.082 0.280 ↓ 4.5 279 3 / 3

Hash Join (cost=4.69..26.19 rows=62 width=29) (actual time=0.093..0.280 rows=279 loops=3)

  • Hash Cond: (rs.route_id = r.id)
19. 0.150 0.150 ↑ 1.0 279 3 / 3

Index Scan using pk_rule_stops on rule_stops rs (cost=0.28..20.96 rows=279 width=28) (actual time=0.041..0.150 rows=279 loops=3)

  • Index Cond: (cid = 2547)
20. 0.005 0.048 ↑ 1.0 21 3 / 3

Hash (cost=3.73..3.73 rows=21 width=9) (actual time=0.048..0.048 rows=21 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.043 0.043 ↑ 1.0 21 3 / 3

Index Scan using pk_routes on routes r (cost=0.14..3.73 rows=21 width=9) (actual time=0.035..0.043 rows=21 loops=3)

  • Index Cond: (cid = 2547)
22. 0.008 0.179 ↑ 1.0 26 3 / 3

Hash (cost=22.76..22.76 rows=26 width=23) (actual time=0.179..0.179 rows=26 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 0.171 0.171 ↑ 1.0 26 3 / 3

Index Scan using pk_company_groups on company_groups cg (cost=0.28..22.76 rows=26 width=23) (actual time=0.043..0.171 rows=26 loops=3)

  • Index Cond: (cid = 2547)
24. 0.279 0.279 ↓ 0.0 0 837 / 3

Index Scan using pk_company_users on company_users cu (cost=0.42..2.39 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=837)

  • Index Cond: ((cid = rs.cid) AND (cid = 2547) AND (id = rs.company_user_id))
25. 395.632 395.632 ↓ 0.0 0 395,632

Index Scan using idx_company_employees_id on company_employees ce (cost=0.29..0.47 rows=1 width=26) (actual time=0.001..0.001 rows=0 loops=395,632)

  • Index Cond: (id = cu.company_employee_id)
  • Filter: ((cid = 2547) AND (cid = cu.cid))
26.          

SubPlan (for Subquery Scan)

27. 0.035 0.035 ↑ 1.0 1 1

Index Only Scan using uk_company_user_groups_cid_company_user_id_company_group_id on company_user_groups cug (cost=0.29..1.32 rows=1 width=4) (actual time=0.035..0.035 rows=1 loops=1)

  • Index Cond: ((cid = 2547) AND (company_user_id = 202742))
  • Heap Fetches: 0
Planning time : 5.236 ms
Execution time : 2,116.536 ms