explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IgHy : Optimization for: plan #eW8a

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4.365 4.365 ↓ 15.0 15 1

CTE Scan on available_items (cost=4,093.91..4,093.93 rows=1 width=116) (actual time=4.339..4.365 rows=15 loops=1)

  • Buffers: shared hit=1911
2.          

CTE available_items

3. 0.005 4.346 ↓ 15.0 15 1

Limit (cost=4,093.90..4,093.91 rows=1 width=77) (actual time=4.336..4.346 rows=15 loops=1)

  • Buffers: shared hit=1911
4. 0.081 4.341 ↓ 15.0 15 1

HashAggregate (cost=4,093.90..4,093.91 rows=1 width=77) (actual time=4.336..4.341 rows=15 loops=1)

  • Group Key: inventory_item.id, product_variant.id, brand.id, category.id
  • Buffers: shared hit=1911
5. 0.582 4.260 ↓ 79.0 79 1

Nested Loop Left Join (cost=82.36..4,093.88 rows=1 width=77) (actual time=1.083..4.260 rows=79 loops=1)

  • Join Filter: (brand.id = product.brand_id)
  • Rows Removed by Join Filter: 1896
  • Buffers: shared hit=1911
6. 0.116 3.125 ↓ 79.0 79 1

Nested Loop (cost=82.36..4,092.37 rows=1 width=68) (actual time=1.073..3.125 rows=79 loops=1)

  • Buffers: shared hit=1832
7. 0.089 2.930 ↓ 79.0 79 1

Nested Loop (cost=82.36..4,091.28 rows=1 width=59) (actual time=1.069..2.930 rows=79 loops=1)

  • Buffers: shared hit=1753
8. 0.149 1.891 ↑ 1.3 190 1

Hash Join (cost=82.09..1,892.96 rows=252 width=55) (actual time=1.045..1.891 rows=190 loops=1)

  • Hash Cond: (inventory_item.product_variant_id = product_variant.id)
  • Buffers: shared hit=604
9. 0.767 0.922 ↑ 1.3 190 1

Bitmap Heap Scan on inventory_item (cost=41.50..1,848.90 rows=252 width=16) (actual time=0.218..0.922 rows=190 loops=1)

  • Recheck Cond: (poc_id = ANY ('{21,23}'::integer[]))
  • Filter: ((available_date IS NOT NULL) AND (price > '0'::double precision))
  • Rows Removed by Filter: 949
  • Heap Blocks: exact=576
  • Buffers: shared hit=586
10. 0.155 0.155 ↑ 1.0 1,139 1

Bitmap Index Scan on poc_product_variant (cost=0.00..41.44 rows=1,180 width=0) (actual time=0.155..0.155 rows=1,139 loops=1)

  • Index Cond: (poc_id = ANY ('{21,23}'::integer[]))
  • Buffers: shared hit=10
11. 0.410 0.820 ↓ 1.0 1,030 1

Hash (cost=28.04..28.04 rows=1,004 width=39) (actual time=0.820..0.820 rows=1,030 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 90kB
  • Buffers: shared hit=18
12. 0.410 0.410 ↓ 1.0 1,030 1

Seq Scan on product_variant (cost=0.00..28.04 rows=1,004 width=39) (actual time=0.004..0.410 rows=1,030 loops=1)

  • Buffers: shared hit=18
13. 0.570 0.950 ↓ 0.0 0 190

Index Scan using product_pkey on product (cost=0.28..8.71 rows=1 width=8) (actual time=0.004..0.005 rows=0 loops=190)

  • Index Cond: (id = product_variant.product_id)
  • Filter: ((SubPlan 1) = 94)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1149
14.          

SubPlan (forIndex Scan)

15. 0.190 0.380 ↑ 1.0 1 190

Limit (cost=0.28..8.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=190)

  • Buffers: shared hit=572
16. 0.190 0.190 ↑ 1.0 1 190

Index Scan using ix_product_id on product_category (cost=0.28..8.29 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=190)

  • Index Cond: (product_id = product.id)
  • Buffers: shared hit=572
17. 0.079 0.079 ↑ 1.0 1 79

Seq Scan on category (cost=0.00..1.07 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=79)

  • Filter: (id = 94)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=79
18. 0.553 0.553 ↓ 1.1 25 79

Seq Scan on brand (cost=0.00..1.23 rows=23 width=13) (actual time=0.001..0.007 rows=25 loops=79)

  • Buffers: shared hit=79
Planning time : 0.676 ms
Execution time : 4.447 ms