explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TXiP

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 0.128 ↓ 0.0 0 1

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

2.          

CTE batch

3. 0.002 0.093 ↓ 0.0 0 1

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

4. 0.003 0.066 ↑ 1.0 1 1

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

5. 0.028 0.028 ↑ 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.027..0.028 rows=1 loops=1)

  • Index Cond: (id = $26)
6. 0.035 0.035 ↑ 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.034..0.035 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.025 0.025 ↓ 0.0 0 1

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

  • Index Cond: (id = at.account_id)
  • Filter: (account_type_id = 5)
  • Rows Removed by Filter: 1
8. 0.001 0.127 ↓ 0.0 0 1

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

9. 0.001 0.126 ↓ 0.0 0 1

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

10. 0.027 0.125 ↓ 0.0 0 1

Sort (cost=43,263.68..43,263.68 rows=2 width=154) (actual time=0.125..0.125 rows=0 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.001 0.098 ↓ 0.0 0 1

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

12. 0.001 0.097 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,401.69..43,253.61 rows=2 width=145) (actual time=0.097..0.097 rows=0 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.000 0.096 ↓ 0.0 0 1

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

  • Join Filter: (pwp.id IS NOT NULL)
14. 0.000 0.096 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.88..19.10 rows=1 width=120) (actual time=0.096..0.096 rows=0 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.000 0.096 ↓ 0.0 0 1

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

  • Join Filter: (pwp.id IS NOT NULL)
16. 0.000 0.096 ↓ 0.0 0 1

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

  • Join Filter: (batch.account_operation_type_id = ANY ('{3,5,6,9,10,28,29,38,39}'::integer[]))
17. 0.002 0.096 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.58..5.64 rows=1 width=92) (actual time=0.096..0.096 rows=0 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.094 0.094 ↓ 0.0 0 1

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

19. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_payment_way_pieces on payment_way_pieces pwp (cost=0.58..5.59 rows=1 width=42) (never executed)

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

Index Scan using pk_route_cards on route_cards rc (cost=0.44..5.46 rows=1 width=26) (never executed)

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

Index Only Scan using idx_devices_id_serial_num_is_deleted on devices d (cost=0.43..2.82 rows=1 width=14) (never executed)

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

Index Scan using relationship_127_fk on vehicle_devices vd (cost=0.43..5.14 rows=1 width=24) (never executed)

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

Index Scan using idx_road_names_road_id on road_names r (cost=0.42..15.33 rows=1 width=44) (never executed)

  • 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.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on vehicles v (cost=1,399.39..1,404.40 rows=2,787,651 width=17) (never executed)

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

BitmapOr (cost=1,399.39..1,399.39 rows=2 width=0) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on vehicles_id_is_deleted_account_id_idx (cost=0.00..2.78 rows=1 width=0) (never executed)

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

Bitmap Index Scan on vehicles_id_is_deleted_account_id_idx (cost=0.00..2.78 rows=1 width=0) (never executed)

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

Index Scan using pk_vehicle_groups on vehicle_groups vg (cost=0.29..4.99 rows=1 width=17) (never executed)

  • Index Cond: (v.vehicle_group_id = id)