explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wZiq

Settings
# exclusive inclusive rows x rows loops node
1. 40.042 43.361 ↑ 2.0 1 1

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

2.          

CTE batch

3. 0.001 0.049 ↑ 1.0 1 1

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

4. 0.000 0.031 ↑ 1.0 1 1

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

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

  • Index Cond: (id = $26)
6. 0.019 0.019 ↑ 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.018..0.019 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.017 0.017 ↑ 1.0 1 1

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

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

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

9. 0.003 3.317 ↑ 2.0 1 1

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

10. 0.012 3.314 ↑ 2.0 1 1

Sort (cost=43,263.68..43,263.68 rows=2 width=154) (actual time=3.314..3.314 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 3.302 ↑ 2.0 1 1

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

12. 0.007 3.291 ↑ 2.0 1 1

Nested Loop Left Join (cost=1,401.69..43,253.61 rows=2 width=145) (actual time=3.271..3.291 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.004 2.233 ↑ 1.0 1 1

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

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

Nested Loop Left Join (cost=1.88..19.10 rows=1 width=120) (actual time=2.161..2.178 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.002 0.087 ↑ 1.0 1 1

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

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

Nested Loop Left Join (cost=1.02..11.12 rows=1 width=110) (actual time=0.061..0.075 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.067 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.58..5.64 rows=1 width=92) (actual time=0.053..0.067 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.052 0.052 ↑ 1.0 1 1

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

19. 0.011 0.011 ↑ 1.0 1 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.010..0.011 rows=1 loops=1)

  • Index Cond: (batch.charge_doc_id = id)
20. 0.007 0.007 ↓ 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.007..0.007 rows=0 loops=1)

  • Index Cond: (batch.charge_doc_id = id)
21. 0.010 0.010 ↑ 1.0 1 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.009..0.010 rows=1 loops=1)

  • Index Cond: (id = pwp.device_id)
  • Heap Fetches: 1
22. 2.085 2.085 ↑ 1.0 1 1

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

  • Index Cond: (d.id = device_id)
  • Filter: (NOT is_deleted)
23. 0.029 0.051 ↑ 1.0 1 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.050..0.051 rows=1 loops=1)

  • Index Cond: (road_id = pwp.road_id)
  • Filter: (gis_version_id = (SubPlan 2))
  • Rows Removed by Filter: 1
24.          

SubPlan (for Index Scan)

25. 0.000 0.022 ↑ 1.0 1 2

Limit (cost=1.08..1.09 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=2)

26. 0.014 0.022 ↑ 2.0 1 2

Sort (cost=1.08..1.09 rows=2 width=12) (actual time=0.011..0.011 rows=1 loops=2)

  • Sort Key: gis_versions.dt_start
  • Sort Method: top-N heapsort Memory: 25kB
27. 0.008 0.008 ↓ 3.0 6 2

Seq Scan on gis_versions (cost=0.00..1.07 rows=2 width=12) (actual time=0.002..0.004 rows=6 loops=2)

  • Filter: (dt_start <= pwp.end_move_dt)
28. 1.040 1.051 ↑ 2,787,651.0 1 1

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

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

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

30. 0.009 0.009 ↑ 1.0 1 1

Bitmap Index Scan on vehicles_id_is_deleted_account_id_idx (cost=0.00..2.78 rows=1 width=0) (actual time=0.009..0.009 rows=1 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.001 0.001 ↓ 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.001..0.001 rows=0 loops=1)

  • Index Cond: (v.vehicle_group_id = id)