explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qF6H

Settings
# exclusive inclusive rows x rows loops node
1. 0.165 369.289 ↓ 0.0 0 1

Delete on client (cost=45,763.08..45,797.32 rows=10 width=34) (actual time=369.289..369.289 rows=0 loops=1)

2. 0.024 369.124 ↑ 1.0 10 1

Nested Loop (cost=45,763.08..45,797.32 rows=10 width=34) (actual time=369.071..369.124 rows=10 loops=1)

3. 0.041 369.040 ↑ 1.0 10 1

HashAggregate (cost=45,762.65..45,762.75 rows=10 width=32) (actual time=369.036..369.040 rows=10 loops=1)

  • Group Key: "ANY_subquery".id
4. 0.044 368.999 ↑ 1.0 10 1

Subquery Scan on ANY_subquery (cost=1,200.98..45,762.62 rows=10 width=32) (actual time=368.859..368.999 rows=10 loops=1)

5. 0.006 368.955 ↑ 1.0 10 1

Limit (cost=1,200.98..45,762.52 rows=10 width=4) (actual time=368.828..368.955 rows=10 loops=1)

6. 25.035 368.949 ↑ 33,040.4 10 1

Nested Loop Anti Join (cost=1,200.98..1,472,332,606.85 rows=330,404 width=4) (actual time=368.825..368.949 rows=10 loops=1)

7. 36.204 38.858 ↑ 34.7 9,533 1

Seq Scan on client client_1 (cost=120.62..49,211.10 rows=330,955 width=4) (actual time=4.707..38.858 rows=9,533 loops=1)

  • Filter: ((is_dealer IS NOT TRUE) AND (dispatch_point IS NOT TRUE) AND (NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)) AND (id <> ALL ('{1437,3186,2901,2342}'::integer[])))
  • Rows Removed by Filter: 749
8.          

SubPlan (for Seq Scan)

9. 2.357 2.357 ↓ 1.0 5,106 1

Seq Scan on skip (cost=0.00..88.67 rows=5,067 width=4) (actual time=0.010..2.357 rows=5,106 loops=1)

  • Filter: (client_id IS NOT NULL)
10. 0.297 0.297 ↑ 1.0 175 1

Seq Scan on driver (cost=0.00..18.85 rows=175 width=4) (actual time=0.021..0.297 rows=175 loops=1)

  • Filter: (position_client_id IS NOT NULL)
  • Rows Removed by Filter: 310
11. 114.396 305.056 ↑ 134,857.0 1 9,533

Bitmap Heap Scan on delivery_order (cost=1,080.35..3,105.62 rows=134,857 width=8) (actual time=0.032..0.032 rows=1 loops=9,533)

  • Recheck Cond: ((dest_customer_id = client_1.id) OR (source_dealer_id = client_1.id))
  • Heap Blocks: exact=9,915
12. 9.533 190.660 ↓ 0.0 0 9,533

BitmapOr (cost=1,080.35..1,080.35 rows=134,918 width=0) (actual time=0.020..0.020 rows=0 loops=9,533)

13. 152.528 152.528 ↑ 10.6 46 9,533

Bitmap Index Scan on fki_orderdestfk (cost=0.00..4.17 rows=489 width=0) (actual time=0.016..0.016 rows=46 loops=9,533)

  • Index Cond: (dest_customer_id = client_1.id)
14. 28.599 28.599 ↓ 0.0 0 9,533

Bitmap Index Scan on fki_ordersourcefk (cost=0.00..1,008.75 rows=134,429 width=0) (actual time=0.003..0.003 rows=0 loops=9,533)

  • Index Cond: (source_dealer_id = client_1.id)
15. 0.060 0.060 ↑ 1.0 1 10

Index Scan using clientpk on client (cost=0.43..3.45 rows=1 width=10) (actual time=0.006..0.006 rows=1 loops=10)

  • Index Cond: (id = "ANY_subquery".id)
