explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G5KF

Settings
# exclusive inclusive rows x rows loops node
1. 2.195 6.383 ↑ 2.0 1 1

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

2.          

CTE batch

3. 0.003 3.923 ↑ 1.0 1 1

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

4. 0.003 0.042 ↑ 1.0 1 1

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

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

  • Index Cond: (id = $26)
6. 0.018 0.018 ↑ 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.015..0.018 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. 3.878 3.878 ↑ 1.0 1 1

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

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

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

9. 0.002 4.176 ↑ 2.0 1 1

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

10. 0.029 4.174 ↑ 2.0 1 1

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

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

12. 0.007 4.134 ↑ 2.0 1 1

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

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

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

Nested Loop Left Join (cost=1.88..19.10 rows=1 width=120) (actual time=0.133..4.005 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)))
  • Rows Removed by Join Filter: 1
15. 0.006 3.979 ↑ 1.0 1 1

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

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

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

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

Nested Loop Left Join (cost=0.58..5.64 rows=1 width=92) (actual time=0.084..3.947 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. 3.927 3.927 ↑ 1.0 1 1

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

19. 0.017 0.017 ↑ 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.016..0.017 rows=1 loops=1)

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

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

  • Index Cond: (id = pwp.device_id)
  • Heap Fetches: 1
22. 0.016 0.016 ↓ 2.0 2 1

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

  • Index Cond: (d.id = device_id)
  • Filter: (NOT is_deleted)
23. 0.034 0.100 ↑ 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.099..0.100 rows=1 loops=1)

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

SubPlan (for Index Scan)

25. 0.006 0.066 ↑ 1.0 1 6

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

26. 0.036 0.060 ↑ 2.0 1 6

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

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

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

  • Filter: (dt_start <= pwp.end_move_dt)
28. 0.005 0.019 ↑ 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=0.019..0.019 rows=1 loops=1)

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

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

30. 0.011 0.011 ↑ 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.011..0.011 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)