PostgreSQL弃用索引致查询性能下降，如何优化自动选用索引？

Hey there, let's break down why PostgreSQL is opting for a sequential scan over your index, and how to get it to use the index naturally without relying on the hacky enable_seqscan = OFF setting.

First, let's recap your scenario: you have a table with ~740k rows, your query filters out rows where MMG is empty, groups by MMG and Gene, then counts. MySQL runs this in ~0.5s, PostgreSQL takes ~3s with a seq scan, but drops to ~0.5s when forcing index usage.

Why PostgreSQL Chooses Seq Scan Over Index #

Looking at your first execution plan:

Seq Scan on public.at_summary_typing (cost=0.00..36821.60 rows=182713 width=14) (actual time=0.010..224.658 rows=180657 loops=1)
  Output: "MMG", "Gene"
  Filter: ((at_summary_typing."MMG")::text <> ''::text)
  Rows Removed by Filter: 559071

PostgreSQL's query planner estimates that ~182k rows match your filter (which is ~24% of the total table). By default, PostgreSQL often prefers seq scans when a large portion of the table needs to be read—especially if using the index would require frequent "heap fetches" (going back to the main table to verify data visibility).

Your index-only scan with forced seqscan off shows 739728 heap fetches—that's way more than the number of matching rows. This means PostgreSQL can't trust the visibility map (VM) for your table, so it has to go back to the main table for almost every row to check if it's visible to your transaction. This makes the index scan look much more expensive to the planner than a seq scan.

Step-by-Step Fixes #

1. Update Table Statistics First #

PostgreSQL relies on accurate statistics to make query plan decisions. Run this to refresh them:

ANALYZE at_summary_typing;

This tells PostgreSQL the actual distribution of data in your table, including how many rows have non-empty MMG values. The planner might now realize the index is a better choice.

2. Clean Up the Table with VACUUM #

The high number of heap fetches points to an outdated visibility map. Run a vacuum to update it:

VACUUM ANALYZE at_summary_typing;

VACUUM updates the VM so PostgreSQL knows which pages have only visible data, allowing index-only scans to skip heap fetches entirely (or at least reduce them drastically). This will make the index scan much cheaper in the planner's eyes.

3. Create a Partial Index (Targeted to Your Filter) #

Instead of a generic composite index, create an index that only includes rows where MMG != ''. This reduces the size of the index and makes it even more efficient for your query:

CREATE INDEX mm_gene_non_empty_idx ON at_summary_typing (MMG, Gene) WHERE MMG != '';

This index is smaller, faster to scan, and directly matches your query's filter condition. The planner will be far more likely to pick this index because it only contains the rows you care about.

4. Verify the New Plan #

After applying these steps, run your EXPLAIN (analyse, buffers, verbose) again. You should see PostgreSQL using the partial index (or your original index) with far fewer heap fetches, and the query runtime should drop to match MySQL's performance.

Bonus: Check for NULL Values #

If your MMG column can have NULL values, make sure your filter explicitly excludes them too (though your current filter MMG != '' already excludes NULLs, since NULL != '' returns NULL and gets filtered out). But if you want to be explicit, you can adjust the query to:

SELECT "MMG", "Gene", COUNT(*) 
FROM at_summary_typing 
WHERE "MMG" IS NOT NULL AND "MMG" != '' 
GROUP BY "MMG", "Gene" 
ORDER BY COUNT(*);

This won't change the results, but it might help the planner be more confident in its row count estimates.

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