Rails7+PostgreSQL百万级listings表查询过慢，如何优化性能？

我有一个近100列的数据库表listings，其中25列为STRING类型，其余为INT/DECIMAL/BOOL类型。用户筛选数据时生成的查询语句如下：

SELECT "listings".*
FROM "listings"
WHERE "listings"."source" = 3
  AND "listings"."listing_type" = 4
ORDER BY listings.created_at DESC
LIMIT 100 OFFSET 0

该查询在Rails应用中响应时间超4分钟，生产环境报错无日志：

Completed 200 OK in 244372ms (Views: 71.4ms | ActiveRecord: 244263.2ms | Allocations: 17464)

我曾尝试为WHERE和ORDER BY子句的列添加索引，但无效果。在pgAdmin执行EXPLAIN ANALYSE的结果如下：

"Limit  (cost=0.42..1903.89 rows=100 width=1587) (actual time=64960.075..170237.003 rows=8 loops=1)"
"  ->  Index Scan Backward using index_listings_on_created_at on listings  (cost=0.42..437892.59 rows=23005 width=1587) (actual time=64960.073..170236.988 rows=8 loops=1)"
"        Filter: ((source = 3) AND (listing_type = 4))"
"        Rows Removed by Filter: 1022248"
"Planning Time: 25.418 ms"
"Execution Time: 170237.100 ms"

执行该语句耗时2分51秒，慢查询导致应用无法使用。

listings表结构：

- id (int)
- source (int)
- title (string)
- description (text)
- listing_type (int)
- transaction_type (int)
- region (int)
- uuid (uuid)
- ...other columns...
- created_at (datetime)
- updated_at (datetime)

现有索引：

INDEX NAME                          INDEXDEF
listings_pkey                       CREATE UNIQUE INDEX listings_pkey ON public.listings USING btree (id)
index_listings_on_listing_type      CREATE INDEX index_listings_on_listing_type ON public.listings USING btree (listing_type)
index_listings_on_transaction_type  CREATE INDEX index_listings_on_transaction_type ON public.listings USING btree (transaction_type)
index_listings_on_region            CREATE INDEX index_listings_on_region ON public.listings USING btree (region)
index_listings_on_created_at        CREATE INDEX index_listings_on_created_at ON public.listings USING btree (created_at)
index_listings_on_uuid              CREATE INDEX index_listings_on_uuid ON public.listings USING btree (uuid)

此外，我使用will_paginate做分页，该gem会执行SELECT COUNT(*)，这对大表也是性能问题。

1. 创建复合索引解决主查询性能瓶颈 #

从执行计划能看到，数据库当前是先按created_at倒序扫描索引，再逐一过滤source=3和listing_type=4的条件，总共扫描了100多万行才找到8条符合条件的数据——这是性能差的核心原因。

需要创建覆盖WHERE过滤条件+ORDER BY排序的复合索引，让数据库直接通过索引定位目标行，无需大量扫描后过滤：

CREATE INDEX idx_listings_source_type_created_at ON listings USING btree (source, listing_type, created_at DESC);

索引顺序很关键：先放过滤条件列（source、listing_type），再放排序列（created_at DESC）。这样数据库能快速定位到source=3且listing_type=4的所有行，且这些行已经按created_at倒序排列，直接取前100条即可，无需额外排序或过滤。

如果业务只需要返回部分列，还可以创建覆盖索引，把需要的列包含进去，避免回表查询：

CREATE INDEX idx_listings_source_type_created_at_covering ON listings USING btree (source, listing_type, created_at DESC) INCLUDE (id, title, ...);

只需把业务必需的列放到INCLUDE中，查询时直接从索引取数据，不用访问表的主数据块。

2. 避免SELECT *，只查询需要的列 #

当前查询用SELECT *会返回近100列数据，很多列可能是业务不需要的。减少返回列数能降低数据传输量、减少内存占用，同时更容易利用覆盖索引优化性能。

修改Rails查询代码，明确指定需要的列：

Listing.where(source: 3, listing_type: 4).order(created_at: :desc).select(:id, :title, :source, :listing_type, :created_at).limit(100)

3. 优化分页的COUNT(*)性能 #

will_paginate默认执行SELECT COUNT(*)获取总页数，大表下这个操作极慢，因为需要扫描全表统计行数。可以通过以下方式优化：

• 近似计数（适合不需要精确总页数的场景）：PostgreSQL可通过系统表快速获取近似行数，速度极快：

  SELECT reltuples::bigint AS approximate_count FROM pg_class WHERE relname = 'listings';
  

  在Rails中封装成方法，替换will_paginate的计数逻辑即可。
• 预计算行数（适合数据更新频率低的场景）：创建统计表，定时（用cron或Sidekiq任务）更新listings的总行数，或按source、listing_type等维度统计分组行数，查询时直接从统计表取数，避免实时计算。
• 禁用总页数计算（如果业务不需要）：若业务只需要“下一页”按钮，可修改will_paginate配置，跳过COUNT(*)查询，仅判断是否有下一页数据。

4. 更新表统计信息 #

PostgreSQL的查询优化器依赖表的统计信息生成最优执行计划，若统计信息过期，可能导致优化器选错索引。手动更新统计信息：

ANALYZE listings;

5. 考虑分区表（数据量极大时） #

若listings表数据量超过千万级，可考虑按created_at或source、listing_type进行分区，把大表拆成多个小表，提升查询和维护性能。

内容的提问来源于stack exchange，提问作者user984621