explain.depesz.com

PostgreSQL's explain analyze made readable

Result: faCu

Settings
# exclusive inclusive rows x rows loops node
1. 12.030 121,027.571 ↑ 1.2 2,174 1

Unique (cost=230,821.61..230,905.16 rows=2,571 width=105) (actual time=121,009.749..121,027.571 rows=2,174 loops=1)

  • Output: skus.id, raw_skus.vendor_sku, brands.name, products.display_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)
2. 31.579 121,015.541 ↑ 1.1 2,302 1

Sort (cost=230,821.61..230,828.03 rows=2,571 width=105) (actual time=121,009.742..121,015.541 rows=2,302 loops=1)

  • Output: skus.id, raw_skus.vendor_sku, brands.name, products.display_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 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. 105,040.078 120,983.962 ↑ 1.1 2,302 1

Nested Loop (cost=197,354.86..230,675.98 rows=2,571 width=105) (actual time=12,530.292..120,983.962 rows=2,302 loops=1)

  • Output: skus.id, raw_skus.vendor_sku, brands.name, products.display_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
4. 18.215 14,887.907 ↑ 1.2 2,137 1

Nested Loop (cost=197,354.43..204,536.79 rows=2,550 width=94) (actual time=12,257.885..14,887.907 rows=2,137 loops=1)

  • Output: skus.id, skus.price_retail, products.display_name, products.id, products.is_closeout, products.reviewed_at, products.closed_out_at, products.is_preorder, brands.name
  • Join Filter: (products_1.id = skus.product_id)
5. 318.998 14,335.872 ↑ 1.3 615 1

Merge Join (cost=197,354.00..201,730.96 rows=780 width=88) (actual time=12,254.456..14,335.872 rows=615 loops=1)

  • Output: products_1.id, products.display_name, products.id, products.is_closeout, products.reviewed_at, products.closed_out_at, products.is_preorder, brands.name
  • Merge Cond: (products_1.id = products.id)
6. 1,019.314 13,435.541 ↑ 1.9 115,315 1

Unique (cost=170,769.64..172,455.34 rows=214,048 width=9) (actual time=11,643.941..13,435.541 rows=115,315 loops=1)

  • Output: products_1.id, products_1.is_blemished
7. 1,649.262 12,416.227 ↓ 1.2 274,605 1

Sort (cost=170,769.64..171,331.54 rows=224,759 width=9) (actual time=11,643.935..12,416.227 rows=274,605 loops=1)

  • Output: products_1.id, products_1.is_blemished
  • Sort Key: products_1.id, products_1.is_blemished
  • Sort Method: external merge Disk: 5096kB
8. 3,464.697 10,766.965 ↓ 1.2 274,764 1

Hash Right Join (cost=68,595.28..146,947.79 rows=224,759 width=9) (actual time=4,486.076..10,766.965 rows=274,764 loops=1)

  • Output: products_1.id, products_1.is_blemished
  • 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,818.906 2,818.906 ↓ 1.0 995,992 1

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

  • Output: s.product_id, s.is_active
10. 378.870 4,483.362 ↓ 1.1 115,365 1

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

  • Output: products_1.id, products_1.is_blemished, products_1.type
  • Buckets: 65536 Batches: 2 Memory Usage: 3321kB
11. 4,085.995 4,104.492 ↓ 1.1 115,365 1

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

  • Output: products_1.id, products_1.is_blemished, products_1.type
  • Recheck Cond: (products_1.is_active AND products_1.is_reviewed)
  • Filter: ((NOT products_1.is_sneak_peak) AND (NOT products_1.is_blemished) AND ((products_1.out_of_inventory_at IS NULL) OR ((NOT products_1.is_blemished) AND (NOT products_1.is_closeout) AND (products_1.out_of_inventory_at > (now() - '6 mons'::interval)))))
  • Rows Removed by Filter: 48837
  • Heap Blocks: exact=51705
12. 18.497 18.497 ↑ 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=18.497..18.497 rows=164,205 loops=1)

13. 37.954 581.333 ↓ 4.0 6,365 1

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

  • Output: products.display_name, products.id, products.is_closeout, products.reviewed_at, products.closed_out_at, products.is_preorder, brands.name
  • Sort Key: products.id
  • Sort Method: quicksort Memory: 1086kB
14. 34.147 543.379 ↓ 4.0 6,365 1

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

  • Output: products.display_name, products.id, products.is_closeout, products.reviewed_at, products.closed_out_at, products.is_preorder, brands.name
