PostgreSQL Product表关键词查询性能过慢问题求助

Alright, let's tackle that painfully slow keyword query on your Heroku PostgreSQL setup—2.8 million rows shouldn't take 50 seconds, even with your 8GB/250GB config. Let's break down actionable steps to diagnose and fix this:

First things first: you need to see exactly what PostgreSQL is doing when you run that keyword search. Even if you think your indexes are "reasonable," they might not be suited for your query pattern.

Run your keyword query with this prefix to get a detailed execution plan:

EXPLAIN ANALYZE SELECT * FROM product WHERE [your keyword condition];

Look for these red flags in the output:

• A Seq Scan (full table scan) instead of an Index Scan—this means your indexes aren't being used at all, even if you created them.
• Sky-high rows or cost values, especially if they're way larger than the actual number of rows you expect to get back.
• Expensive Sort operations that spill to disk (marked as Sort Method: External Merge Disk:)—this happens if you're ordering results without an appropriate index.

If your keyword search uses something like LIKE '%your-term%' (wildcards at the start/end), standard B-tree indexes won't help—they only work for prefix matches (e.g., LIKE 'term%'). Here are better index options tailored to keyword searches:

Option A: Full-Text Search with GIN Index #

PostgreSQL's built-in full-text search is made for this kind of work. Since you're on PG 9.5 (which doesn't support generated columns natively), use a trigger to maintain a search vector:

1. Add a tsvector column to store processed text:

   ALTER TABLE product ADD COLUMN search_vector tsvector;
   

2. Create a trigger to update this column whenever your text fields change:

   CREATE TRIGGER tsvector_update_trigger
   BEFORE INSERT OR UPDATE ON product
   FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(
     search_vector, 'pg_catalog.english', name, description); -- Replace with your actual text columns
   

3. Build a GIN index on the vector (GIN is optimized for full-text search):

   CREATE INDEX idx_product_search_vector ON product USING GIN(search_vector);
   

4. Query using full-text operators instead of LIKE:

   SELECT * FROM product WHERE search_vector @@ to_tsquery('english', 'your-keyword');
   

Option B: Trigram Indexes for Partial Matches #

If you need exact substring matches (not just full-word searches), use the pg_trgm extension:

1. Enable the extension first:

   CREATE EXTENSION IF NOT EXISTS pg_trgm;
   

2. Create a GIN or GIST index on your text column (GIN is faster for lookups, GIST is smaller):

   CREATE INDEX idx_product_name_trgm ON product USING GIN(name gin_trgm_ops);
   

3. Now your LIKE '%your-term%' queries will use this index instead of scanning the whole table.

Bonus: Partial Indexes #

If your keyword search is always combined with another filter (e.g., active = true), a partial index can drastically reduce index size and speed up queries:

CREATE INDEX idx_product_active_search ON product USING GIN(search_vector) WHERE active = true;

Even with great indexes, a poorly written query can drag things down:

• Ditch SELECT *: Only fetch the columns your app actually needs. Pulling unnecessary large text/binary columns wastes memory and disk I/O.
• Limit Results: If you don't need every matching row at once, add LIMIT 50 (or whatever makes sense for your UI) to stop the query early.
• Avoid Unnecessary Sorting: If you're using ORDER BY without an index on the sorted column, PostgreSQL will do an expensive in-memory or disk sort. Add an index that combines your search index with the sort column (e.g., include the sort column in the index if possible).

Since you're on Heroku, there are platform-specific tweaks to squeeze out more performance:

• Check Connection Usage: You mentioned 1000 max connections—make sure you're not hitting this limit, which causes query queuing. Run SELECT count(*) FROM pg_stat_activity; to see active connections.
• Use PGBouncer: Heroku recommends PGBouncer for connection pooling, which reduces overhead from idle connections and lets you make better use of your database's resources.
• Upgrade PostgreSQL: PG 9.5 is end-of-life (EOL) since 2021—newer versions (like 14+) have massive improvements to query planning, full-text search, and performance. Heroku has tools to make upgrading manageable, and it's a critical long-term fix.
• Verify Cache Hit Ratio: Run this query to check how much data is being served from RAM vs. disk:

  SELECT sum(heap_blks_hit) / sum(heap_blks_read + heap_blks_hit) AS cache_hit_ratio
  FROM pg_stat_user_tables WHERE relname = 'product';
  

  A ratio below 0.9 means your database is hitting disk too often. With 8GB RAM, Heroku should have shared_buffers tuned appropriately, but you can check with SHOW shared_buffers; if you're curious.

After making changes, re-run EXPLAIN ANALYZE to confirm indexes are being used and execution time is down. Keep an eye on Heroku's database metrics (via the dashboard) for query latency, disk I/O, and memory usage to ensure the fixes hold as your data grows.

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