explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Td2N : Optimization for: Optimization for: plan #YhlT; plan #ITFU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 224.090 ↑ 1.0 1 1

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

2.          

CTE a

3. 0.017 222.771 ↑ 56.0 1 1

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

  • Group Key: s_1.product_id
4. 4.910 222.754 ↑ 3,000.0 2 1

Hash Join (cost=6,606.97..228,125.32 rows=6,000 width=15) (actual time=211.225..222.754 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. 197.836 214.161 ↑ 1.5 82,181 1

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

  • Recheck Cond: (product_id = 303682)
  • Filter: (NOT is_blocked)
  • Rows Removed by Filter: 92925
  • Heap Blocks: exact=89518
6. 16.325 16.325 ↓ 1.0 179,530 1

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

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

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

  • Buckets: 8192 Batches: 1 Memory Usage: 548kB
8. 2.474 2.827 ↓ 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.438..2.827 rows=6,793 loops=1)

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

  • Index Cond: (box_type_id = ANY ('{31,32,33}'::integer[]))
10. 0.002 224.090 ↑ 560.0 1 1

Nested Loop (cost=0.85..24,998.91 rows=560 width=49) (actual time=224.090..224.090 rows=1 loops=1)

11. 1.294 1.294 ↑ 560.0 1 1

Index Scan using shipment_pkey on shipment s (cost=0.42..24,984.80 rows=560 width=12) (actual time=1.294..1.294 rows=1 loops=1)

  • Filter: (product_id = 303682)
  • Rows Removed by Filter: 8812
12. 0.002 222.794 ↑ 1.0 1 1

Materialize (cost=0.42..5.71 rows=1 width=49) (actual time=222.794..222.794 rows=1 loops=1)

13. 0.002 222.792 ↑ 1.0 1 1

Nested Loop (cost=0.42..5.71 rows=1 width=49) (actual time=222.792..222.792 rows=1 loops=1)

14. 222.780 222.780 ↑ 1.0 1 1

CTE Scan on a (cost=0.00..1.26 rows=1 width=44) (actual time=222.780..222.780 rows=1 loops=1)

  • Filter: (product_id = 303682)
15. 0.010 0.010 ↑ 1.0 1 1

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

  • Index Cond: (product_id = 303682)