explain.depesz.com

PostgreSQL's explain analyze made readable

Result: C7Y

Settings
# exclusive inclusive rows x rows loops node
1. 38.659 41.087 ↑ 2.0 1 1

Insert on account_operations_report (cost=43,280.36..43,280.46 rows=2 width=160) (actual time=41.080..41.087 rows=1 loops=1)

2.          

CTE batch

3. 0.006 0.291 ↑ 1.0 1 1

Nested Loop (cost=1.60..16.68 rows=1 width=66) (actual time=0.210..0.291 rows=1 loops=1)

4. 0.005 0.053 ↑ 1.0 1 1

Nested Loop (cost=1.16..11.21 rows=1 width=66) (actual time=0.048..0.053 rows=1 loops=1)

5. 0.027 0.027 ↑ 1.0 1 1

Index Scan using pk_account_transactions on account_transactions at (cost=0.58..5.60 rows=1 width=34) (actual time=0.025..0.027 rows=1 loops=1)

  • Index Cond: (id = $26)
6. 0.021 0.021 ↑ 1.0 1 1

Index Scan using pk_account_operations on account_operations ao (cost=0.58..5.61 rows=1 width=42) (actual time=0.019..0.021 rows=1 loops=1)

  • Index Cond: (id = at.account_operation_id)
  • Filter: ((account_operation_type_id <> 5) OR (at.account_transaction_type_id <> 1))
7. 0.232 0.232 ↑ 1.0 1 1

Index Scan using pk_accounts on accounts a (cost=0.43..5.46 rows=1 width=4) (actual time=0.158..0.232 rows=1 loops=1)

  • Index Cond: (id = at.account_id)
  • Filter: (account_type_id = 5)
8. 0.011 2.428 ↑ 2.0 1 1

Subquery Scan on *SELECT* (cost=43,263.68..43,263.78 rows=2 width=160) (actual time=2.424..2.428 rows=1 loops=1)

9. 0.003 2.417 ↑ 2.0 1 1

Unique (cost=43,263.68..43,263.76 rows=2 width=154) (actual time=2.414..2.417 rows=1 loops=1)

10. 2.072 2.414 ↑ 2.0 1 1

Sort (cost=43,263.68..43,263.68 rows=2 width=154) (actual time=2.413..2.414 rows=1 loops=1)

  • Sort Key: batch.operation_dt, batch.account_id, batch.account_transaction_id, batch.account_operation_id, batch.account_operation_type_id, (CASE batch.turnover_type WHEN 'CREDIT'::type_enum_at_turnover_type THEN batch.amount ELSE ((-1) * batch.amount) END), (CASE (rc.id IS NULL) WHEN CASE_TEST_EXPR THEN (d.serial_num)::bigint ELSE rc.num END), pwp.start_move_dt, pwp.end_move_dt, v.grnz, v.id, r.road_name, (CASE WHEN (batch.account_operation_type_id = ANY ('{4,7,8,33,34,35,36,37,40,41,42,43,44,45}'::integer[])) THEN pwp.distance ELSE rc.length_fed END), vg.name, rc.id
  • Sort Method: quicksort Memory: 25kB
11. 0.010 0.342 ↑ 2.0 1 1

Nested Loop Left Join (cost=1,401.98..43,263.67 rows=2 width=154) (actual time=0.256..0.342 rows=1 loops=1)

12. 0.008 0.332 ↑ 2.0 1 1

Nested Loop Left Join (cost=1,401.69..43,253.61 rows=2 width=145) (actual time=0.247..0.332 rows=1 loops=1)

  • Join Filter: (((pwp.id IS NOT NULL) AND (v.id = vd.vehicle_id)) OR ((rc.id IS NOT NULL) AND (v.id = rc.vehicle_id)))
13. 0.003 0.320 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.30..34.44 rows=1 width=144) (actual time=0.235..0.320 rows=1 loops=1)

  • Join Filter: (pwp.id IS NOT NULL)