Planning time : 1.256 ms
Execution time : 872.208 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
for constraint client_external_service_client_id_fkey 0.821 ms 10 0.082 ms
for constraint client_external_client_lookup_client_id_fkey 0.642 ms 10 0.064 ms
for constraint vehicle_base_rate_client_id_fkey 0.638 ms 10 0.064 ms
for constraint quote_billing_client_id_fkey 0.663 ms 10 0.066 ms
for constraint order_group_client_id_fkey 0.471 ms 10 0.047 ms
for constraint customer_exclusions_client_id_fkey 0.757 ms 10 0.076 ms
for constraint client_id_fk 0.674 ms 10 0.067 ms
for constraint adp_customer_client_id_fkey 2.310 ms 10 0.231 ms
for constraint adp_customer_parent_id_fkey 0.706 ms 10 0.071 ms
for constraint adpcust_dlr_fk 1.516 ms 10 0.152 ms
for constraint adporder_dlr_fk 1.645 ms 10 0.165 ms
for constraint agent_drop_point_id_fkey 0.702 ms 10 0.070 ms
for constraint cdmcustfktwo 6.555 ms 10 0.655 ms
for constraint cdmdlrfktwo 1.397 ms 10 0.140 ms
for constraint client_idfk 13.255 ms 10 1.326 ms
for constraint client_idfk 53.905 ms 10 5.391 ms
for constraint client_idfk 0.885 ms 10 0.088 ms
for constraint client_parent_billing_client_id_fkey 0.843 ms 10 0.084 ms
for constraint clientwarehousefk 2.283 ms 10 0.228 ms
for constraint clobmobifk 0.868 ms 10 0.087 ms
for constraint clsmsifk 0.800 ms 10 0.080 ms
for constraint custcodecustfk 0.779 ms 10 0.078 ms
for constraint customer_list_dealer_id_fkey 0.836 ms 10 0.084 ms
for constraint customer_list_source_dealer_id_fkey 0.782 ms 10 0.078 ms
for constraint ddp_dlr_fk 1.459 ms 10 0.146 ms
for constraint dealer_performance_dealer_id_fkey 3.711 ms 10 0.371 ms
for constraint delivery_order_billing_client_id_fkey 3.076 ms 10 0.308 ms
for constraint dispdealerfk 0.884 ms 10 0.088 ms
for constraint dlrhistdlrfk 0.782 ms 10 0.078 ms
for constraint dlrhistwhfk 0.705 ms 10 0.070 ms
for constraint doorderedbyfk 2.527 ms 10 0.253 ms
for constraint driver_dealer_change_new_dealer_id_fkey 0.877 ms 10 0.088 ms
for constraint driver_dealer_change_old_dealer_id_fkey 0.815 ms 10 0.081 ms
for constraint driver_position_client_id_fkey 3.412 ms 10 0.341 ms
for constraint driverdealerfk 2.045 ms 10 0.204 ms
for constraint fk_client_from_adp_feed 0.751 ms 10 0.075 ms
for constraint fmpbilltoclientfk 0.827 ms 10 0.083 ms
for constraint fmplocwhfk 0.883 ms 10 0.088 ms
for constraint mergeclientfk 0.761 ms 10 0.076 ms
for constraint orderdestfk 2.469 ms 10 0.247 ms
for constraint ordersourcefk 2.688 ms 10 0.269 ms
for constraint origdestfk 0.807 ms 10 0.081 ms
for constraint origsourcefk 2.389 ms 10 0.239 ms
for constraint recurring_routes_alternate_endpoint_id_fkey 0.756 ms 10 0.076 ms
for constraint recurring_routes_alternate_startpoint_id_fkey 0.673 ms 10 0.067 ms
for constraint scheduled_run_warehouse_id_fkey 0.757 ms 10 0.076 ms
for constraint scheduled_run_warehouse_id_fkey1 0.678 ms 10 0.068 ms
for constraint scheduled_run_warehouse_id_fkey2 0.680 ms 10 0.068 ms
for constraint shiptoclientfk 0.789 ms 10 0.079 ms
for constraint signerclientfk 0.777 ms 10 0.078 ms
for constraint sr_client_map_client_id_fkey 0.812 ms 10 0.081 ms
for constraint transfer_history_from_client_id_fkey 162.431 ms 10 16.243 ms
for constraint transfer_history_to_client_id_fkey 162.864 ms 10 16.286 ms
for constraint vehicle_dispatch_map_dealer_id_fkey 1.304 ms 10 0.130 ms
for constraint vehicle_location_fk 1.586 ms 10 0.159 ms
for constraint vendor_client_id_fkey 0.721 ms 10 0.072 ms
for constraint warehousefk 0.746 ms 10 0.075 ms
for constraint zone_dispatch_fk 0.821 ms 10 0.082 ms
for constraint client_idfk 0.762 ms 10 0.076 ms
for constraint client_idfk 0.681 ms 10 0.068 ms
for constraint client_idfk 0.660 ms 10 0.066 ms
for constraint client_idfk 0.679 ms 10 0.068 ms
for constraint transfer_location_setting_local_client_id_fkey 0.678 ms 10 0.068 ms
for constraint sms_text_log_location_id_fkey 0.701 ms 10 0.070 ms
for constraint client_email_template_map_client_id_fkey 0.545 ms 10 0.055 ms
for constraint fk_pickup_source_id_map_to_source 0.787 ms 10 0.079 ms
for constraint driver_default_start_client_id_fkey 1.757 ms 10 0.176 ms
for constraint driver_default_end_client_id_fkey 1.470 ms 10 0.147 ms
for constraint customer_list_default_start_client_id_fkey 0.779 ms 10 0.078 ms
for constraint customer_list_default_end_client_id_fkey 0.753 ms 10 0.075 ms
for constraint scorev2_route_performance_by_dealer_summary_dealer_id_fkey 1.865 ms 10 0.186 ms
for constraint scorev2_driver_dealer_performance_dealer_id_fkey 19.505 ms 10 1.950 ms
for constraint scorev2_driver_dealer_performance_summary_dealer_id_fkey 1.466 ms 10 0.147 ms
for constraint driver_home_client_id_fkey 1.615 ms 10 0.162 ms
for constraint client_notification_recipient_client_id_fkey 3.596 ms 10 0.360 ms
for constraint location_id_fk 0.729 ms 10 0.073 ms