explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xGhx

Settings
# exclusive inclusive rows x rows loops node
1. 145.066 145.066 ↓ 3.7 25,183 1

CTE Scan on available_items_with_discount (cost=40,338.15..40,473.71 rows=6,778 width=128) (actual time=116.550..145.066 rows=25,183 loops=1)

  • Output: 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_price, available_items_with_discount.max_price, available_items_with_discount.showcase_order, available_items_with_discount.price_with_discount
  • Buffers: shared hit=10873
2.          

CTE available_items

3. 0.010 2.904 ↑ 1.0 30 1

Limit (cost=1,432.18..1,432.25 rows=30 width=92) (actual time=2.893..2.904 rows=30 loops=1)

  • Output: product_variant.title, inventory_item.product_variant_id, brand.title, category.title, (min(inventory_item.price)), (max(inventory_item.price)), product.showcase_order, product_variant.id, brand.id, category.id, product.id
  • Buffers: shared hit=907
4. 0.044 2.894 ↑ 5.1 30 1

Sort (cost=1,432.18..1,432.56 rows=152 width=92) (actual time=2.892..2.894 rows=30 loops=1)

  • Output: product_variant.title, inventory_item.product_variant_id, brand.title, category.title, (min(inventory_item.price)), (max(inventory_item.price)), product.showcase_order, product_variant.id, brand.id, category.id, product.id
  • Sort Key: product.showcase_order
  • Sort Method: quicksort Memory: 33kB
  • Buffers: shared hit=907
5. 0.043 2.850 ↑ 2.5 60 1

GroupAggregate (cost=1,423.13..1,427.69 rows=152 width=92) (actual time=2.809..2.850 rows=60 loops=1)

  • Output: product_variant.title, inventory_item.product_variant_id, brand.title, category.title, min(inventory_item.price), max(inventory_item.price), product.showcase_order, product_variant.id, brand.id, category.id, product.id
  • Group Key: inventory_item.product_variant_id, product_variant.id, brand.id, category.id, product.id
  • Buffers: shared hit=907
6. 0.049 2.807 ↑ 2.5 60 1

Sort (cost=1,423.13..1,423.51 rows=152 width=84) (actual time=2.802..2.807 rows=60 loops=1)

  • Output: inventory_item.product_variant_id, product_variant.id, brand.id, category.id, product.id, product_variant.title, brand.title, category.title, inventory_item.price, product.showcase_order
  • Sort Key: inventory_item.product_variant_id, brand.id, category.id, product.id
  • Sort Method: quicksort Memory: 33kB
  • Buffers: shared hit=907
7. 0.043 2.758 ↑ 2.5 60 1

Hash Join (cost=1,275.30..1,417.62 rows=152 width=84) (actual time=1.545..2.758 rows=60 loops=1)

  • Output: inventory_item.product_variant_id, product_variant.id, brand.id, category.id, product.id, product_variant.title, brand.title, category.title, inventory_item.price, product.showcase_order
  • Inner Unique: true
  • Hash Cond: (product.brand_id = brand.id)
  • Buffers: shared hit=907
8. 0.066 2.695 ↓ 1.1 174 1

Hash Join (cost=1,273.74..1,415.63 rows=152 width=75) (actual time=1.486..2.695 rows=174 loops=1)

  • Output: inventory_item.product_variant_id, inventory_item.price, product_variant.title, product_variant.id, product.showcase_order, product.id, product.brand_id, category.title, category.id
  • Inner Unique: true
  • Hash Cond: (product_category.category_id = category.id)
  • Buffers: shared hit=906
9. 0.211 2.622 ↓ 1.1 174 1

Nested Loop (cost=1,272.60..1,413.82 rows=152 width=66) (actual time=1.473..2.622 rows=174 loops=1)

  • Output: inventory_item.product_variant_id, inventory_item.price, product_variant.title, product_variant.id, product.showcase_order, product.id, product.brand_id, product_category.category_id
  • Inner Unique: true
  • Join Filter: (product_variant.product_id = product.id)
  • Buffers: shared hit=905
