sql - 索引跳过扫描仿真以检索不同的产品 ID 和附加列的最小值/最大值

Question

这是我的表架构:

CREATE TABLE tickers (
    product_id TEXT NOT NULL,
    trade_id INT NOT NULL,
    sequence BIGINT NOT NULL,
    time TIMESTAMPTZ NOT NULL,
    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)
);

CREATE INDEX idx_tickers_product_id_time ON tickers (product_id, time);

我的应用程序在“ticker” channel 上订阅了 Coinbase Pro 的 websocket，并在收到消息时在 tickers 表中插入一行。

该表现在有超过 200 万行。

我学习了如何在 PostgreSQL 中使用索引跳过扫描模拟(参见:SELECT DISTINCT is slower than expected on my table in PostgreSQL)以便从该表中快速检索不同的 product_id 值，而不是使用较慢的 SELECT DISTINCT 方法。

我还想检索其他列的最小/最大值。这是我想出的。 2.25 行需要大约 2.9 毫秒。

有没有更好的方法来完成这个？

WITH product_ids AS (
    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
)
SELECT
    product_id,
    (SELECT (MAX(trade_id) - MIN(trade_id) + 1) FROM tickers WHERE product_id = product_ids.product_id) AS ticker_count,
    (SELECT MIN(time) FROM tickers WHERE product_id = product_ids.product_id) AS min_time,
    (SELECT MAX(time) FROM tickers WHERE product_id = product_ids.product_id) AS max_time
FROM product_ids
ORDER BY ticker_count DESC

Answer 1

查询

使用 (product_id, time) 上的现有索引，我们可以以一个的价格获得两个，即获取 product_id 和一次索引扫描中的最小时间:

WITH RECURSIVE product_ids AS (
   (   -- parentheses required
   SELECT product_id, time AS min_time
   FROM   tickers
   ORDER  BY 1, 2
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   product_ids p
   CROSS JOIN LATERAL (
      SELECT t.product_id, t.time
      FROM   tickers t
      WHERE  t.product_id > p.product_id
      ORDER  BY 1, 2
      LIMIT  1
      ) l
   )
SELECT product_id, min_time
    , (SELECT MAX(time) FROM tickers WHERE product_id = p.product_id) AS max_time
    , (SELECT MAX(trade_id) - MIN(trade_id) + 1 FROM tickers WHERE product_id = p.product_id) AS ticker_count
FROM   product_ids p
ORDER  BY ticker_count DESC;

此外，不需要第二个 CTE 包装器。

索引

目前您有两个索引:(product_id, trade_id) 上的 PK 索引，以及 (product_id, time) 上的另一个索引。您可以通过反转两者之一中的列顺序来优化它。喜欢:

PRIMARY KEY (trade_id, product_id)

逻辑上等效，但通常效率更高，因为它涵盖了更广泛的可能查询。请参阅(再次):

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

我们只需要 (product_id, time) 上的现有索引，因此不会直接影响此查询。