sql - 从大表中有效地选择不同的(a，b)

Question

我在 Postgres 9.6 数据库中有一个包含大约 5400 万行的表，我想找到所有不同的两列对(大约有 400 万个这样的值)。我对感兴趣的两列有一个索引:

create index ab_index on tbl (a, b)

获得此类对的最有效方法是什么？我试过:

select a,b
from tbl
where a>$previouslargesta
group by a,b
order by a,b
limit 1000

还有:

select distinct(a,b)
from tbl
where a>previouslargesta
order by a,b
limit 1000

还有这个递归查询:

with recursive t AS (
  select min(a) AS a from tbl
  union all
  select (select min(a) from tickets where a > t.a)
  FROM t)
select a FROM t

但都是懒散的。

有没有更快的方法来获取这些信息？

Answer 1

您的表有 5400 万行并且...

there are around 4 million such values

所有行的 7.4% 是一个很高的百分比，索引主要只能通过提供预先排序的数据来提供帮助，最好是在 index-only scan 中.对于较小的结果集有更复杂的技术(见下文)，并且有很多更快的分页方法，一次返回更少的行(见下文)但对于一般情况，一个普通的 DISTINCT 可能是最快的:

SELECT DISTINCT a, b  -- *no* parentheses
FROM   tbl;
-- ORDER  BY a, b      -- ORDER BY wasn't not mentioned as requirement ...

不要将它与 DISTINCT ON 混淆，后者需要括号。见:

• Select first row in each GROUP BY group?

(a, b) 上的 B 树索引 ab_index 已经是最好的索引。不过，必须对其进行整体扫描。挑战在于要有足够的 work_mem 来处理 RAM 中的所有内容。在标准设置下，它至少占用 1831 MB 的磁盘空间，通常会因膨胀而占用更多空间。如果负担得起，请在 session 中使用 2 GB(或更多)的 work_mem 设置运行查询。见:

• Configuration parameter work_mem in PostgreSQL on Linux

SET work_mem = '2 GB';
SELECT DISTINCT a, b ...
RESET work_mem;

只读表有帮助。否则您需要足够积极的 VACUUM 设置以允许仅索引扫描。然而，更多的 RAM 将有助于(通过适当的设置)保持索引兑现。

同时升级到 Postgres 的最新版本(撰写本文时为 11.3)。大数据已经有了很多改进。

分页

如果您想按照示例查询的指示添加分页，请紧急考虑ROW 值比较。见:

• Optimize query with OFFSET on large table
• SQL syntax term for 'WHERE (col1, col2) < (val1, val2)'

SELECT DISTINCT a, b
FROM   tbl
<b>WHERE  (a, b) > ($previous_a, $previous_b)   -- !!!</b>
ORDER  BY a, b
LIMIT  1000;

递归CTE

对于一般的大查询，这也可能会或可能不会更快。对于小子集，它变得多更有吸引力:

WITH RECURSIVE cte AS (
   (  -- parentheses required du to LIMIT 1
   SELECT a, b
   FROM   tbl
   <b>WHERE  (a, b) > ($previous_a, $previous_b)</b>   -- !!!
   ORDER  BY a, b
   LIMIT  1
   )
   UNION ALL
   SELECT x.a, x.b
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT t.a, t.b
      FROM   tbl t
      WHERE  (t.a, t.b) > (c.a, c.b) -- lateral reference
      ORDER  BY t.a, t.b
      LIMIT  1
      ) x
   )
TABLE cte
LIMIT 1000;

这可以完美地利用您的索引，并且应该尽可能快。

进一步阅读:

• Optimize GROUP BY query to retrieve latest row per user

对于重复使用和表上没有或很少的写入负载，请考虑 MATERIALIZED VIEW ，基于上述查询之一 - 以获得更快的读取性能。