explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A91Q

Settings
# exclusive inclusive rows x rows loops node
1. 10.470 125,193.354 ↑ 1.2 2,174 1

Unique (cost=209,133.98..209,211.65 rows=2,589 width=97) (actual time=125,177.826..125,193.354 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 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)
  • Buffers: shared hit=11921368 read=25343747, temp read=2595 written=2593
2. 26.627 125,182.884 ↑ 1.1 2,302 1

Sort (cost=209,133.98..209,140.45 rows=2,589 width=97) (actual time=125,177.820..125,182.884 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 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 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: 651kB
  • Buffers: shared hit=11921368 read=25343747, temp read=2595 written=2593
3. 109,402.896 125,156.257 ↑ 1.1 2,302 1

Nested Loop (cost=197,347.50..208,987.21 rows=2,589 width=97) (actual time=12,710.973..125,156.257 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 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
  • Buffers: shared hit=11921368 read=25343747, temp read=2595 written=2593
4. 17.925 15,063.110 ↑ 1.2 2,137 1

Nested Loop (cost=197,347.08..204,541.96 rows=2,568 width=86) (actual time=12,624.755..15,063.110 rows=2,137 loops=1)

  • Output: skus.id, skus.price_retail, products.display_name, products.is_closeout, products.reviewed_at, products.closed_out_at, products.is_preorder, brands.name
  • Join Filter: (products_1.id = skus.product_id)
  • Buffers: shared hit=1768 read=112624, temp read=2595 written=2593
5. 307.487 14,631.113 ↑ 1.2 639 1

Merge Join (cost=197,346.65..201,723.72 rows=785 width=88) (actual time=12,624.675..14,631.113 rows=639 loops=1)

  • Output: products_1.id, products.display_name, products.is_closeout, products.reviewed_at, products.closed_out_at, products.is_preorder, products.id, brands.name
  • Merge Cond: (products_1.id = products.id)
  • Buffers: shared hit=36 read=108453, temp read=2595 written=2593
6. 978.534 14,095.219 ↑ 1.9 115,315 1

Unique (cost=170,769.64..172,455.34 rows=214,048 width=9) (actual time=12,373.163..14,095.219 rows=115,315 loops=1)

  • Output: products_1.id, products_1.is_blemished
  • Buffers: shared read=103370, temp read=2595 written=2593
7. 1,579.079 13,116.685 ↓ 1.2 274,605 1

Sort (cost=170,769.64..171,331.54 rows=224,759 width=9) (actual time=12,373.154..13,116.685 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
  • Buffers: shared read=103370, temp read=2595 written=2593
8. 3,442.248 11,537.606 ↓ 1.2 274,764 1

Hash Right Join (cost=68,595.28..146,947.79 rows=224,759 width=9) (actual time=5,082.898..11,537.606 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
  • Buffers: shared read=103370, temp read=1956 written=1954
9. 3,014.298 3,014.298 ↓ 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.011..3,014.298 rows=995,992 loops=1)

  • Output: s.product_id, s.is_active
  • Buffers: shared read=51214
10. 361.365 5,081.060 ↓ 1.1 115,365 1

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

  • Output: products_1.id, products_1.is_blemished, products_1.type
  • Buckets: 65536 Batches: 2 Memory Usage: 3321kB
  • Buffers: shared read=52156, temp written=261
11. 4,699.211 4,719.695 ↓ 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=30.022..4,719.695 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 (products_1.is_blemished IS NOT TRUE) 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
  • Buffers: shared read=52156
12. 20.484 20.484 ↑ 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=20.484..20.484 rows=164,205 loops=1)

  • Buffers: shared read=451
13. 34.760 228.407 ↓ 4.0 6,392 1

Sort (cost=26,577.01..26,580.97 rows=1,584 width=80) (actual time=211.469..228.407 rows=6,392 loops=1)

  • Output: products.display_name, products.is_closeout, products.reviewed_at, products.closed_out_at, products.is_preorder, products.id, brands.name
  • Sort Key: products.id
  • Sort Method: quicksort Memory: 1106kB
  • Buffers: shared hit=36 read=5083
14. 31.568 193.647 ↓ 4.0 6,392 1

Nested Loop (cost=1,786.68..26,492.82 rows=1,584 width=80) (actual time=6.416..193.647 rows=6,392 loops=1)

  • Output: products.display_name, products.is_closeout, products.reviewed_at, products.closed_out_at, products.is_preorder, products.id, brands.name
  • Buffers: shared hit=36 read=5083
15. 4.343 4.343 ↑ 1.0 8 1

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

  • Output: brands.name, brands.id
  • Index Cond: (brands.id = ANY ('{14082,16499,16632,17396,17760,16575,17945,18007}'::integer[]))
  • Buffers: shared hit=11 read=13
16. 150.455 157.736 ↓ 3.9 799 8

Bitmap Heap Scan on public.products (cost=1,786.40..3,302.75 rows=206 width=72) (actual time=1.030..19.717 rows=799 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: (NOT (hashed SubPlan 1))
  • Heap Blocks: exact=5051
  • Buffers: shared hit=25 read=5070
17. 6.008 6.008 ↓ 1.9 799 8

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

  • Index Cond: (products.brand_id = brands.id)
  • Buffers: shared hit=14 read=27
18.          

SubPlan (forBitmap Heap Scan)

19. 0.007 1.273 ↓ 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.273..1.273 rows=0 loops=1)

  • Output: facet_set_product_orders.product_id
  • Recheck Cond: (facet_set_product_orders.facet_set_id = 2671)
  • Buffers: shared read=3
20. 1.266 1.266 ↓ 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.266..1.266 rows=0 loops=1)

  • Index Cond: (facet_set_product_orders.facet_set_id = 2671)
  • Buffers: shared read=3
21. 414.072 414.072 ↓ 1.5 3 639

Index Scan using idx_skus_product_id on public.skus (cost=0.42..3.57 rows=2 width=22) (actual time=0.381..0.648 rows=3 loops=639)

  • 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
  • Buffers: shared hit=1732 read=4171
22. 690.251 690.251 ↑ 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.275..0.323 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
  • Buffers: shared hit=985 read=8430