explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

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

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

2.          

CTE a

3. 0.018 142.771 ↑ 56.0 1 1

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

  • Group Key: s_1.product_id
4. 4.209 142.753 ↑ 3,000.0 2 1

Hash Join (cost=6,606.97..228,125.32 rows=6,000 width=15) (actual time=138.024..142.753 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. 116.862 135.192 ↑ 1.5 82,179 1

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

  • Recheck Cond: (product_id = 303682)
  • Filter: (NOT is_blocked)
  • Rows Removed by Filter: 92922
  • Heap Blocks: exact=89512
6. 18.330 18.330 ↓ 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=18.330..18.330 rows=179,463 loops=1)

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

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

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

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

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

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

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

12. 0.000 141.008 ↑ 56.0 1 8,813

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

13. 0.003 142.785 ↑ 56.0 1 1

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

14. 142.773 142.773 ↑ 56.0 1 1

CTE Scan on a (cost=0.00..1.12 rows=56 width=44) (actual time=142.773..142.773 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)