Postgres HashAggregate运行缓慢求助：3亿行大表查询优化建议

Hey there! Half a second on a 300M-row table isn’t a terrible baseline, but I totally get wanting to squeeze more performance out of it. While sheer data volume does play a role, there’s usually a lot more we can tweak beyond just adding individual indexes. Let’s walk through actionable steps to speed things up:

• Dive into your execution plan details
  First, confirm that your indexes are actually being used. Run EXPLAIN (or your database’s equivalent) on the query—look for signs like Using index (great, no need for bookmark lookups) vs Using where; Using index condition (might be doing partial scans or missing a covering index). If your query pulls columns not included in your indexes, the database has to do a "bookmark lookup" to fetch data from the main table, which kills speed on large datasets. Fix this by creating a covering index that includes all columns your query needs, e.g.:

  CREATE INDEX idx_ssn_email_phone_covering ON your_table(hashed_ssn, email, phone) INCLUDE (required_col1, required_col2);
  

• Evaluate index selectivity and order
  Not all indexes perform equally. If hashed_ssn has very low selectivity (e.g., millions of rows share the same value), a single index on it won’t help much. Check selectivity with:

  SELECT COUNT(DISTINCT hashed_ssn) / COUNT(*) AS ssn_selectivity FROM your_table;
  

  For multi-condition queries, order composite indexes by selectivity (most unique values first) to let the database narrow down results faster. For example, if email has higher selectivity than hashed_ssn, swap their order in the composite index.

• Tune database caching and hardware
  If your indexes aren’t fitting in memory, the database will hit disk constantly—way slower than in-memory access. Adjust your database’s buffer pool settings:

  • For MySQL/InnoDB: Increase innodb_buffer_pool_size (aim to fit all indexes + frequently accessed data in memory)
  • For PostgreSQL: Tune shared_buffers and work_mem
    Upgrading to SSD/NVMe storage will also drastically reduce random IO latency, a common bottleneck on large tables.

• Optimize the query itself
  Are you fetching more columns than you need? Trim the SELECT clause to only required fields. If your query uses OR conditions, try rewriting it with UNION ALL (if result sets are disjoint) to let the database use separate indexes for each condition. Avoid unnecessary subqueries or joins—simplify wherever possible.

• Consider table partitioning
  300M rows is a perfect candidate for partitioning. Split the table by a logical column:

  • Hash partitioning: Use hashed_ssn to spread rows across partitions, so queries only scan the partition containing the target value.
  • Range partitioning: If your data has a time component (e.g., created_at), partition by date to limit scans to relevant time ranges.
    Partitioning cuts down the amount of data the database needs to scan for each query.

• Maintain your indexes
  Over time, indexes on frequently updated tables get fragmented, slowing down scans. Schedule regular maintenance:

  • MySQL: Run OPTIMIZE TABLE (for InnoDB, this rebuilds indexes)
  • PostgreSQL: Use REINDEX to defragment indexes
    This keeps your indexes running at peak efficiency.

• Verify index type suitability
  If you’re doing pattern matching (e.g., email LIKE '%@example.com'), standard B-tree indexes won’t help—use a full-text index instead. For specialized data types, make sure you’re using the right index type (e.g., GIN/GIST indexes in PostgreSQL for JSON or spatial data).

While 300M rows is a large dataset, faster query times are definitely achievable with targeted optimizations. Start with the execution plan—it’ll tell you exactly where the bottlenecks are.

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