explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fxvd

Settings
# exclusive inclusive rows x rows loops node
1. 14.077 14.077 ↑ 1.0 5 1

CTE Scan on available_items (cost=1,430.22..1,430.32 rows=5 width=120) (actual time=14.068..14.077 rows=5 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
2.          

CTE available_items

3. 0.007 14.072 ↑ 1.0 5 1

Limit (cost=1,430.21..1,430.22 rows=5 width=92) (actual time=14.065..14.072 rows=5 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.046 14.065 ↑ 30.4 5 1

Sort (cost=1,430.21..1,430.59 rows=152 width=92) (actual time=14.064..14.065 rows=5 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: top-N heapsort Memory: 26kB
  • Buffers: shared hit=907
5. 0.050 14.019 ↑ 2.5 60 1

GroupAggregate (cost=1,423.13..1,427.69 rows=152 width=92) (actual time=13.970..14.019 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.055 13.969 ↑ 2.5 60 1

Sort (cost=1,423.13..1,423.51 rows=152 width=84) (actual time=13.963..13.969 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.050 13.914 ↑ 2.5 60 1

Hash Join (cost=1,275.30..1,417.62 rows=152 width=84) (actual time=12.482..13.914 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.076 13.841 ↓ 1.1 174 1

Hash Join (cost=1,273.74..1,415.63 rows=152 width=75) (actual time=12.414..13.841 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.119 13.758 ↓ 1.1 174 1

Nested Loop (cost=1,272.60..1,413.82 rows=152 width=66) (actual time=12.401..13.758 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.430 13.117 ↑ 1.3 174 1

Hash Join (cost=1,272.32..1,318.64 rows=219 width=62) (actual time=12.383..13.117 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.328 0.328 ↑ 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.328 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.077 12.359 ↑ 1.3 174 1

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

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

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

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

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

Hash (cost=33.67..33.67 rows=1,167 width=42) (actual time=11.292..11.292 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.422 0.422 ↓ 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.422 rows=1,176 loops=1)

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

Index Scan using product_pkey on public.product (cost=0.28..0.42 rows=1 width=12) (actual time=0.003..0.003 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.012 0.023 ↑ 1.0 25 1

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

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

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

  • Output: brand.title, brand.id
  • Buffers: shared hit=1
Planning time : 2.431 ms
Execution time : 14.232 ms