explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jj37

Settings
# exclusive inclusive rows x rows loops node
1. 456.188 456.188 ↑ 1.0 30 1

CTE Scan on available_items_with_discount (cost=73,082.18..73,082.78 rows=30 width=128) (actual time=51.592..456.188 rows=30 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=116267
2.          

CTE available_items

3. 0.024 3.151 ↑ 1.0 30 1

Limit (cost=1,432.18..1,432.25 rows=30 width=92) (actual time=3.115..3.151 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.059 3.127 ↑ 5.1 30 1

Sort (cost=1,432.18..1,432.56 rows=152 width=92) (actual time=3.114..3.127 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.044 3.068 ↑ 2.5 60 1

GroupAggregate (cost=1,423.13..1,427.69 rows=152 width=92) (actual time=3.025..3.068 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 3.024 ↑ 2.5 60 1

Sort (cost=1,423.13..1,423.51 rows=152 width=84) (actual time=3.019..3.024 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.975 ↑ 2.5 60 1

Hash Join (cost=1,275.30..1,417.62 rows=152 width=84) (actual time=1.774..2.975 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.065 2.913 ↓ 1.1 174 1

Hash Join (cost=1,273.74..1,415.63 rows=152 width=75) (actual time=1.714..2.913 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.201 2.840 ↓ 1.1 174 1

Nested Loop (cost=1,272.60..1,413.82 rows=152 width=66) (actual time=1.700..2.840 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.358 2.291 ↑ 1.3 174 1

Hash Join (cost=1,272.32..1,318.64 rows=219 width=62) (actual time=1.686..2.291 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.272 0.272 ↑ 1.0 2,058 1

Seq Scan on public.product_category (cost=0.00..36.18 rows=2,118 width=8) (actual time=0.006..0.272 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.065 1.661 ↑ 1.3 174 1

Hash (cost=1,269.59..1,269.59 rows=219 width=54) (actual time=1.661..1.661 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.077 1.596 ↑ 1.3 174 1

Hash Join (cost=74.26..1,269.59 rows=219 width=54) (actual time=0.889..1.596 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.780 ↑ 1.3 174 1

Bitmap Heap Scan on public.inventory_item (cost=26.00..1,220.75 rows=219 width=12) (actual time=0.142..0.780 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.095 0.095 ↓ 1.1 811 1

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

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

Hash (cost=33.67..33.67 rows=1,167 width=42) (actual time=0.739..0.739 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.372 0.372 ↓ 1.0 1,176 1

Seq Scan on public.product_variant (cost=0.00..33.67 rows=1,167 width=42) (actual time=0.006..0.372 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.005 0.008 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=13) (actual time=0.007..0.008 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.010 0.019 ↑ 1.0 25 1

Hash (cost=1.25..1.25 rows=25 width=13) (actual time=0.019..0.019 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. 3.252 456.132 ↑ 1.0 30 1

CTE Scan on available_items (cost=0.00..71,649.92 rows=30 width=128) (actual time=51.589..456.132 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, (SubPlan 2)
  • Buffers: shared hit=116267
25.          

SubPlan (for CTE Scan)

26. 0.090 452.880 ↑ 1.0 1 30

Aggregate (cost=2,388.30..2,388.31 rows=1 width=8) (actual time=15.095..15.096 rows=1 loops=30)

  • Output: max(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)
  • Buffers: shared hit=115360
27. 0.130 452.790 ↓ 2.0 2 30

Nested Loop (cost=7.02..2,388.29 rows=1 width=12) (actual time=14.642..15.093 rows=2 loops=30)

  • Output: price_rules.value_type, price_rules.value
  • Buffers: shared hit=115360
28. 38.471 452.340 ↑ 1.0 2 30

Nested Loop (cost=6.59..2,293.32 rows=2 width=20) (actual time=10.937..15.078 rows=2 loops=30)

  • Output: entitled_variant.price_rules_id, price_rules.value_type, price_rules.value, price_rules.id
  • Inner Unique: true
  • Buffers: shared hit=115097
29. 75.570 86.490 ↓ 3.7 839 30

Bitmap Heap Scan on public.entitled_variant (cost=6.17..559.05 rows=226 width=4) (actual time=0.422..2.883 rows=839 loops=30)

  • Output: entitled_variant.created_date, entitled_variant.updated_date, entitled_variant.id, entitled_variant.variant_id, entitled_variant.price_rules_id
  • Recheck Cond: (entitled_variant.variant_id = available_items.product_variant_id)
  • Heap Blocks: exact=13834
  • Buffers: shared hit=14042
30. 10.920 10.920 ↓ 3.8 859 30

Bitmap Index Scan on ix_entitled_variant_variant_id (cost=0.00..6.12 rows=226 width=0) (actual time=0.364..0.364 rows=859 loops=30)

  • Index Cond: (entitled_variant.variant_id = available_items.product_variant_id)
  • Buffers: shared hit=208
31. 327.379 327.379 ↓ 0.0 0 25,183

Index Scan using price_rules_pkey on public.price_rules (cost=0.42..7.67 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=25,183)

  • Output: price_rules.created_date, price_rules.updated_date, price_rules.id, price_rules.title, price_rules.target_type, price_rules.target_selection, price_rules.allocation_method, price_rules.value_type, price_rules.value, price_rules.usage_limit_per_customer, price_rules.usage_limit, price_rules.minimum_subtotal, price_rules.maximum_subtotal, price_rules.prerequisite_shipping_price_range, price_rules.starts_at, price_rules.ends_at, price_rules.type, price_rules.only_first_purchase, price_rules.maximum_discount, price_rules.max_variant_quantity_per_checkout, price_rules.utm_restriction, price_rules.created_by, price_rules.uptaded_by, price_rules.only_receive
  • Index Cond: (price_rules.id = entitled_variant.price_rules_id)
  • Filter: ((price_rules.value IS NOT NULL) AND (price_rules.value > '0'::double precision) AND (price_rules.target_selection = 2) AND (price_rules.starts_at <= now()) AND ((price_rules.ends_at IS NULL) OR (price_rules.ends_at >= now())))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=101055
32. 0.320 0.320 ↑ 1.0 1 64

Index Scan using ix_price_rule_id on public.discount (cost=0.43..47.47 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=64)

  • 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
  • Index Cond: (discount.price_rule_id = price_rules.id)
  • Filter: ((discount.active IS TRUE) AND (discount.discount_type = 'PROMOTION'::discounttypeenum) AND ((discount.type)::text = 'discount'::text))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=263
Planning time : 7.417 ms
Execution time : 456.452 ms