explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pj3S

Settings
# exclusive inclusive rows x rows loops node
1. 0.465 677.323 ↓ 1.9 448 1

Hash Join (cost=1,919.31..2,035.12 rows=234 width=4) (actual time=674.858..677.323 rows=448 loops=1)

  • Hash Cond: (tr1.ticket_id = t1.id)
2. 2.164 145.466 ↓ 3.4 1,246 1

Hash Join (cost=312.38..418.89 rows=371 width=4) (actual time=142.938..145.466 rows=1,246 loops=1)

  • Hash Cond: (tr1.id = btr1.id)
3. 101.023 101.023 ↓ 2.8 7,217 1

Foreign Scan on ticket_report tr1 (cost=100.00..186.80 rows=2,560 width=8) (actual time=99.534..101.023 rows=7,217 loops=1)

4. 0.379 42.279 ↓ 43.0 1,246 1

Hash (cost=212.01..212.01 rows=29 width=4) (actual time=42.279..42.279 rows=1,246 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 60kB
5. 41.900 41.900 ↓ 43.0 1,246 1

Foreign Scan on breakdown_ticket_report btr1 (cost=100.00..212.01 rows=29 width=4) (actual time=41.468..41.900 rows=1,246 loops=1)

  • Filter: ((elevator_state_on_technician_arrival = 'OUT_OF_ORDER'::elevatorstateontechnicianarrival) OR (elevator_state_on_technician_departure = 'STOPPED'::elevatorstateontechniciandeparture))
  • Rows Removed by Filter: 1709
6. 0.519 531.392 ↓ 13.4 1,691 1

Hash (cost=1,605.36..1,605.36 rows=126 width=4) (actual time=531.392..531.392 rows=1,691 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 76kB
7. 7.125 530.873 ↓ 13.4 1,691 1

Hash Semi Join (cost=1,516.63..1,605.36 rows=126 width=4) (actual time=509.156..530.873 rows=1,691 loops=1)

  • Hash Cond: (elevator1.id = elevator2.id)
  • Join Filter: ((t2.created_at <= t1.created_at) AND (t1.id <> t2.id) AND (t2.created_at >= (t1.created_at - '21 days'::interval)))
  • Rows Removed by Join Filter: 28931
8. 9.804 277.476 ↓ 2.3 10,268 1

Merge Join (cost=371.77..444.49 rows=4,545 width=16) (actual time=262.785..277.476 rows=10,268 loops=1)

  • Merge Cond: ((t1.elevator_id)::text = (elevator1.sfid)::text)
9. 46.243 216.463 ↓ 11.3 10,269 1

Sort (cost=182.03..184.30 rows=910 width=66) (actual time=213.801..216.463 rows=10,269 loops=1)

  • Sort Key: t1.elevator_id
  • Sort Method: quicksort Memory: 1280kB
10. 170.220 170.220 ↓ 13.5 12,264 1

Foreign Scan on ticket t1 (cost=100.00..137.30 rows=910 width=66) (actual time=166.942..170.220 rows=12,264 loops=1)

11. 10.913 51.209 ↓ 11.1 11,072 1

Sort (cost=189.74..192.24 rows=999 width=58) (actual time=48.959..51.209 rows=11,072 loops=1)

  • Sort Key: elevator1.sfid
  • Sort Method: quicksort Memory: 158kB
12. 40.296 40.296 ↓ 1.4 1,398 1

Foreign Scan on elevator__c elevator1 (cost=100.00..139.97 rows=999 width=58) (actual time=39.939..40.296 rows=1,398 loops=1)

13. 0.428 246.272 ↑ 6.8 1,246 1

Hash (cost=1,039.41..1,039.41 rows=8,436 width=16) (actual time=246.272..246.272 rows=1,246 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 192kB
14. 2.040 245.844 ↑ 6.8 1,246 1

Merge Join (cost=907.86..1,039.41 rows=8,436 width=16) (actual time=243.229..245.844 rows=1,246 loops=1)

  • Merge Cond: ((elevator2.sfid)::text = (t2.elevator_id)::text)
15. 9.179 49.682 ↓ 1.4 1,385 1

Sort (cost=189.74..192.24 rows=999 width=58) (actual time=49.341..49.682 rows=1,385 loops=1)

  • Sort Key: elevator2.sfid
  • Sort Method: quicksort Memory: 158kB
16. 40.503 40.503 ↓ 1.4 1,398 1

Foreign Scan on elevator__c elevator2 (cost=100.00..139.97 rows=999 width=58) (actual time=40.110..40.503 rows=1,398 loops=1)

17. 4.886 194.122 ↑ 1.4 1,246 1

Sort (cost=718.12..722.34 rows=1,689 width=66) (actual time=193.863..194.122 rows=1,246 loops=1)

  • Sort Key: t2.elevator_id
  • Sort Method: quicksort Memory: 146kB
18. 0.930 189.236 ↑ 1.4 1,246 1

Hash Join (cost=461.05..627.57 rows=1,689 width=66) (actual time=185.853..189.236 rows=1,246 loops=1)

  • Hash Cond: (tr2.ticket_id = t2.id)
19. 2.190 90.037 ↓ 3.4 1,246 1

Hash Join (cost=312.38..418.89 rows=371 width=4) (actual time=87.562..90.037 rows=1,246 loops=1)

  • Hash Cond: (tr2.id = btr2.id)
20. 45.483 45.483 ↓ 2.8 7,217 1

Foreign Scan on ticket_report tr2 (cost=100.00..186.80 rows=2,560 width=8) (actual time=44.058..45.483 rows=7,217 loops=1)

21. 0.359 42.364 ↓ 43.0 1,246 1

Hash (cost=212.01..212.01 rows=29 width=4) (actual time=42.364..42.364 rows=1,246 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 60kB
22. 42.005 42.005 ↓ 43.0 1,246 1

Foreign Scan on breakdown_ticket_report btr2 (cost=100.00..212.01 rows=29 width=4) (actual time=41.528..42.005 rows=1,246 loops=1)

  • Filter: ((elevator_state_on_technician_arrival = 'OUT_OF_ORDER'::elevatorstateontechnicianarrival) OR (elevator_state_on_technician_departure = 'STOPPED'::elevatorstateontechniciandeparture))
  • Rows Removed by Filter: 1709
23. 3.680 98.269 ↓ 13.5 12,264 1

Hash (cost=137.30..137.30 rows=910 width=66) (actual time=98.268..98.269 rows=12,264 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 864kB
24. 94.589 94.589 ↓ 13.5 12,264 1

Foreign Scan on ticket t2 (cost=100.00..137.30 rows=910 width=66) (actual time=91.343..94.589 rows=12,264 loops=1)