explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t9xs : Optimization for: plan #llhF

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 322.628 31,941.727 ↓ 1.2 325,595 1

Gather (cost=1,583.66..88,000,869.60 rows=269,936 width=89) (actual time=9.465..31,941.727 rows=325,595 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 3,724.802 31,619.099 ↑ 1.0 108,532 3 / 3

Nested Loop (cost=583.66..87,972,876.00 rows=112,473 width=89) (actual time=34.057..31,619.099 rows=108,532 loops=3)

3. 1,685.937 2,957.134 ↑ 1.3 1,558,573 3 / 3

Hash Left Join (cost=546.62..82,243.52 rows=1,950,749 width=68) (actual time=33.364..2,957.134 rows=1,558,573 loops=3)

  • Hash Cond: (iip.place_id = bip.id)
  • Join Filter: (iip.place_type = 3)
  • Rows Removed by Join Filter: 196
4. 1,238.193 1,238.193 ↑ 1.3 1,558,573 3 / 3

Parallel Seq Scan on instance_in_place iip (cost=0.00..76,576.19 rows=1,950,749 width=48) (actual time=0.022..1,238.193 rows=1,558,573 loops=3)

  • Filter: (stock_type = ANY ('{1}'::integer[]))
  • Rows Removed by Filter: 45044
5. 24.098 33.004 ↑ 1.0 18,157 3 / 3

Hash (cost=319.61..319.61 rows=18,161 width=20) (actual time=33.004..33.004 rows=18,157 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1179kB
6. 8.906 8.906 ↑ 1.0 18,157 3 / 3

Seq Scan on boxing_in_place bip (cost=0.00..319.61 rows=18,161 width=20) (actual time=0.504..8.906 rows=18,157 loops=3)

7. 9,351.436 24,937.163 ↓ 0.0 0 4,675,718 / 3

Bitmap Heap Scan on place p (cost=37.03..45.04 rows=1 width=41) (actual time=0.016..0.016 rows=0 loops=4,675,718)

  • Recheck Cond: (((place_id = iip.place_id) AND (place_type = iip.place_type)) OR ((place_id = bip.place_id) AND (place_type = bip.place_type)))
  • Filter: ((parent_place_id = ANY ('{99}'::integer[])) AND (parent_place_type = ANY ('{4}'::integer[])) AND (((bip.id IS NULL) AND (place_id = iip.place_id) AND (place_type = iip.place_type)) OR ((bip.id IS NOT NULL) AND (place_id = bip.place_id) AND (place_type = bip.place_type))))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=2333431
8. 1,558.573 15,585.727 ↓ 0.0 0 4,675,718 / 3

BitmapOr (cost=37.03..37.03 rows=3 width=0) (actual time=0.010..0.010 rows=0 loops=4,675,718)

9. 14,027.154 14,027.154 ↑ 1.0 2 4,675,718 / 3

Bitmap Index Scan on place_place_idx (cost=0.00..0.47 rows=2 width=0) (actual time=0.009..0.009 rows=2 loops=4,675,718)

  • Index Cond: ((place_id = iip.place_id) AND (place_type = iip.place_type))
10. 0.000 0.000 ↓ 0.0 0 4,675,718 / 3

Bitmap Index Scan on place_place_idx (cost=0.00..3.27 rows=2 width=0) (actual time=0.000..0.000 rows=0 loops=4,675,718)

  • Index Cond: ((place_id = bip.place_id) AND (place_type = bip.place_type))
Planning time : 1.155 ms
Execution time : 31,977.819 ms