Rails+PostgreSQL下130万条数据索引查询耗时200ms是否正常？

Hey there! Let's dig into your query performance question. First, let's pull out the key detail from your explain output:

Bitmap Heap Scan on public.feeded_products (cost=875.39..74405.02 rows=36805 width=1085) (actual time=12.821..44.605 rows=37314 loops=1)
...
Execution time: 47.059 ms

首先：这个耗时是否正常？ #

从数据库层面看，47ms的执行时间 for 1.3M-row table retrieving 37k matching records is solid—your cc_index_on_fp index (category_connection_id + is_available) is being used correctly, and the Bitmap Index Scan + Heap Scan flow is efficient.

But the 150-300ms you see in Rails includes two parts:

1. The database's query execution time (~47ms)
2. Rails' overhead of instantiating 37k FeededProduct ActiveRecord objects from raw database rows. This Ruby object initialization cost is totally expected for large result sets, but there's plenty we can do to trim it down.

可优化的点 #

1. Cut down on unnecessary data & object instantiation

Your query uses SELECT *, pulling 1085 bytes per row (38MB total for 37k rows) and instantiating full FeededProduct objects. If your frontend only needs specific fields, query only what you need:

# Example: Fetch only fields your UI uses
@feed_products = FeededProduct.where(
  category_connection_id: @page.category_connections.pluck(:id),
  is_available: true
).select(:id, :name, :price, :product_url)

If you don't need ActiveRecord objects at all, skip instantiation entirely with pluck:

# Get raw data as an array, no object overhead
product_data = FeededProduct.where(
  category_connection_id: @page.category_connections.pluck(:id),
  is_available: true
).pluck(:id, :name, :price, :product_url)

This will drastically reduce Rails-side latency.

2. Tune PostgreSQL's memory settings

Your Bitmap Heap Scan uses 9428 Heap Blocks. PostgreSQL's work_mem parameter controls how much memory is allocated for Bitmap operations. If work_mem is too small, the Bitmap gets written to disk, slowing things down. Check your postgresql.conf and bump work_mem (e.g., from default 4MB to 16-32MB) to keep these operations in memory.

3. Cache results if data doesn't update frequently

If these product records don't change often, use Rails caching to avoid re-running the query:

cache_key = "feeded_products_page_#{@page.id}_available"
@feed_products = Rails.cache.fetch(cache_key, expires_in: 1.hour) do
  FeededProduct.where(
    category_connection_id: @page.category_connections.pluck(:id),
    is_available: true
  ).select(:id, :name, :price, :product_url)
end

4. Paginate results (if your workflow allows)

If you don't need to show all 37k products at once, paginate to reduce the result set size:

# Basic pagination with will_paginate/kaminari
@feed_products = FeededProduct.where(
  category_connection_id: @page.category_connections.pluck(:id),
  is_available: true
).select(:id, :name, :price, :product_url).page(params[:page]).per(20)

# More efficient keyset pagination (avoids offset performance issues)
last_id = params[:last_id] || 0
@feed_products = FeededProduct.where(
  category_connection_id: @page.category_connections.pluck(:id),
  is_available: true,
  id: last_id..
).select(:id, :name, :price, :product_url).limit(20)

5. Combine queries to eliminate extra DB round-trip

You're currently fetching category_connection_ids in a separate query. Merge it into one SQL statement to save a database call:

@feed_products = FeededProduct.joins(:category_connection)
  .where(category_connections: { page_id: @page.id }, is_available: true)
  .select(:id, :name, :price, :product_url)

总结 #

The database's 47ms execution time is normal and efficient—your index is working as intended. The Rails-side latency comes mostly from object instantiation and data transfer. Focus on trimming the data you fetch, caching, or paginating, and you'll see significant speedups.

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