explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4GPZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.025 23.591 ↑ 1.0 200 1

Limit (cost=1,821.06..4,294.90 rows=200 width=243) (actual time=18.376..23.591 rows=200 loops=1)

2. 0.159 23.566 ↑ 23.6 200 1

Nested Loop Left Join (cost=1,821.06..60,290.10 rows=4,727 width=243) (actual time=18.375..23.566 rows=200 loops=1)

3. 0.125 5.207 ↑ 23.6 200 1

Nested Loop Left Join (cost=10.69..58,420.61 rows=4,727 width=211) (actual time=0.127..5.207 rows=200 loops=1)

  • Filter: (((sa.step_id = 6) AND ((max(shipment.real_pick_up_date)) >= (now() - '6 mons'::interval))) OR (sa.step_id <> 6))
  • Rows Removed by Filter: 14
4. 0.120 4.440 ↑ 24.2 214 1

Nested Loop Left Join (cost=2.38..15,147.17 rows=5,184 width=211) (actual time=0.105..4.440 rows=214 loops=1)

5. 0.108 2.608 ↑ 24.2 214 1

Hash Join (cost=1.25..9,107.81 rows=5,184 width=205) (actual time=0.066..2.608 rows=214 loops=1)

  • Hash Cond: (s.step_action_id = sa.id)
6. 2.483 2.483 ↑ 24.2 214 1

Seq Scan on stocks s (cost=0.00..9,087.72 rows=5,184 width=137) (actual time=0.038..2.483 rows=214 loops=1)

  • Filter: ((NOT is_deleted) AND (orientation = 'Supplier'::text) AND (((type_of_return = 'A return'::text) AND (cr2_date IS NOT NULL)) OR ((type_of_return = 'B return'::text) AND (tfp_date IS NOT NULL))))
  • Rows Removed by Filter: 237
7. 0.008 0.017 ↑ 1.0 11 1

Hash (cost=1.11..1.11 rows=11 width=76) (actual time=0.017..0.017 rows=11 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
8. 0.009 0.009 ↑ 1.0 11 1

Seq Scan on steps_actions sa (cost=0.00..1.11 rows=11 width=76) (actual time=0.007..0.009 rows=11 loops=1)

9. 1.498 1.712 ↑ 1.0 1 214

Aggregate (cost=1.14..1.15 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=214)

10. 0.214 0.214 ↑ 2.0 2 214

Seq Scan on ref_stock_statuses status (cost=0.00..1.11 rows=4 width=40) (actual time=0.001..0.001 rows=2 loops=214)

  • Filter: (id = ANY (s.statuses))
  • Rows Removed by Filter: 3
11. 0.214 0.642 ↑ 1.0 1 214

Aggregate (cost=8.30..8.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=214)

12. 0.428 0.428 ↓ 0.0 0 214

Index Scan using idx_shipments_stock_id on shipments shipment (cost=0.29..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=214)

  • Index Cond: (stock_id = s.id)
13. 0.034 18.200 ↑ 1.0 1 200

Materialize (cost=1,810.38..1,810.40 rows=1 width=32) (actual time=0.091..0.091 rows=1 loops=200)

14. 2.649 18.166 ↑ 1.0 1 1

Aggregate (cost=1,810.38..1,810.39 rows=1 width=32) (actual time=18.166..18.166 rows=1 loops=1)

15. 5.583 15.517 ↑ 1.0 13,709 1

Hash Join (cost=1,341.01..1,776.10 rows=13,709 width=4) (actual time=8.654..15.517 rows=13,709 loops=1)

  • Hash Cond: (ots.stock_id = s_1.id)
16. 1.355 1.355 ↑ 1.0 13,709 1

Seq Scan on transfer_orders ots (cost=0.00..399.09 rows=13,709 width=8) (actual time=0.007..1.355 rows=13,709 loops=1)

17. 4.622 8.579 ↑ 1.0 27,081 1

Hash (cost=1,002.50..1,002.50 rows=27,081 width=4) (actual time=8.579..8.579 rows=27,081 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,209kB
18. 3.957 3.957 ↑ 1.0 27,081 1

Index Only Scan using stocks_pkey on stocks s_1 (cost=0.29..1,002.50 rows=27,081 width=4) (actual time=0.019..3.957 rows=27,081 loops=1)

  • Heap Fetches: 0
Planning time : 1.004 ms
Execution time : 23.768 ms