supabase-postgres-best-practices - query-covering-indexes

title: Use Covering Indexes to Avoid Table Lookupsimpact: MEDIUM-HIGHimpactDescription: 2-5x faster queries by eliminating heap fetchestags: indexes, covering-index, include, index-only-scan使用覆盖索引避免回表查询覆盖索引包含查询所需的全部列从而支持索引扫描而无需访问表本身。错误做法索引扫描 堆表取数createindexusers_email_idxonusers(email);-- 必须从表堆中获取 name 和 created_atselectemail,name,created_atfromuserswhereemailuserexample.com;正确做法使用 INCLUDE 的索引扫描-- 将不可用于搜索的列包含在索引中createindexusers_email_idxonusers(email)include(name,created_at);-- 所有列都由索引提供无需访问表selectemail,name,created_atfromuserswhereemailuserexample.com;对于 SELECT 中需要但不需要过滤的列使用 INCLUDE-- 按 status 搜索但还需要 customer_id 和 totalcreateindexorders_status_idxonorders(status)include(customer_id,total);selectstatus,customer_id,totalfromorderswherestatusshipped;参考仅索引扫描