提高查询速度：在大型postgres表中进行简单的SELECT

一尘不染

提高查询速度：在大型postgres表中进行简单的SELECT

sql

我在Postgres数据库上的SELECT查询中遇到关于速度的麻烦。

我有一个包含两个整数列作为键的表：（int1，int2）该表大约有7000万行。

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

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

这两个选择从这7,000万个中的每个返回大约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

任何帮助，评论或想法都将不胜感激。

先感谢您。

阅读 180

2021-03-17

共1个答案

一尘不染

将我的评论提取成一个答案：这里的索引查找非常快-一直都花在检索实际行上。23秒/
7871行=每行2.9毫秒，这对于检索散布在磁盘子系统中的数据是合理的。搜寻缓慢；您可以a）将数据集放入RAM，b）购买SSD，或c）提前整理数据以最大程度地减少寻道。

PostgreSQL
9.2具有称为仅索引扫描的功能，该功能允许它（通常）在不访问表的情况下回答查询。您可以将其与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

还要注意，这并不能神奇地擦除磁盘搜索，只是将它们从查询时间移到插入时间。由于您要复制数据，因此也会浪费您的存储空间。不过，这可能是您要权衡的。

2021-03-17