explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SY6E

Settings
# exclusive inclusive rows x rows loops node
1. 3.855 64.900 ↑ 14.3 14 1

HashAggregate (cost=18,828.99..18,830.99 rows=200 width=124) (actual time=64.896..64.900 rows=14 loops=1)

  • Group Key: available_items_with_discount.title, available_items_with_discount.product_variant_id, available_items_with_discount.brand, available_items_with_discount.category, available_items_with_discount.min, available_items_with_discount.max
  • Buffers: shared hit=21386
2.          

CTE available_items

3. 0.010 3.096 ↑ 1.0 15 1

Limit (cost=1,929.48..1,929.63 rows=15 width=77) (actual time=3.082..3.096 rows=15 loops=1)

  • Buffers: shared hit=1023
4. 0.075 3.086 ↑ 5.6 15 1

HashAggregate (cost=1,929.48..1,930.32 rows=84 width=77) (actual time=3.082..3.086 rows=15 loops=1)

  • Group Key: inventory_item.id, product_variant.id, brand.id, category.id
  • Buffers: shared hit=1023
5. 0.105 3.011 ↑ 1.1 80 1

Nested Loop Left Join (cost=117.02..1,928.22 rows=84 width=77) (actual time=1.536..3.011 rows=80 loops=1)

  • Buffers: shared hit=1023
6. 0.048 2.826 ↑ 1.1 80 1

Nested Loop (cost=116.89..1,913.34 rows=84 width=68) (actual time=1.532..2.826 rows=80 loops=1)

  • Buffers: shared hit=863
7. 0.005 0.005 ↑ 1.0 1 1

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

  • Filter: (id = 94)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=1
8. 0.115 2.773 ↑ 1.1 80 1

Nested Loop (cost=116.89..1,911.43 rows=84 width=59) (actual time=1.525..2.773 rows=80 loops=1)

  • Buffers: shared hit=862
9. 0.085 2.498 ↑ 1.1 80 1

Hash Join (cost=116.61..1,875.22 rows=85 width=63) (actual time=1.516..2.498 rows=80 loops=1)

  • Hash Cond: (product_variant.product_id = product_category.product_id)
  • Buffers: shared hit=618
10. 0.127 1.877 ↑ 1.2 191 1

Hash Join (cost=73.42..1,830.01 rows=234 width=55) (actual time=0.965..1.877 rows=191 loops=1)

  • Hash Cond: (inventory_item.product_variant_id = product_variant.id)
  • Buffers: shared hit=604
11. 0.863 0.999 ↑ 1.2 191 1

Bitmap Heap Scan on inventory_item (cost=32.83..1,786.20 rows=234 width=16) (actual time=0.208..0.999 rows=191 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: 948
  • Heap Blocks: exact=576
  • Buffers: shared hit=586
12. 0.136 0.136 ↓ 1.0 1,139 1

Bitmap Index Scan on poc_product_variant (cost=0.00..32.77 rows=1,093 width=0) (actual time=0.136..0.136 rows=1,139 loops=1)

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

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

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

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

  • Buffers: shared hit=18
15. 0.191 0.536 ↑ 1.8 338 1

Hash (cost=35.42..35.42 rows=621 width=8) (actual time=0.536..0.536 rows=338 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
  • Buffers: shared hit=14
16. 0.345 0.345 ↓ 1.0 646 1

Seq Scan on product_category (cost=0.00..35.42 rows=621 width=8) (actual time=0.010..0.345 rows=646 loops=1)

  • Filter: (category_id = 94)
  • Rows Removed by Filter: 1103
  • Buffers: shared hit=14
17. 0.160 0.160 ↑ 1.0 1 80

Index Scan using product_pkey on product (cost=0.28..0.42 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=80)

  • Index Cond: (id = product_variant.product_id)
  • Buffers: shared hit=244
18. 0.080 0.080 ↑ 1.0 1 80

Index Scan using brand_pkey on brand (cost=0.14..0.17 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=80)

  • Index Cond: (id = product.brand_id)
  • Buffers: shared hit=160
19.          

CTE available_items_with_discount

20. 5.819 57.143 ↓ 2.6 4,866 1

Nested Loop Left Join (cost=1.33..16,828.60 rows=1,887 width=128) (actual time=3.160..57.143 rows=4,866 loops=1)

  • Buffers: shared hit=21386
21. 3.785 51.324 ↓ 2.6 4,866 1

Nested Loop Left Join (cost=0.91..2,926.65 rows=1,887 width=132) (actual time=3.150..51.324 rows=4,866 loops=1)

  • Buffers: shared hit=21255
22. 19.065 37.807 ↓ 2.6 4,866 1

Hash Right Join (cost=0.49..1,683.66 rows=1,887 width=120) (actual time=3.138..37.807 rows=4,866 loops=1)

  • Hash Cond: (entitled_variant.variant_id = available_items.product_variant_id)
  • Buffers: shared hit=1769
23. 15.618 15.618 ↑ 1.0 64,637 1

Seq Scan on entitled_variant (cost=0.00..1,413.86 rows=66,786 width=8) (actual time=0.003..15.618 rows=64,637 loops=1)

  • Buffers: shared hit=746
24. 0.010 3.124 ↑ 1.0 15 1

Hash (cost=0.30..0.30 rows=15 width=116) (actual time=3.124..3.124 rows=15 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1023
25. 3.114 3.114 ↑ 1.0 15 1

CTE Scan on available_items (cost=0.00..0.30 rows=15 width=116) (actual time=3.085..3.114 rows=15 loops=1)

  • Buffers: shared hit=1023
26. 9.732 9.732 ↓ 0.0 0 4,866

Index Scan using price_rules_pkey on price_rules (cost=0.42..0.65 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4,866)

  • Index Cond: (id = entitled_variant.price_rules_id)
  • Filter: ((value IS NOT NULL) AND (value > '0'::double precision) AND (target_selection = 2) AND (starts_at <= now()) AND ((ends_at IS NULL) OR (ends_at >= now())))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=19486
27. 0.000 0.000 ↓ 0.0 0 4,866

Index Scan using ix_price_rule_id on discount (cost=0.42..7.34 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=4,866)

  • Index Cond: (price_rule_id = price_rules.id)
  • Filter: ((active IS TRUE) AND (discount_type = 'PROMOTION'::discounttypeenum) AND ((type)::text = 'discount'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=131
28. 61.045 61.045 ↓ 2.6 4,866 1

CTE Scan on available_items_with_discount (cost=0.00..37.74 rows=1,887 width=124) (actual time=3.162..61.045 rows=4,866 loops=1)

  • Buffers: shared hit=21386
Planning time : 1.742 ms
Execution time : 65.173 ms