explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8D5n

Settings
# exclusive inclusive rows x rows loops node
1. 28.717 667,893.025 ↑ 1.0 1 1

Aggregate (cost=2,100,300.93..2,100,300.94 rows=1 width=8) (actual time=667,893.025..667,893.025 rows=1 loops=1)

2. 1,355.205 667,864.308 ↑ 2.3 41,254 1

Hash Join (cost=349,066.47..2,100,067.91 rows=93,205 width=0) (actual time=6,232.555..667,864.308 rows=41,254 loops=1)

  • Hash Cond: (plaza_transactions.device_id = devices.id)
3. 298.132 660,568.192 ↓ 6.2 869,814 1

Hash Left Join (cost=10,924.52..1,754,394.16 rows=139,735 width=4) (actual time=257.204..660,568.192 rows=869,814 loops=1)

  • Hash Cond: (shifts.id = shift_operators.shift_id)
4. 401.032 660,232.504 ↓ 6.2 869,814 1

Hash Join (cost=8,787.01..1,751,308.16 rows=139,735 width=8) (actual time=219.069..660,232.504 rows=869,814 loops=1)

  • Hash Cond: (plaza_transactions.service_provider_id = service_providers.id)
5. 931.634 659,831.445 ↓ 6.2 869,814 1

Hash Left Join (cost=8,785.85..1,750,718.12 rows=139,735 width=10) (actual time=218.930..659,831.445 rows=869,814 loops=1)

  • Hash Cond: ((SubPlan 1) = shifts.id)
6. 783.605 654,275.745 ↓ 6.2 869,814 1

Nested Loop (cost=1.56..1,703,574.11 rows=139,735 width=44) (actual time=5.192..654,275.745 rows=869,814 loops=1)

7. 226.413 291,531.788 ↓ 4.4 870,097 1

Nested Loop (cost=0.99..903,861.87 rows=196,469 width=52) (actual time=3.973..291,531.788 rows=870,097 loops=1)

8. 0.287 3.293 ↓ 1.4 54 1

Nested Loop (cost=0.42..1,504.39 rows=39 width=4) (actual time=1.709..3.293 rows=54 loops=1)

  • Join Filter: (lanes.plaza_id = plazas.id)
  • Rows Removed by Join Filter: 4,401
9. 1.872 1.872 ↓ 1.0 1,134 1

Index Scan using lanes_pkey on lanes (cost=0.28..1,429.13 rows=1,129 width=8) (actual time=0.016..1.872 rows=1,134 loops=1)

10. 0.779 1.134 ↑ 1.0 4 1,134

Materialize (cost=0.14..7.89 rows=4 width=4) (actual time=0.000..0.001 rows=4 loops=1,134)

11. 0.355 0.355 ↑ 1.0 4 1

Index Scan using fk_reference_plazas_road_id_fk on plazas (cost=0.14..7.87 rows=4 width=4) (actual time=0.351..0.355 rows=4 loops=1)

  • Index Cond: (road_id = 2)
12. 291,302.082 291,302.082 ↓ 1.6 16,113 54

Index Scan using idx_plaza_transactions_lane_id_dt on plaza_transactions (cost=0.57..23,034.15 rows=10,322 width=60) (actual time=0.889..5,394.483 rows=16,113 loops=54)

  • Index Cond: ((lane_id = lanes.id) AND (dt >= '2019-10-31 21:00:00+00'::timestamp with time zone) AND (dt <= '2020-02-13 20:59:59+00'::timestamp with time zone))
  • Filter: ((deleted_at IS NULL) AND (id IS NOT NULL) AND (plaza_transaction_status_id <> ALL ('{10,11,12}'::integer[])) AND (vehicle_class_id = ANY ('{1,2,3}'::integer[])) AND (payment_type_id = ANY ('{2,5,6,7,17}'::integer[])) AND (plaza_transaction_status_id = ANY ('{1,2,4,3,5,7}'::integer[])))
  • Rows Removed by Filter: 5,035
13. 361,960.352 361,960.352 ↑ 1.0 1 870,097

Index Scan using plaza_raw_transactions_pkey on plaza_raw_transactions (cost=0.57..4.07 rows=1 width=8) (actual time=0.416..0.416 rows=1 loops=870,097)

  • Index Cond: (id = plaza_transactions.plaza_raw_transaction_id)
  • Filter: (vehicle_class = ANY ('{1,2,3}'::bigint[]))
  • Rows Removed by Filter: 0
14. 57.601 209.450 ↓ 1.1 218,577 1

Hash (cost=6,210.24..6,210.24 rows=205,924 width=4) (actual time=209.449..209.450 rows=218,577 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,733kB
15. 151.849 151.849 ↓ 1.1 218,577 1

Seq Scan on shifts (cost=0.00..6,210.24 rows=205,924 width=4) (actual time=0.522..151.849 rows=218,577 loops=1)

16.          

SubPlan (for Hash Left Join)

17. 1,103.654 4,414.616 ↓ 0.0 0 1,103,654

Limit (cost=0.42..5.44 rows=1 width=4) (actual time=0.003..0.004 rows=0 loops=1,103,654)

18. 3,310.962 3,310.962 ↓ 0.0 0 1,103,654

Index Scan using idx_shifts_uuid on shifts shifts_1 (cost=0.42..5.44 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1,103,654)

  • Index Cond: (uuid = plaza_transactions.shift_uuid)
19. 0.005 0.027 ↓ 1.1 8 1

Hash (cost=1.07..1.07 rows=7 width=2) (actual time=0.027..0.027 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.022 0.022 ↓ 1.1 8 1

Seq Scan on service_providers (cost=0.00..1.07 rows=7 width=2) (actual time=0.020..0.022 rows=8 loops=1)

21. 11.654 37.556 ↑ 1.0 60,941 1

Hash (cost=1,355.56..1,355.56 rows=62,556 width=8) (actual time=37.556..37.556 rows=60,941 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,893kB
22. 25.902 25.902 ↑ 1.0 60,941 1

Seq Scan on shift_operators (cost=0.00..1,355.56 rows=62,556 width=8) (actual time=0.016..25.902 rows=60,941 loops=1)

23. 304.318 5,940.911 ↑ 1.5 1,069,516 1

Hash (cost=312,635.82..312,635.82 rows=1,554,650 width=4) (actual time=5,940.911..5,940.911 rows=1,069,516 loops=1)

  • Buckets: 1,048,576 Batches: 2 Memory Usage: 26,993kB
24. 5,636.593 5,636.593 ↑ 1.5 1,069,516 1

Seq Scan on devices (cost=0.00..312,635.82 rows=1,554,650 width=4) (actual time=0.885..5,636.593 rows=1,069,516 loops=1)

  • Filter: ((ets_provider_id = ANY ('{2,3,4}'::integer[])) OR ((pan)::text ~~ '605842%'::text) OR ((pan)::text ~~ '637219%'::text) OR ((pan)::text ~~ '636287%'::text))
  • Rows Removed by Filter: 1,305,862