14. 0.003 0.316 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.88..19.10 rows=1 width=120) (actual time=0.232..0.316 rows=1 loops=1)

  • Join Filter: ((vd.attach_date < pwp.end_move_dt) AND ((vd.detach_date IS NULL) OR (vd.detach_date >= pwp.end_move_dt)))
15. 0.007 0.312 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.45..13.95 rows=1 width=120) (actual time=0.228..0.312 rows=1 loops=1)

  • Join Filter: (pwp.id IS NOT NULL)
16. 0.003 0.304 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.02..11.12 rows=1 width=110) (actual time=0.222..0.304 rows=1 loops=1)

  • Join Filter: (batch.account_operation_type_id = ANY ('{3,5,6,9,10,28,29,38,39}'::integer[]))
17. 0.004 0.300 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.58..5.64 rows=1 width=92) (actual time=0.218..0.300 rows=1 loops=1)

  • Join Filter: (batch.account_operation_type_id = ANY ('{4,7,8,33,34,35,36,37,40,41,42,43,44,45}'::integer[]))
18. 0.295 0.295 ↑ 1.0 1 1

CTE Scan on batch (cost=0.00..0.02 rows=1 width=50) (actual time=0.214..0.295 rows=1 loops=1)

19. 0.001 0.001 ↓ 0.0 0 1

Index Scan using pk_payment_way_pieces on payment_way_pieces pwp (cost=0.58..5.59 rows=1 width=42) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (batch.charge_doc_id = id)
20. 0.001 0.001 ↓ 0.0 0 1

Index Scan using pk_route_cards on route_cards rc (cost=0.44..5.46 rows=1 width=26) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (batch.charge_doc_id = id)
21. 0.001 0.001 ↓ 0.0 0 1

Index Only Scan using idx_devices_id_serial_num_is_deleted on devices d (cost=0.43..2.82 rows=1 width=14) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (id = pwp.device_id)
  • Heap Fetches: 0
22. 0.001 0.001 ↓ 0.0 0 1

Index Scan using relationship_127_fk on vehicle_devices vd (cost=0.43..5.14 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (d.id = device_id)
  • Filter: (NOT is_deleted)
23. 0.001 0.001 ↓ 0.0 0 1

Index Scan using idx_road_names_road_id on road_names r (cost=0.42..15.33 rows=1 width=44) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (road_id = pwp.road_id)
  • Filter: (gis_version_id = (SubPlan 2))
24.          

SubPlan (for Index Scan)

25. 0.000 0.000 ↓ 0.0 0

Limit (cost=1.08..1.09 rows=1 width=12) (never executed)

26. 0.000 0.000 ↓ 0.0 0

Sort (cost=1.08..1.09 rows=2 width=12) (never executed)

  • Sort Key: gis_versions.dt_start
27. 0.000 0.000 ↓ 0.0 0

Seq Scan on gis_versions (cost=0.00..1.07 rows=2 width=12) (never executed)

  • Filter: (dt_start <= pwp.end_move_dt)
28. 0.002 0.004 ↓ 0.0 0 1

Bitmap Heap Scan on vehicles v (cost=1,399.39..1,404.40 rows=2,787,651 width=17) (actual time=0.004..0.004 rows=0 loops=1)

  • Recheck Cond: ((id = vd.vehicle_id) OR (id = rc.vehicle_id))
29. 0.002 0.002 ↓ 0.0 0 1

BitmapOr (cost=1,399.39..1,399.39 rows=2 width=0) (actual time=0.002..0.002 rows=0 loops=1)

30. 0.000 0.000 ↓ 0.0 0 1

Bitmap Index Scan on vehicles_id_is_deleted_account_id_idx (cost=0.00..2.78 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (id = vd.vehicle_id)
31. 0.000 0.000 ↓ 0.0 0 1

Bitmap Index Scan on vehicles_id_is_deleted_account_id_idx (cost=0.00..2.78 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (id = rc.vehicle_id)
32. 0.000 0.000 ↓ 0.0 0 1

Index Scan using pk_vehicle_groups on vehicle_groups vg (cost=0.29..4.99 rows=1 width=17) (actual time=0.000..0.000 rows=0 loops=1)

  • Index Cond: (v.vehicle_group_id = id)