15. 7.144 7.144 ↑ 1.0 8 1

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

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

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

  • Output: products.id, products.name, products.long_description, products.price, products.sale_price, products.apply_sale_price, products.show_our_price, products.allow_discounting, products.is_taxable, products.is_active, products.sitemask, products.creator_id, products.created_at, products.modifier_id, products.modified_at, products.brand_id, products.application_imports, products.is_hazardous, products.display_name, products.price_wholesale, products.price_retail, products.vendor_sku, products.is_closeout, products.show_retail_price, products.gender_mask, products.is_reviewed, products.image_looked_for, products.keywords, products.reviewer_id, products.shipping_notes, products.product_family_id, products.closed_out_at, products.featured_until, products.additional_shipping_charge, products.url_alias, products.is_application, products.model_year, products.max_price_adjustment, products.size_chart_url, products.apparel_type, products.apparel_material_mask, products.editorial, products.price_unadvertised, products.sales, products.reviewed_at, products.allow_closeout_exchange, products.is_preorder, products.sales_by_quarter, products.video_embed_url, products.url_alias_prefix, products.ignore_vendor_inventory_until, products.return_stats, products.type, products.super_product_id, products.needs_mask, products.browser_title, products.meta_description, products.meta_keywords, products.is_sneak_peak, products.redirect_to_navigation_tree_id, products.redirect_to_url_alias, products.stock_eta_date, products.killed_at, products.updated_from_preorder_at, products.hs_tariff_number, products.fitment_notes, products.straight_to_page, products.force_fitment_notes_acknowledgement, products.is_blemished, products.blemish_notes, products.universal_fit_message_override, products.blemish_caption, products.country_of_origin, products.out_of_inventory_at, products.receiving_notes, products.teaser, products.auto_generate_teaser, products.sneak_peek_description, products.sneak_peek_theme_is_ct, products.long_description_values, products.teaser_values, products.site_url_alias_prefixes, products.site_sales, products.site_sales_by_quarter, products.price_retail_max, products.sales_1mo, products.site_sales_1mo, products.is_oem, products.is_reduced_visibility
  • Recheck Cond: (products.brand_id = brands.id)
  • Filter: (((products.type)::text <> 'SuperProduct'::text) AND (NOT (hashed SubPlan 3)))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=5051
17. 4.192 4.192 ↓ 1.9 799 8

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

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

SubPlan (forBitmap Heap Scan)

19. 0.006 1.033 ↓ 0.0 0 1

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

  • Output: facet_set_product_orders.product_id
  • Recheck Cond: (facet_set_product_orders.facet_set_id = 2671)
20. 1.027 1.027 ↓ 0.0 0 1

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

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

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

  • Output: skus.id, skus.product_id, skus.price_wholesale, skus.price_retail, skus.width, skus.height, skus.length, skus.cube, skus.price_adjustment, skus.start_display_at, skus.stop_display_at, skus.vendor_sku, skus.is_active, skus.created_at, skus.modified_at, skus.modifier_id, skus.product_image_id, skus.out_of_inventory_at, skus.is_closeout, skus.mfg_part_number, skus.application, skus.inventory_last_updated_at, skus.weight, skus.is_price_adjustment_override, skus.sales, skus.sales_by_quarter, skus.is_immediately_fulfillable, skus.stock_level_target, skus.sales_prev_year_quarter, skus.availability_3mo, skus.availability_12mo, skus.showroom_sales, skus.showroom_sales_by_quarter, skus.showroom_sales_prev_year_quarter, skus.availability_ly3mo, skus.pending_notifications, skus.is_compound_sku, skus.stock_eta_date, skus.marketplace_identifier, skus.site_sales, skus.site_sales_by_quarter, skus.site_sales_prev_year_quarter, skus.sales_1mo, skus.showroom_sales_1mo, skus.site_sales_1mo, skus.availability_1mo
  • Index Cond: (skus.product_id = products.id)
  • Filter: (skus.is_active AND ((NOT products.is_closeout) OR (products.is_closeout AND (sku_inventory(skus.id) > 0)) OR (products.is_closeout AND (sku_inventory(skus.id) = 0) AND (products.closed_out_at >= (now() - '180 days'::interval)) AND (products.closed_out_at <= now()))))
  • Rows Removed by Filter: 1
22. 897.540 897.540 ↑ 2.0 1 2,137

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

  • Output: raw_skus.id, raw_skus.distributor_id, raw_skus.vendor_sku, raw_skus.name, raw_skus.display_name, raw_skus.description, raw_skus.status, raw_skus.size, raw_skus.color, raw_skus.brand, raw_skus.vendor_code, raw_skus.category, raw_skus.price_wholesale, raw_skus.price_retail, raw_skus.weight, raw_skus.length, raw_skus.width, raw_skus.height, raw_skus.cube, raw_skus.is_hazardous, raw_skus.segment, raw_skus.application, raw_skus.mfg_part_number, raw_skus.created_at, raw_skus.sku_id, raw_skus.filename, raw_skus.category2, raw_skus.category3, raw_skus.map_lifted, raw_skus.imported_name, raw_skus.imported_category1, raw_skus.imported_category2, raw_skus.imported_category3, raw_skus.imported_description, raw_skus.images, raw_skus.price_retail_map, raw_skus.sound, raw_skus.creator_id, raw_skus.base_wholesale_price, raw_skus.base_wholesale_price_currency, raw_skus.quantity_per_unit, raw_skus.vendor_notes, raw_skus.updated_at, raw_skus.country_of_origin, raw_skus.hs_tariff_number, raw_skus.external_data
  • Index Cond: (raw_skus.sku_id = skus.id)
  • Filter: (raw_skus.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 public.facets_products (cost=0.43..8.45 rows=1 width=0) (never executed)

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

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

  • Output: facets_products_1.product_id
  • Recheck Cond: (facets_products_1.facet_id = 2094)
  • Heap Blocks: exact=783
26. 1.526 1.526 ↓ 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.526..1.526 rows=1,385 loops=1)

  • Index Cond: (facets_products_1.facet_id = 2094)