10. 0.351 2.063 ↑ 1.3 174 1

Hash Join (cost=1,272.32..1,318.64 rows=219 width=62) (actual time=1.453..2.063 rows=174 loops=1)

  • Output: inventory_item.product_variant_id, inventory_item.price, product_variant.title, product_variant.id, product_variant.product_id, product_category.product_id, product_category.category_id
  • Hash Cond: (product_category.product_id = product_variant.product_id)
  • Buffers: shared hit=383
11. 0.281 0.281 ↑ 1.0 2,058 1

Seq Scan on public.product_category (cost=0.00..36.18 rows=2,118 width=8) (actual time=0.008..0.281 rows=2,058 loops=1)

  • Output: product_category.created_date, product_category.updated_date, product_category.id, product_category.product_id, product_category.category_id, product_category."order
  • Buffers: shared hit=15
12. 0.067 1.431 ↑ 1.3 174 1

Hash (cost=1,269.59..1,269.59 rows=219 width=54) (actual time=1.431..1.431 rows=174 loops=1)

  • Output: inventory_item.product_variant_id, inventory_item.price, product_variant.title, product_variant.id, product_variant.product_id
  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=368
13. 0.078 1.364 ↑ 1.3 174 1

Hash Join (cost=74.26..1,269.59 rows=219 width=54) (actual time=0.658..1.364 rows=174 loops=1)

  • Output: inventory_item.product_variant_id, inventory_item.price, product_variant.title, product_variant.id, product_variant.product_id
  • Inner Unique: true
  • Hash Cond: (inventory_item.product_variant_id = product_variant.id)
  • Buffers: shared hit=368
14. 0.685 0.776 ↑ 1.3 174 1

Bitmap Heap Scan on public.inventory_item (cost=26.00..1,220.75 rows=219 width=12) (actual time=0.138..0.776 rows=174 loops=1)

  • Output: inventory_item.created_date, inventory_item.updated_date, inventory_item.id, inventory_item.poc_id, inventory_item.product_variant_id, inventory_item.quantity, inventory_item.price, inventory_item.published_date, inventory_item.available_date
  • Recheck Cond: (inventory_item.poc_id = 21)
  • Filter: ((inventory_item.available_date IS NOT NULL) AND (inventory_item.price > '0'::double precision))
  • Rows Removed by Filter: 527
  • Heap Blocks: exact=337
  • Buffers: shared hit=346
15. 0.091 0.091 ↓ 1.1 811 1

Bitmap Index Scan on poc_product_variant (cost=0.00..25.95 rows=737 width=0) (actual time=0.091..0.091 rows=811 loops=1)

  • Index Cond: (inventory_item.poc_id = 21)
  • Buffers: shared hit=9
16. 0.255 0.510 ↓ 1.0 1,176 1

Hash (cost=33.67..33.67 rows=1,167 width=42) (actual time=0.510..0.510 rows=1,176 loops=1)

  • Output: product_variant.title, product_variant.id, product_variant.product_id
  • Buckets: 2048 Batches: 1 Memory Usage: 104kB
  • Buffers: shared hit=22
17. 0.255 0.255 ↓ 1.0 1,176 1

Seq Scan on public.product_variant (cost=0.00..33.67 rows=1,167 width=42) (actual time=0.005..0.255 rows=1,176 loops=1)

  • Output: product_variant.title, product_variant.id, product_variant.product_id
  • Buffers: shared hit=22
18. 0.348 0.348 ↑ 1.0 1 174

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

  • Output: product.created_date, product.updated_date, product.id, product.title, product.tags, product.labels, product.abi_product, product.brand_id, product.rgb, product.showcase_order, product.attachment, product.description, product.short_description, product.type, product.has_fixed_price
  • Index Cond: (product.id = product_category.product_id)
  • Buffers: shared hit=522
19. 0.004 0.007 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=13) (actual time=0.007..0.007 rows=6 loops=1)

  • Output: category.title, category.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
20. 0.003 0.003 ↑ 1.0 6 1

