针对你这种2亿+数据量的分析型查询场景，索引效果不好太正常了——毕竟B树索引本来就不是为批量扫描设计的，分析查询大多是扫全表或者大范围数据，索引根本派不上用场。下面给你列几个实际生产中验证过的优化方向，按优先级排序，你可以一步步试：

• 内存参数拉满：分析型查询特别吃内存，PostgreSQL 9.5里重点调这几个参数：
  • work_mem：每个操作（排序、哈希连接等）可用的内存，建议设为64M-256M（比如32G物理内存的服务器设128M），避免频繁落磁盘做临时文件。
  • shared_buffers：一般设为物理内存的1/4，让数据库能缓存更多数据。
  • effective_cache_size：设为物理内存的2/3，告诉优化器系统有多少缓存可用，帮助生成更优的执行计划。
  • maintenance_work_mem：给建表、VACUUM这类维护操作留足内存，建议设为2G左右。
• 开启并行查询：PostgreSQL 9.5已经支持并行扫描了！把max_parallel_workers_per_gather设为4-8（根据CPU核心数来，比如8核服务器设4），再调低parallel_setup_cost（比如设100）和parallel_tuple_cost（比如设0.1），让优化器更愿意启用并行查询，利用多CPU核心加速扫描。

• 给大表做分区：2亿数据全堆在一张表，扫描起来肯定慢。按你查询常用的维度（比如时间、地区）做分区——9.5虽然没有10+版本的 declarative 分区，但可以用继承+触发器的方式实现：建一个父表，然后把数据按规则拆成多个子表（分区），查询时只会扫描符合条件的分区，直接把扫描量砍到原来的几分之一甚至几十分之一。
• 试试列存储：行存储适合OLTP，但分析型查询大多只用到少数列，列存储能大幅减少IO。你可以装cstore_fdw扩展，把大表转成列存储外部表，或者做冷热分离：近期的热数据用行存储，历史冷数据转成列存储压缩保存。
• 压缩数据：9.5没有原生表压缩，但可以通过cstore_fdw自带的压缩功能，或者用pg_repack工具在线重表时压缩数据，减少磁盘占用和IO开销。

• **绝对别用SELECT ***：只查你需要的列，能省大量的IO和内存，尤其是大表场景，效果立竿见影。
• 避免CTE的优化屏障：9.5里的CTE是优化屏障，优化器没法把谓词下推到CTE里，所以尽量把CTE改成子查询，比如把WITH t AS (...) SELECT * FROM t WHERE id > 100改成SELECT * FROM (...) t WHERE id > 100，让优化器能更好地过滤数据。
• 优化聚合逻辑：如果需要多维度聚合，用ROLLUP、CUBE代替多次GROUP BY，减少重复扫描。要是不需要精确的COUNT(DISTINCT)，可以用采样估算（比如SELECT COUNT(*) FROM your_table TABLESAMPLE SYSTEM(10)），速度能快好几倍。
• 别给索引列套函数：比如DATE(created_at) = '2024-01-01'这种写法会让索引失效，改成created_at BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59'，让优化器能用上分区或者索引。

• 换高速存储：分析型查询的瓶颈大多在IO，把机械硬盘换成SSD或者NVMe，IOPS能提升10倍以上，云环境的话直接选高IOPS的磁盘实例。
• 读写分离：把分析查询放到只读副本上，别占主库的资源。9.5支持流复制，建1-2个只读副本，专门跑分析任务，主库专心处理写入。
• 用MPP或者专门的分析引擎：如果数据量还在增长，单纯优化PostgreSQL可能不够，可以试试Citus（PostgreSQL的MPP扩展），把数据分布式存储，并行查询；或者把数据同步到ClickHouse、Greenplum这类专为分析设计的引擎，性能提升会非常明显。

• 定期VACUUM ANALYZE：大表的统计信息容易过时，优化器会生成烂计划。用pg_cron定时跑VACUUM ANALYZE your_table;，比如每天凌晨业务低峰期执行，保证统计信息准确。
• 清理死元组：如果有写入操作，死元组会越积越多，占用空间还影响扫描速度。可以用pg_repack在线重表（不锁表），或者在低峰期跑VACUUM FULL，清理冗余数据。

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