sql - 提高查询速度 : simple SELECT in big postgres table

Question

我在 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 数据库配置的一些参数:

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

如有任何帮助、评论或想法，我们将不胜感激。

提前谢谢你。

Answer 1

将我的评论提取为答案:此处的索引查找速度非常快——所有时间都花在了检索实际行上。 23 秒/7871 行 = 每行 2.9 毫秒，这对于检索分散在磁盘子系统中的数据来说是合理的。寻道很慢；您可以 a) 将您的数据集放入 RAM，b) 购买 SSD，或 c) 提前组织您的数据以尽量减少查找。

PostgreSQL 9.2 有一个名为 index-only scans 的功能这允许它(通常)在不访问表的情况下回答查询。您可以将其与自动维护顺序的 btree 索引属性相结合，以加快此查询速度。您提到了 int1、int2 和两个 float :

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

还要注意，这并没有神奇地删除磁盘寻道，它只是将它们从查询时间移到插入时间。它还会花费您的存储空间，因为您正在复制数据。不过，这可能是您想要的权衡。