索引列上极慢的distinct查询

Question

索引列上极慢的distinct查询

sqlpostgresqlindexingquery-optimizationdistinct

4

在一个拥有三亿条记录的大表中，我正在查询MY_DATE的不同值。这个列有约400个不同的值，并且已经建立了索引。这个数据库是Postgres。

Select distinct  MY_DATE from MY_TABLE;

该查询运行时间为22分钟。

在我的Oracle数据库中，具有完全相同数据集和相同索引定义的相同查询只需11秒即可运行。

查询计划显示该查询正在使用索引：

EXPLAIN Select distinct  MY_DATE from MY_TABLE LIMIT 200;

提供：

QUERY PLAN
Limit  (cost=0.57..7171644.14 rows=200 width=8)
  ->  Unique  (cost=0.57..15419034.24 rows=430 width=8)
        ->  Index Only Scan using idx_obsdate on my_table  (cost=0.57..14672064.14 rows=298788038 width=8)

当我限制结果时，查询可以变得更快。例如：

Select distinct  MY_DATE from MY_TABLE LIMIT 5;

在子秒级别运行。

但是：

Select distinct  MY_DATE from MY_TABLE LIMIT 50;

查询已经需要几分钟的时间了。在使用LIMIT子句时，时间似乎呈指数增长。

我期望Postgres查询能够在几秒钟内运行，就像我的OracleDB一样。即使对于大表格，索引扫描也需要20分钟的时间，这似乎远远偏离了正常值。

请问有什么原因导致这个问题，并且我可以做些什么来解决它？

- tomanizer

1

@GordonLinoff：Postgres具有索引扫描功能。对于SELECT查询，它不需要行锁定。 - Erwin Brandstetter

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

有3亿行数据，其中大约有400个不同的值在一个被索引的列中。

对于这个问题，有更快的技术。模拟松散索引扫描（也称跳跃扫描），并假设my_date已定义为NOT NULL（或者我们可以忽略NULL值）：

WITH RECURSIVE cte AS (
   SELECT min(my_date) AS my_date
   FROM   my_table

   UNION ALL
   SELECT (SELECT my_date
           FROM   my_table 
           WHERE  my_date > cte.my_date
           ORDER  BY my_date
           LIMIT  1)
   FROM   cte
   WHERE  my_date IS NOT NULL
   )
TABLE  cte;

相关:

优化GROUP BY查询以检索每个用户的最新记录

使用您提到的索引，它应该在毫秒内完成。

Oracle DB ... 11秒。

因为Oracle有原生的索引跳过扫描而Postgres没有。在Postgres 12中有持续努力实现类似功能。

目前（Postgres 11），虽然索引被很好地使用，即使在仅索引扫描中，Postgres也无法跳过并且必须按顺序读取索引元组。如果没有LIMIT，则必须扫描完整个索引。因此我们在您的EXPLAIN输出中看到：

Index Only Scan ... rows=298788038

建议的新查询只需读取400个索引元组（每个不同值一个）。大的区别。

使用 LIMIT（并且没有 ORDER BY！）像您测试的那样，Postgres 在检索足够的行后立即停止。增加限制具有线性效应。但是，如果每个不同值的行数可能会变化，则增加的成本也会随之变化。