sql - PostgreSQL 索引性能问题

Question

我有一个 PostgreSQL 数据库，其中包含很多行 ~160,000,000。数据库基本上是一组网络日志。有一个名为 time 的列，它是一个时间戳。基本上对于每个唯一时间，网络上的每台机器都有一个日志。因此，有多行具有相同的时间戳。即

time    ip             value
2:00    192.168.1.1    5
2:00    192.168.1.2    4
2:00    192.168.1.3    5
3:00    192.168.1.1    3
3:00    192.168.1.2    5
3:00    192.168.1.3    2
etc     etc

现在我已经按时间为这个数据库创建了一个索引，因为这是我经常查询的相关字段。

i.e. select * from networklogs where time = '2:00' and value = 5

由于查询必须扫描整个数据库，因此在不使用索引的情况下，每个查询大约需要 2 分钟。我创建了索引，这样我的查询就会找到包含匹配时间的数据子集，然后根据此处的另一个值进行过滤，因此我希望查询会快很多。

但是现在查询似乎需要更长的时间...

这很奇怪，因为在 pgAdmin 中它解释了查询将使用时间索引，并且时间索引将数据从总数 160,000,000 条缩小到约 800,000 条，然后查询过滤器将此数据缩小到 1,700 条.这个解释需要 17 毫秒，但是如果我运行查询需要 3 分钟。

这应该比在 160,000,000 个项目上搜索两个匹配条件更快!

我无法弄清楚为什么它没有更快，我想知道即使 pgAdmin 将查询解释为使用时间索引，它是否在执行查询时实际使用它？

有没有人有什么想法或建议？

谢谢，詹姆斯

更新:我对以下命令运行了 EXPLAIN ANALYZE:

explain analyze select latitude,longitude from networklogs where value = 5 and time = '2:00'

结果是:

"Bitmap Heap Scan on networklogs  (cost=13178.17..1488620.94 rows=848 width=19) (actual time=3507.923..278760.509 rows=33 loops=1)"
"  Recheck Cond: (time = '2:00'::timestamp without time zone)"
"  Rows Removed by Index Recheck: 38302021"
"  Filter: (value = 5)"
"  Rows Removed by Filter: 882873"
"  ->  Bitmap Index Scan on timeindex  (cost=0.00..13177.95 rows=530111 width=0) (actual time=695.771..695.771 rows=882906 loops=1)"
"        Index Cond: (time = '2:00'::timestamp without time zone)"
"Total runtime: 278764.148 ms"

Answer 1

如果这些主要是您要针对其执行搜索的参数，我建议为它们创建一个多重索引。因为，在你的实际情况下，数据库有一个准时的索引，所以它能够知道哪些行符合这个条件，但是它必须从索引给定的所有不同地方获取它们，然后检查次要条件。

CREATE INDEX time_and_value ON networklogs (time, value);

使用这个索引，数据库将不必为了检查另一个条件而获取数据，它只会获取它知道已经匹配搜索条件的数据。顺序当然很重要。

我看到您正在使用纬度和经度数据，也许使用 point 类型会更好，这样您就可以使用所有 geometric operators Postgres 默认自带的。这些数据也可以被索引。