explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4i6Z

Settings
# exclusive inclusive rows x rows loops node
1. 0.048 250.463 ↓ 5.0 5 1

HashAggregate (cost=33,142.92..33,142.93 rows=1 width=16) (actual time=250.456..250.463 rows=5 loops=1)

  • Group Key: product.product_id, asset.current_location
2. 0.009 250.415 ↓ 6.0 30 1

Nested Loop (cost=31,643.95..33,142.88 rows=5 width=16) (actual time=248.806..250.415 rows=30 loops=1)

3. 0.008 0.008 ↑ 1.0 1 1

Index Only Scan using products_index on product (cost=0.28..8.30 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=1)

  • Index Cond: (product_id = 416)
  • Heap Fetches: 0
4. 1.877 250.398 ↓ 6.0 30 1

Index Scan using asset_table_index on asset (cost=31,643.67..33,134.53 rows=5 width=16) (actual time=248.799..250.398 rows=30 loops=1)

  • Index Cond: (product_id = 416)
  • Filter: ((NOT deleted) AND (NOT (hashed SubPlan 1)) AND (status_id = 32))
  • Rows Removed by Filter: 3
5.          

SubPlan (for Index Scan)

6. 0.002 248.521 ↓ 0.0 0 1

Nested Loop (cost=1,491.95..31,643.25 rows=1 width=4) (actual time=248.521..248.521 rows=0 loops=1)

7. 0.031 248.519 ↓ 0.0 0 1

Nested Loop (cost=1,491.67..31,634.94 rows=1 width=8) (actual time=248.519..248.519 rows=0 loops=1)

8. 2.294 248.288 ↓ 6.7 40 1

Hash Join (cost=1,491.38..31,630.23 rows=6 width=12) (actual time=9.435..248.288 rows=40 loops=1)

  • Hash Cond: (reserved_asset.asset_id = asset_1.asset_id)
9. 244.103 244.103 ↑ 1.0 30,500 1

Seq Scan on reserved_asset (cost=0.00..30,018.86 rows=31,981 width=8) (actual time=0.039..244.103 rows=30,500 loops=1)

  • Filter: ((NOT deleted) AND (status = ANY ('{41,42}'::integer[])))
  • Rows Removed by Filter: 971825
10. 0.011 1.891 ↓ 3.1 31 1

Hash (cost=1,491.25..1,491.25 rows=10 width=8) (actual time=1.891..1.891 rows=31 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
11. 1.880 1.880 ↓ 3.1 31 1

Index Scan using asset_table_index on asset asset_1 (cost=0.41..1,491.25 rows=10 width=8) (actual time=0.238..1.880 rows=31 loops=1)

  • Index Cond: (product_id = 416)
  • Filter: ((NOT deleted) AND (status_id <> 39))
  • Rows Removed by Filter: 2
12. 0.200 0.200 ↓ 0.0 0 40

Index Scan using work_order_pkey on work_order (cost=0.29..0.78 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=40)

  • Index Cond: (work_order_id = reserved_asset.work_order_id)
  • Filter: (((((delivery_date_time - ((delivery_travel_days)::double precision * '1 day'::interval)) - '04:00:00'::interval) - ((delivery_travel_min)::double precision * '00:01:00'::interval)) <= '2019-11-05 18:00:00'::timestamp without time zone) AND ((((pickup_date_time + ((pickup_travel_days)::double precision * '1 day'::interval)) + ((pickup_travel_min)::double precision * '00:01:00'::interval)) + '04:00:00'::interval) >= '2019-11-04 08:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using products_index on product product_1 (cost=0.28..8.30 rows=1 width=4) (never executed)

  • Index Cond: (product_id = 416)
  • Heap Fetches: 0
Planning time : 0.886 ms