Seq Scan on public.category (cost=0.00..1.06 rows=6 width=13) (actual time=0.002..0.003 rows=6 loops=1)

  • Output: category.title, category.id
  • Buffers: shared hit=1
21. 0.011 0.020 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=13) (actual time=0.020..0.020 rows=25 loops=1)

  • Output: brand.title, brand.id
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
22. 0.009 0.009 ↑ 1.0 25 1

Seq Scan on public.brand (cost=0.00..1.25 rows=25 width=13) (actual time=0.004..0.009 rows=25 loops=1)

  • Output: brand.title, brand.id
  • Buffers: shared hit=1
23.          

CTE available_items_with_discount

24. 9.535 127.521 ↓ 3.7 25,183 1

Hash Right Join (cost=9,875.89..38,905.90 rows=6,778 width=128) (actual time=116.543..127.521 rows=25,183 loops=1)

  • Output: available_items.title, available_items.product_variant_id, available_items.brand, available_items.category, available_items.min_price, available_items.max_price, available_items.showcase_order, CASE WHEN (price_rules.value_type = 1) THEN (available_items.max_price - price_rules.value) WHEN (price_rules.value_type = 2) THEN (('1'::double precision - price_rules.value) * available_items.max_price) ELSE NULL::double precision END
  • Hash Cond: (discount.price_rule_id = price_rules.id)
  • Buffers: shared hit=10873
25. 33.643 42.523 ↓ 13.0 18,078 1

Bitmap Heap Scan on public.discount (cost=2,891.84..31,827.97 rows=1,388 width=4) (actual time=17.601..42.523 rows=18,078 loops=1)

  • Output: discount.created_date, discount.updated_date, discount.id, discount.code, discount.price_rule_id, discount.member_email, discount.type, discount.friend_order_number, discount.active, discount.discount_type, discount.created_by, discount.uptaded_by, discount.image_url
  • Recheck Cond: ((discount.type)::text = 'discount'::text)
  • Filter: ((discount.active IS TRUE) AND (discount.discount_type = 'PROMOTION'::discounttypeenum))
  • Rows Removed by Filter: 62851
  • Heap Blocks: exact=6621
  • Buffers: shared hit=6958
26. 8.880 8.880 ↑ 1.1 81,176 1

Bitmap Index Scan on ix_type (cost=0.00..2,891.49 rows=87,342 width=0) (actual time=8.880..8.880 rows=81,176 loops=1)

  • Index Cond: ((discount.type)::text = 'discount'::text)
  • Buffers: shared hit=337
27. 10.111 75.463 ↓ 3.7 25,183 1

Hash (cost=6,899.33..6,899.33 rows=6,778 width=136) (actual time=75.463..75.463 rows=25,183 loops=1)

  • Output: available_items.title, available_items.product_variant_id, available_items.brand, available_items.category, available_items.min_price, available_items.max_price, available_items.showcase_order, price_rules.value_type, price_rules.value, price_rules.id
  • Buckets: 32768 (originally 8192) Batches: 1 (originally 1) Memory Usage: 2991kB
  • Buffers: shared hit=3915
28. 6.347 65.352 ↓ 3.7 25,183 1

Hash Left Join (cost=3,864.09..6,899.33 rows=6,778 width=136) (actual time=15.249..65.352 rows=25,183 loops=1)

  • Output: available_items.title, available_items.product_variant_id, available_items.brand, available_items.category, available_items.min_price, available_items.max_price, available_items.showcase_order, price_rules.value_type, price_rules.value, price_rules.id
  • Inner Unique: true
  • Hash Cond: (entitled_variant.price_rules_id = price_rules.id)
  • Buffers: shared hit=3915
29. 27.343 46.716 ↓ 3.7 25,183 1

Hash Right Join (cost=0.97..3,018.42 rows=6,778 width=124) (actual time=2.949..46.716 rows=25,183 loops=1)

  • Output: available_items.title, available_items.product_variant_id, available_items.brand, available_items.category, available_items.min_price, available_items.max_price, available_items.showcase_order, entitled_variant.price_rules_id
  • Hash Cond: (entitled_variant.variant_id = available_items.product_variant_id)
  • Buffers: shared hit=1971
