sql - SELECT DISTINCT 在我的 PostgreSQL 表上比预期的要慢

Question

这是我的表架构:

CREATE TABLE tickers (
    product_id TEXT NOT NULL,
    trade_id INT NOT NULL,
    sequence BIGINT NOT NULL,
    time TIMESTAMPTZ,
    price NUMERIC NOT NULL,
    side TEXT NOT NULL,
    last_size NUMERIC NOT NULL,
    best_bid NUMERIC NOT NULL,
    best_ask NUMERIC NOT NULL,
    PRIMARY KEY (product_id, trade_id)
);

我的应用程序在“ticker” channel 上订阅了 Coinbase Pro 的 websocket，并在收到消息时在行情表中插入一行。
该表现在有近 200 万行。
我假设运行 SELECT DISTINCT product_id FROM tickers会很快，但需要大约 500 到 600 毫秒。这是 EXPLAIN ANALYZE 的输出:

HashAggregate  (cost=47938.97..47939.38 rows=40 width=8) (actual time=583.105..583.110 rows=40 loops=1)
  Group Key: product_id
  ->  Seq Scan on tickers  (cost=0.00..42990.98 rows=1979198 width=8) (actual time=0.030..195.536 rows=1979243 loops=1)
Planning Time: 0.068 ms
Execution Time: 583.137 ms

如果我通过运行 SET enable_seqscan = FALSE 关闭 seq 扫描(不是我想要真正依赖的东西，只是为了测试目的)那么查询会快一点。在 400 到 500 毫秒之间。这是 EXPLAIN ANALYZE 的输出:

Unique  (cost=0.43..80722.61 rows=40 width=8) (actual time=0.020..480.339 rows=40 loops=1)
  ->  Index Only Scan using tickers_pkey on tickers  (cost=0.43..75772.49 rows=1980051 width=8) (actual time=0.019..344.113 rows=1980160 loops=1)
        Heap Fetches: 328693
Planning Time: 0.064 ms
Execution Time: 480.386 ms

表中只有 40 个唯一的产品 ID。我认为因为 product_id是复合主键的一部分，因此被编入索引， SELECT DISTINCT product_id FROM tickers会快得多。但事实证明，查询计划器默认使用 seq 扫描而不是索引，即使我强制它使用索引它仍然很慢(但比 seq 扫描快一点)。我意识到我可以创建另一个表来存储唯一的产品 ID 并查询它，但我更关心为什么我在股票行情表上的查询需要这么长时间。
编辑#1:
我尝试仅在 product_id 列( CREATE INDEX idx_tickers_product_id ON tickers (product_id) )上创建索引，除非我运行 SET enable_seqscan = FALSE，否则查询规划器仍会执行顺序扫描第一的。但它的性能比使用复合 PK 索引时略好(快 10 到 50 毫秒)。
编辑#2:
我尝试了 Erwin Brandstetter 的解决方案，它大大提高了速度。现在表中有 225 万行，执行只需要 0.75 毫秒!
编辑#3:
我想增加已接受的解决方案，以便检索代码计数 (max(trade_id) - min(trade_id) + 1) 以及每个产品 ID 的最短和最长时间。我为此创建了一个新问题:
How to use index skip emulation in PostgreSQL to retrieve distinct product IDs and also min/max for certain columns

Answer 1

虽然 Postgres 中还没有索引跳过扫描，请模拟它:

WITH RECURSIVE cte AS (
   (   -- parentheses required
   SELECT product_id
   FROM   tickers
   ORDER  BY 1
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT product_id
      FROM   tickers t
      WHERE  t.product_id > c.product_id  -- lateral reference
      ORDER  BY 1
      LIMIT  1
      ) l
   )
TABLE  cte;

索引位于 (product_id)并且表中只有 40 个唯一的产品 ID，这应该是快速的。用大写 F。 (product_id, trade_id)上的PK指数对它也有好处!
每个 product_id 只有很少的行(与您的数据分布相反)， DISTINCT/ DISTINCT ON会一样快或更快。
实现索引跳过扫描的工作正在进行中。
看:

Select first row in each GROUP BY group?

Optimize GROUP BY query to retrieve latest row per user

Is a composite index also good for queries on the first field?