explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ITFU : Optimization for: plan #YhlT

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 245.812 ↑ 1.0 1 1

Limit (cost=228,171.87..228,491.58 rows=1 width=49) (actual time=245.812..245.812 rows=1 loops=1)

2.          

CTE a

3. 0.019 242.855 ↑ 56.0 1 1

GroupAggregate (cost=6,606.97..228,171.02 rows=56 width=15) (actual time=242.855..242.855 rows=1 loops=1)

  • Group Key: s_1.product_id
4. 5.027 242.836 ↑ 3,000.0 2 1

Hash Join (cost=6,606.97..228,125.32 rows=6,000 width=15) (actual time=237.819..242.836 rows=2 loops=1)

  • Hash Cond: (s_1.box_id = b.box_id)
  • Join Filter: ((s_1.document_id <> 4) OR (b.ns_path[2] <> 228))
5. 217.503 234.086 ↑ 1.5 82,178 1

Bitmap Heap Scan on su s_1 (cost=2,353.52..223,333.01 rows=119,678 width=23) (actual time=30.994..234.086 rows=82,178 loops=1)

  • Recheck Cond: (product_id = 303682)
  • Filter: (NOT is_blocked)
  • Rows Removed by Filter: 92922
  • Heap Blocks: exact=89511
6. 16.583 16.583 ↓ 1.0 179,449 1

Bitmap Index Scan on fki_su_product_id_fkey (cost=0.00..2,323.60 rows=174,271 width=0) (actual time=16.583..16.583 rows=179,449 loops=1)

  • Index Cond: (product_id = 303682)
7. 0.873 3.723 ↓ 1.0 6,793 1

Hash (cost=4,169.89..4,169.89 rows=6,685 width=33) (actual time=3.723..3.723 rows=6,793 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 548kB
8. 2.493 2.850 ↓ 1.0 6,793 1

Bitmap Heap Scan on box b (cost=119.06..4,169.89 rows=6,685 width=33) (actual time=0.441..2.850 rows=6,793 loops=1)

  • Recheck Cond: (box_type_id = ANY ('{31,32,33}'::integer[]))
  • Heap Blocks: exact=989
9. 0.357 0.357 ↓ 1.0 6,793 1

Bitmap Index Scan on fki_box_box_type_id_fkey (cost=0.00..117.39 rows=6,685 width=0) (actual time=0.357..0.357 rows=6,793 loops=1)

  • Index Cond: (box_type_id = ANY ('{31,32,33}'::integer[]))
10. 0.000 245.811 ↑ 2,090.0 1 1

Nested Loop (cost=0.85..668,192.26 rows=2,090 width=49) (actual time=245.811..245.811 rows=1 loops=1)

  • Join Filter: (a.product_id = s.product_id)
  • Rows Removed by Join Filter: 8812
11. 1.134 1.134 ↑ 87.1 8,813 1

Index Scan using shipment_pkey on shipment s (cost=0.42..23,065.54 rows=767,706 width=12) (actual time=0.008..1.134 rows=8,813 loops=1)

12. 3.893 246.764 ↑ 56.0 1 8,813

Materialize (cost=0.42..248.60 rows=56 width=49) (actual time=0.028..0.028 rows=1 loops=8,813)

13. 0.003 242.871 ↑ 56.0 1 1

Nested Loop (cost=0.42..248.32 rows=56 width=49) (actual time=242.869..242.871 rows=1 loops=1)

14. 242.858 242.858 ↑ 56.0 1 1

CTE Scan on a (cost=0.00..1.12 rows=56 width=44) (actual time=242.857..242.858 rows=1 loops=1)

15. 0.010 0.010 ↑ 1.0 1 1

Index Scan using product_pkey on product p (cost=0.42..4.40 rows=1 width=5) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (product_id = a.product_id)