explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qjv7

Settings
# exclusive inclusive rows x rows loops node
1. 607.861 2,053.710 ↓ 648.6 395,636 1

Sort (cost=32,612.78..32,614.30 rows=610 width=204) (actual time=1,990.812..2,053.710 rows=395,636 loops=1)

  • Sort Key: rsr.property_id, rs.order_num, rsr.order_num
  • Sort Method: quicksort Memory: 115470kB
2. 297.466 1,445.849 ↓ 648.6 395,636 1

WindowAgg (cost=32,554.06..32,584.56 rows=610 width=204) (actual time=1,085.380..1,445.849 rows=395,636 loops=1)

3. 455.985 1,148.383 ↓ 648.6 395,636 1

Sort (cost=32,554.06..32,555.58 rows=610 width=182) (actual time=1,085.358..1,148.383 rows=395,636 loops=1)

  • Sort Key: rsr.reference_id, rsr.property_id, rs.order_num, rsr.id DESC
  • Sort Method: quicksort Memory: 114836kB
4. 0.000 692.398 ↓ 648.6 395,636 1

Nested Loop Left Join (cost=1,265.72..32,525.84 rows=610 width=182) (actual time=1.823..692.398 rows=395,636 loops=1)

5. 131.228 320.067 ↓ 648.6 395,636 1

Gather (cost=1,265.43..32,222.50 rows=610 width=172) (actual time=1.819..320.067 rows=395,636 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 49.817 188.839 ↓ 519.2 131,879 3 / 3

Hash Join (cost=265.43..31,161.50 rows=254 width=172) (actual time=1.444..188.839 rows=131,879 loops=3)

  • Hash Cond: (rsr.rule_stop_id = rs.id)
7. 44.834 137.944 ↓ 61.1 131,879 3 / 3

Hash Join (cost=65.13..30,945.46 rows=2,159 width=128) (actual time=0.357..137.944 rows=131,879 loops=3)

  • Hash Cond: (rsr.property_id = p.id)
8. 92.894 92.894 ↓ 1.0 131,879 3 / 3

Parallel Index Scan using idx_rule_stop_results_route_type_id on rule_stop_results rsr (cost=0.42..30,534.38 rows=131,524 width=109) (actual time=0.062..92.894 rows=131,879 loops=3)

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

Hash (cost=62.76..62.76 rows=60 width=27) (actual time=0.216..0.216 rows=57 loops=3)

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

Index Scan using idx_properties_cid_id_is_disabled_remote_primary_key on properties p (cost=0.28..62.76 rows=60 width=27) (actual time=0.041..0.203 rows=57 loops=3)

  • Index Cond: ((cid = 2547) AND (is_disabled = 0))
11. 0.066 1.078 ↓ 4.5 279 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
12. 0.184 1.012 ↓ 4.5 279 3 / 3

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

13. 0.070 0.549 ↓ 4.5 279 3 / 3

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

  • Hash Cond: ((rs.cid = cg.cid) AND (rs.company_group_id = cg.id))
14. 0.152 0.326 ↓ 4.5 279 3 / 3

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

  • Hash Cond: (rs.route_id = r.id)
15. 0.067 0.174 ↑ 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.054..0.174 rows=279 loops=3)

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

Index Cond: (cid = 2547)-> Hash (cost=3.73..3.73 rows=21 width=9) (actual time=0.056..0.056 rows=21 loops=3)

17. 0.051 0.051 ↑ 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.042..0.051 rows=21 loops=3)

  • Index Cond: (cid = 2547)
18. 0.009 0.153 ↑ 1.0 26 3 / 3

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
19. 0.144 0.144 ↑ 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.039..0.144 rows=26 loops=3)

  • Index Cond: (cid = 2547)
20. 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))
21. 395.636 395.636 ↓ 0.0 0 395,636

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,636)

  • Index Cond: (id = cu.company_employee_id)
  • Filter: ((cid = 2547) AND (cid = cu.cid))
Planning time : 5.167 ms
Execution time : 2,192.877 ms