explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c5Gx

Settings
# exclusive inclusive rows x rows loops node
1. 12.407 122,855.810 ↑ 1.2 2,174 1

Unique (cost=230,821.61..230,905.16 rows=2,571 width=105) (actual time=122,837.407..122,855.810 rows=2,174 loops=1)

2. 28.337 122,843.403 ↑ 1.1 2,302 1

Sort (cost=230,821.61..230,828.03 rows=2,571 width=105) (actual time=122,837.401..122,843.403 rows=2,302 loops=1)

  • Sort Key: products.display_name, skus.id, raw_skus.vendor_sku, brands.name, (sku_option_description(skus.id)), skus.price_retail, (CASE WHEN ((alternatives: SubPlan 1 or hashed SubPlan 2) IS TRUE) THEN 'CARB Compliant'::text ELSE ''::text END), (CASE WHEN products.is_closeout THEN 'Closeout'::text ELSE CASE WHEN ((products.reviewed_at >= (now() - '90 days'::interval)) AND (products.reviewed_at <= now())) THEN 'New'::text ELSE NULL::text END END), (CASE WHEN products.is_closeout THEN (((((date_part('year'::text, products.closed_out_at))::text || '-'::text) || (date_part('month'::text, products.closed_out_at))::text) || '-'::text) || (date_part('day'::text, products.closed_out_at))::text) ELSE NULL::text END), (CASE WHEN products.is_preorder THEN '0'::bigint ELSE sku_inventory_for_vendor(skus.id, '142'::bigint) END), (CASE WHEN products.is_preorder THEN '0'::bigint ELSE sku_inventory_for_vendor(skus.id, '232'::bigint) END), (CASE WHEN products.is_preorder THEN '0'::bigint ELSE sku_inventory_for_vendor(skus.id, '141'::bigint) END)
  • Sort Method: quicksort Memory: 662kB
3. 110,806.720 122,815.066 ↑ 1.1 2,302 1

Nested Loop (cost=197,354.86..230,675.98 rows=2,571 width=105) (actual time=8,917.990..122,815.066 rows=2,302 loops=1)

4. 18.268 10,996.885 ↑ 1.2 2,137 1

Nested Loop (cost=197,354.43..204,536.79 rows=2,550 width=94) (actual time=8,498.571..10,996.885 rows=2,137 loops=1)

  • Join Filter: (products_1.id = skus.product_id)
5. 301.840 10,478.622 ↑ 1.3 615 1

Merge Join (cost=197,354.00..201,730.96 rows=780 width=88) (actual time=8,495.703..10,478.622 rows=615 loops=1)

  • Merge Cond: (products_1.id = products.id)
6. 965.912 9,817.266 ↑ 1.9 115,315 1

Unique (cost=170,769.64..172,455.34 rows=214,048 width=9) (actual time=8,106.939..9,817.266 rows=115,315 loops=1)

7. 1,626.418 8,851.354 ↓ 1.2 274,605 1

Sort (cost=170,769.64..171,331.54 rows=224,759 width=9) (actual time=8,106.933..8,851.354 rows=274,605 loops=1)

  • Sort Key: products_1.id, products_1.is_blemished
  • Sort Method: external merge Disk: 5096kB
8. 3,458.868 7,224.936 ↓ 1.2 274,764 1

Hash Right Join (cost=68,595.28..146,947.79 rows=224,759 width=9) (actual time=949.900..7,224.936 rows=274,764 loops=1)

  • Hash Cond: (s.product_id = products_1.id)
  • Filter: (s.is_active OR ((products_1.type)::text = 'SuperProduct'::text))
  • Rows Removed by Filter: 19098
9. 2,821.590 2,821.590 ↓ 1.0 995,992 1

Seq Scan on skus s (cost=0.00..61,173.89 rows=995,989 width=9) (actual time=2.403..2,821.590 rows=995,992 loops=1)

10. 316.391 944.478 ↓ 1.1 115,365 1

