sql – 提高查询速度：简单的SELECT在大postgres表中

我在Postgres数据库的SELECT查询中遇到问题.

我有一个带有两个整数列的表：(int1,int2)

这张表有大约7000万行.

我需要在这种环境中进行两种简单的SELECT查询：

SELECT * FROM table WHERE int1=X;
SELECT * FROM table WHERE int2=X;

这两个选择在这7000万个中返回了大约10.000行.为了尽可能快地工作,我考虑使用两个HASH索引,每列都有一个.不幸的是,结果不是那么好：

QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on lec_sim  (cost=232.21..25054.38 rows=6565 width=36) (actual time=14.759..23339.545 rows=7871 loops=1)
   Recheck Cond: (lec2_id = 11782)
   ->  Bitmap Index Scan on lec_sim_lec2_hash_ind  (cost=0.00..230.56 rows=6565 width=0) (actual time=13.495..13.495 rows=7871 loops=1)
         Index Cond: (lec2_id = 11782)
 Total runtime: 23342.534 ms
(5 rows)

这是其中一个查询的EXPLAIN ANALYZE示例.大概需要23秒钟我的期望是在不到一秒钟内得到这些信息.

这些是Postgres db config的一些参数：

work_mem = 128MB
shared_buffers = 2GB
maintenance_work_mem = 512MB
fsync = off
synchronous_commit = off
effective_cache_size = 4GB

任何帮助,评论或想法将非常感激.

先谢谢你.

将我的意见提取到一个答案中：这里的索引查找非常快 – 所有的时间都用于检索实际的行. 23秒/ 7871行=每行2.9毫秒,这对于检索分散在磁盘子系统中的数据是合理的.寻求缓慢您可以a)将您的数据集合在RAM中,b)购买固态硬盘,或c)提前组织数据,以尽量减少寻找.

PostgreSQL 9.2有一个名为 index-only scans 的功能,允许它(通常)回答查询而不访问表.您可以将此与btree index属性组合,自动维护订单,使此查询更快.你提到int1,int2和两个浮点数：

CREATE INDEX sometable_int1_floats_key ON sometable (int1, float1, float2);
CREATE INDEX sometable_int2_floats_key ON sometable (int2, float1, float2);

SELECT float1,float2 FROM sometable WHERE int1=<value>; -- uses int1 index
SELECT float1,float2 FROM sometable WHERE int2=<value>; -- uses int2 index

还要注意,这并不奇怪地擦除磁盘寻找,它只是从查询时间移动到插入时间.由于您正在复制数据,因此还会占用存储空间.不过,这可能是你想要的权衡.