30. 16.440 16.440 ↑ 1.0 137,064 1

Seq Scan on public.entitled_variant (cost=0.00..2,435.39 rows=137,139 width=8) (actual time=0.007..16.440 rows=137,064 loops=1)

  • Output: entitled_variant.created_date, entitled_variant.updated_date, entitled_variant.id, entitled_variant.variant_id, entitled_variant.price_rules_id
  • Buffers: shared hit=1064
31. 0.014 2.933 ↑ 1.0 30 1

Hash (cost=0.60..0.60 rows=30 width=120) (actual time=2.933..2.933 rows=30 loops=1)

  • Output: available_items.title, available_items.product_variant_id, available_items.brand, available_items.category, available_items.min_price, available_items.max_price, available_items.showcase_order
  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=907
32. 2.919 2.919 ↑ 1.0 30 1

CTE Scan on available_items (cost=0.00..0.60 rows=30 width=120) (actual time=2.894..2.919 rows=30 loops=1)

  • Output: available_items.title, available_items.product_variant_id, available_items.brand, available_items.category, available_items.min_price, available_items.max_price, available_items.showcase_order
  • Buffers: shared hit=907
33. 1.755 12.289 ↓ 3.6 7,119 1

Hash (cost=3,838.24..3,838.24 rows=1,990 width=16) (actual time=12.289..12.289 rows=7,119 loops=1)

  • Output: price_rules.value_type, price_rules.value, price_rules.id
  • Buckets: 8192 (originally 2048) Batches: 1 (originally 1) Memory Usage: 426kB
  • Buffers: shared hit=1944
34. 6.063 10.534 ↓ 3.6 7,119 1

Bitmap Heap Scan on public.price_rules (cost=330.37..3,838.24 rows=1,990 width=16) (actual time=4.715..10.534 rows=7,119 loops=1)

  • Output: price_rules.value_type, price_rules.value, price_rules.id
  • Recheck Cond: (((price_rules.ends_at IS NULL) AND (price_rules.starts_at <= now()) AND (price_rules.target_selection = 2) AND (price_rules.value IS NOT NULL) AND (price_rules.value > '0'::double precision)) OR ((price_rules.ends_at >= now()) AND (price_rules.starts_at <= now()) AND (price_rules.target_selection = 2) AND (price_rules.value IS NOT NULL) AND (price_rules.value > '0'::double precision)))
  • Filter: ((price_rules.starts_at <= now()) AND ((price_rules.ends_at IS NULL) OR (price_rules.ends_at >= now())))
  • Heap Blocks: exact=1823
  • Buffers: shared hit=1944
35. 0.002 4.471 ↓ 0.0 0 1

BitmapOr (cost=330.37..330.37 rows=1,996 width=0) (actual time=4.471..4.471 rows=0 loops=1)

  • Buffers: shared hit=121
36. 2.696 2.696 ↓ 3.8 6,774 1

Bitmap Index Scan on price_rules_ends_at_starts_at_target_selection_value_index (cost=0.00..296.31 rows=1,806 width=0) (actual time=2.696..2.696 rows=6,774 loops=1)

  • Index Cond: ((price_rules.ends_at IS NULL) AND (price_rules.starts_at <= now()) AND (price_rules.target_selection = 2) AND (price_rules.value IS NOT NULL) AND (price_rules.value > '0'::double precision))
  • Buffers: shared hit=47
37. 1.773 1.773 ↓ 2.3 443 1

Bitmap Index Scan on price_rules_ends_at_starts_at_target_selection_value_index (cost=0.00..33.06 rows=190 width=0) (actual time=1.773..1.773 rows=443 loops=1)

  • Index Cond: ((price_rules.ends_at >= now()) AND (price_rules.starts_at <= now()) AND (price_rules.target_selection = 2) AND (price_rules.value IS NOT NULL) AND (price_rules.value > '0'::double precision))
  • Buffers: shared hit=74
Planning time : 2.976 ms
Execution time : 148.624 ms