Hash (cost=66,629.48..66,629.48 rows=107,024 width=17) (actual time=944.478..944.478 rows=115,365 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3321kB
11. 606.857 628.087 ↓ 1.1 115,365 1

Bitmap Heap Scan on products products_1 (cost=2,660.19..66,629.48 rows=107,024 width=17) (actual time=30.577..628.087 rows=115,365 loops=1)

  • Recheck Cond: (is_active AND is_reviewed)
  • Filter: ((NOT is_sneak_peak) AND (NOT is_blemished) AND ((out_of_inventory_at IS NULL) OR ((NOT is_blemished) AND (NOT is_closeout) AND (out_of_inventory_at > (now() - '6 mons'::interval)))))
  • Rows Removed by Filter: 48837
  • Heap Blocks: exact=51705
12. 21.230 21.230 ↑ 1.0 164,205 1

Bitmap Index Scan on idx_products_id_is_active_and_reviewed (cost=0.00..2,633.43 rows=164,417 width=0) (actual time=21.230..21.230 rows=164,205 loops=1)

13. 35.347 359.516 ↓ 4.0 6,365 1

Sort (cost=26,584.36..26,588.29 rows=1,572 width=80) (actual time=343.765..359.516 rows=6,365 loops=1)

  • Sort Key: products.id
  • Sort Method: quicksort Memory: 1086kB
14. 31.057 324.169 ↓ 4.0 6,365 1

Nested Loop (cost=1,786.68..26,500.90 rows=1,572 width=80) (actual time=7.175..324.169 rows=6,365 loops=1)

15. 3.608 3.608 ↑ 1.0 8 1

Index Scan using brands_pkey on brands (cost=0.28..54.34 rows=8 width=16) (actual time=3.491..3.608 rows=8 loops=1)

  • Index Cond: (id = ANY ('{14082,16499,16632,17396,17760,16575,17945,18007}'::integer[]))
16. 282.106 289.504 ↓ 3.9 796 8

Bitmap Heap Scan on products (cost=1,786.40..3,303.78 rows=204 width=72) (actual time=1.010..36.188 rows=796 loops=8)

  • Recheck Cond: (brand_id = brands.id)
  • Filter: (((type)::text <> 'SuperProduct'::text) AND (NOT (hashed SubPlan 3)))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=5051
17. 6.424 6.424 ↓ 1.9 799 8

Bitmap Index Scan on idx_products_brand_id (cost=0.00..11.51 rows=412 width=0) (actual time=0.803..0.803 rows=799 loops=8)

  • Index Cond: (brand_id = brands.id)
18.          

SubPlan (forBitmap Heap Scan)

19. 0.012 0.974 ↓ 0.0 0 1

Bitmap Heap Scan on facet_set_product_orders (cost=11.98..1,773.69 rows=458 width=8) (actual time=0.974..0.974 rows=0 loops=1)

  • Recheck Cond: (facet_set_id = 2671)
20. 0.962 0.962 ↓ 0.0 0 1

Bitmap Index Scan on idx_fspo_facet_set_id (cost=0.00..11.87 rows=458 width=0) (actual time=0.962..0.962 rows=0 loops=1)

  • Index Cond: (facet_set_id = 2671)
21. 499.995 499.995 ↓ 1.5 3 615

Index Scan using idx_skus_product_id on skus (cost=0.42..3.57 rows=2 width=22) (actual time=0.499..0.813 rows=3 loops=615)

  • Index Cond: (product_id = products.id)
  • Filter: (is_active AND ((NOT products.is_closeout) OR (products.is_closeout AND (sku_inventory(id) > 0)) OR (products.is_closeout AND (sku_inventory(id) = 0) AND (products.closed_out_at >= (now() - '180 days'::interval)) AND (products.closed_out_at <= now()))))
  • Rows Removed by Filter: 1
22. 846.252 846.252 ↑ 2.0 1 2,137

Index Scan using idx_raw_skus_sku_id on raw_skus (cost=0.43..0.66 rows=2 width=19) (actual time=0.334..0.396 rows=1 loops=2,137)

  • Index Cond: (sku_id = skus.id)
  • Filter: (distributor_id <> ALL ('{4,61}'::bigint[]))
  • Rows Removed by Filter: 0
23.          

SubPlan (forNested Loop)

24. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_facets_products_facet_id_product_id on facets_products (cost=0.43..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((facet_id = 2094) AND (product_id = products.id))
  • Heap Fetches: 0
25. 163.635 165.209 ↓ 1.0 1,385 1

Bitmap Heap Scan on facets_products facets_products_1 (cost=34.78..4,214.37 rows=1,335 width=8) (actual time=2.743..165.209 rows=1,385 loops=1)

  • Recheck Cond: (facet_id = 2094)
  • Heap Blocks: exact=783
26. 1.574 1.574 ↓ 1.0 1,385 1

Bitmap Index Scan on idx_facets_products_facet_id_product_id (cost=0.00..34.44 rows=1,335 width=0) (actual time=1.574..1.574 rows=1,385 loops=1)

  • Index Cond: (facet_id = 2094)