explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kZoB : Optimization for: Optimization for: Optimization for: plan #YhlT; plan #ITFU; plan #TmOc

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.002 348.033 ↑ 1.0 1 1

Limit (cost=228,141.88..228,461.59 rows=1 width=49) (actual time=348.033..348.033 rows=1 loops=1)

2.          

CTE a

3. 0.020 345.040 ↑ 56.0 1 1

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

  • Group Key: s_1.product_id
4. 5.773 345.020 ↑ 3,002.0 2 1

Hash Join (cost=6,606.97..228,095.30 rows=6,004 width=15) (actual time=328.233..345.020 rows=2 loops=1)

  • Hash Cond: (s_1.box_id = b.box_id)
5. 316.463 336.230 ↑ 1.5 82,179 1

Bitmap Heap Scan on su s_1 (cost=2,353.52..223,333.01 rows=119,678 width=19) (actual time=35.081..336.230 rows=82,179 loops=1)

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

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

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 303kB
8. 1.970 2.383 ↓ 1.0 6,793 1

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

  • Recheck Cond: (box_type_id = ANY ('{31,32,33}'::integer[]))
  • Heap Blocks: exact=989
9. 0.413 0.413 ↓ 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.413..0.413 rows=6,793 loops=1)

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

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

  • Join Filter: (a.product_id = s.product_id)
  • Rows Removed by Join Filter: 8812
11. 1.209 1.209 ↑ 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.209 rows=8,813 loops=1)

12. 0.000 343.707 ↑ 56.0 1 8,813

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

13. 0.003 345.054 ↑ 56.0 1 1

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

14. 345.042 345.042 ↑ 56.0 1 1

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

15. 0.009 0.009 ↑ 1.0 1 1

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

  • Index Cond: (product_id = a.product_id)