如何提高PostgreSQL数据库中大量数据的查询效率？

Question

我有一个包含12亿行的PostgreSQL数据库，尝试制作一个可以每次查询一百万行并提供查询更大间隔选项的应用程序。
起初我只是轻松地查询一个包含100万到1000万行的数据库；
现在我使用OFFSET查询一个大型数据库时，ResultSet需要很长时间才能生成。

   // ...
   stmt.setFetchSize(100000);
   ResultSet rs = stmt.executeQuery("SELECT mmsi, report_timestamp, position_geom, ST_X(position_geom) AS Long, "
                        + "ST_Y(position_geom) AS Lat FROM reports4 WHERE position_geom IS NOT NULL ORDER by report_timestamp ASC LIMIT "
                        + limit + " OFFSET " + set); 

所以ORDER BY可能会影响我的执行时间，但是有序的信息可以使后续处理更加方便。是否有更有效率的方法来查询区间内的行？

- guy_sensei

---

你的表格中有哪些索引？ - user3707125

如果您的表以 report_timestamp 为索引，那么这几乎不需要任何操作。我认为还有部分索引可以精确匹配您的 where 子句。 - Marko Topolnik

https://wiki.postgresql.org/wiki/Slow_Query_Questions 和 http://stackoverflow.com/tags/postgresql-performance/info - user330315

Answer 1

对于这个查询：

SELECT mmsi, report_timestamp, position_geom, ST_X(position_geom) AS Long, "
                        + "ST_Y(position_geom) AS Lat
FROM reports4
WHERE position_geom IS NOT NULL
ORDER by report_timestamp ASC;

您应该能够在表达式上使用索引：

CREATE INDEX idx_reports4_position_ts ON reports4((position_geom IS NOT NULL), report_timestamp)

直接使用此索引进行查询。

- Gordon Linoff

---

非常感谢@Gordon Linoff，我之前不知道INDEX函数，我会去了解一下 :) - guy_sensei

这将索引分成“为null”和“不为null”的情况；使用部分索引可能会获得更好的性能。 - Marko Topolnik

@guy_sensei：哇，12亿行数据而你从未需要创建索引？你一定有非常强大的硬件... - user330315

Answer 2

您可以使用建立在数据库子集上的部分索引。

CREATE INDEX idx_reports4 ON reports4(position_geom, report_timestamp) where position_geom IS NOT NULL;

这将大大提高性能，因为您只需索引所需的数据库部分。

- Constantine