时间戳

首页 > 解决方案 > 访问每个标识符的最新行的正确方法？

sql - 访问每个标识符的最新行的正确方法？

问题描述

我core_message在 Postgres 中有一个表，数百万行看起来像这样（简化）：

┌────────────────┬──────────────────────────┬─────────────────┬───────────┬──────────────────────────────────────────┐
│    Colonne     │           Type           │ Collationnement │ NULL-able │                Par défaut                │
├────────────────┼──────────────────────────┼─────────────────┼───────────┼──────────────────────────────────────────┤
│ id             │ integer                  │                 │ not null  │ nextval('core_message_id_seq'::regclass) │
│ mmsi           │ integer                  │                 │ not null  │                                          │
│ time           │ timestamp with time zone │                 │ not null  │                                          │
│ point          │ geography(Point,4326)    │                 │           │                                          │
└────────────────┴──────────────────────────┴─────────────────┴───────────┴──────────────────────────────────────────┘
Index:
    "core_message_pkey" PRIMARY KEY, btree (id)
    "core_message_uniq_mmsi_time" UNIQUE CONSTRAINT, btree (mmsi, "time")
    "core_messag_mmsi_b36d69_idx" btree (mmsi, "time" DESC)
    "core_message_point_id" gist (point)

该mmsi列是用于识别世界上船舶的唯一标识符。我正在尝试获取每个mmsi.

我可以这样得到，例如：

SELECT a.* FROM core_message a
JOIN  (SELECT mmsi, max(time) AS time FROM core_message GROUP BY mmsi) b
       ON a.mmsi=b.mmsi and a.time=b.time;

但这太慢了，2秒+。

所以我的解决方案是创建一个不同的表，其中只包含表的最新行（最大 100K+ 行）core_message，称为LatestMessage.

每次必须将新行添加到core_message.

它工作得很好，我能够在几毫秒内访问该表。但是我很想知道是否有更好的方法可以仅使用一个表来实现这一目标并保持相同水平的数据访问性能。

标签： sqlpostgresqlindexingquery-optimizationgreatest-n-per-group

解决方案

这是本文中提到的查询的快速性能比较。

当前设置：

该表core_message有 10,904,283 行，其中有 60,740 行test_boats（或 60,740 个不同的 mmsi 中core_message）。

我正在使用 PostgreSQL 11.5

使用仅索引扫描进行查询：

1）使用DISTINCT ON：

SELECT DISTINCT ON (mmsi) mmsi 
FROM core_message;

2）RECURSIVE使用LATERAL：

WITH RECURSIVE cte AS (
   (
   SELECT mmsi
   FROM   core_message
   ORDER  BY mmsi
   LIMIT  1
   )
   UNION ALL
   SELECT m.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT mmsi
      FROM   core_message
      WHERE  mmsi > c.mmsi
      ORDER  BY mmsi
      LIMIT  1
      ) m
   )
TABLE cte;

3）使用一个额外的表LATERAL：

SELECT a.mmsi
FROM test_boats a
CROSS JOIN LATERAL(
    SELECT b.time
    FROM core_message b
    WHERE a.mmsi = b.mmsi
    ORDER BY b.time DESC
    LIMIT 1
) b;

查询不使用仅索引扫描：

4）DISTINCT ON使用mmsi,time DESC INDEX：

SELECT DISTINCT ON (mmsi) * 
FROM core_message 
ORDER BY mmsi, time desc;

5）DISTINCT ON向后使用mmsi,time UNIQUE CONSTRAINT：

SELECT DISTINCT ON (mmsi) * 
FROM core_message 
ORDER BY mmsi desc, time desc;

6)RECURSIVE与LATERAL和一起使用mmsi,time DESC INDEX：

WITH RECURSIVE cte AS (
   (
   SELECT *
   FROM   core_message
   ORDER  BY mmsi , time DESC 
   LIMIT  1
   )
   UNION ALL
   SELECT m.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT *
      FROM   core_message
      WHERE  mmsi > c.mmsi
      ORDER  BY mmsi , time DESC 
      LIMIT  1
      ) m
   )
TABLE cte;

7) 使用RECURSIVEwithLATERAL和 back mmsi,time UNIQUE CONSTRAINT：

WITH RECURSIVE cte AS (

   (

   SELECT *
   FROM   core_message
   ORDER  BY mmsi DESC , time DESC 
   LIMIT  1
   )
   UNION ALL
   SELECT m.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT *
      FROM   core_message
      WHERE  mmsi < c.mmsi
      ORDER  BY mmsi DESC , time DESC 
      LIMIT  1
      ) m
   )
TABLE cte;

8）使用一个额外的表LATERAL：

SELECT b.*
FROM test_boats a
CROSS JOIN LATERAL(
    SELECT b.*
    FROM core_message b
    WHERE a.mmsi = b.mmsi
    ORDER BY b.time DESC
    LIMIT 1
) b;

为最后一条消息使用专用表：

9）这是我最初的解决方案，使用只有最后一条消息的不同表。此表在新消息到达时填充，但也可以像这样创建：

CREATE TABLE core_shipinfos AS (
    WITH RECURSIVE cte AS (
       (
       SELECT *
       FROM   core_message
       ORDER  BY mmsi DESC , time DESC 
       LIMIT  1
       )
       UNION ALL
       SELECT m.*
       FROM   cte c
       CROSS  JOIN LATERAL (
          SELECT *
          FROM   core_message
          WHERE  mmsi < c.mmsi
          ORDER  BY mmsi DESC , time DESC 
          LIMIT  1
          ) m
       )
    TABLE cte);

那么获取最新消息的请求就这么简单：

SELECT * FROM core_shipinfos;

结果 ：

多个查询的平均值（快速查询约为 5）：

1) 9146 毫秒
2) 728 毫秒
3) 498 毫秒

4) 51488 毫秒
5) 54764 毫秒
6) 729 毫秒
7) 778 毫秒
8) 516 毫秒

9) 15 毫秒

结论：

我不会评论专用表解决方案，并将保留到最后。

附加表 ( test_boats) 解决方案绝对是这里的赢家，但该RECURSIVE解决方案也非常有效。

使用仅索引扫描和不使用它的扫描在性能上存在巨大差距，DISTINCT ON但对于其他有效查询而言，性能增益相当小。

这是有道理的，因为这些查询带来的主要改进是它们不需要遍历整个表，而只需要在与表大小 (10M+)相比明显更小core_message的唯一性子集上(60K+)mmsicore_message

作为附加说明，使用UNIQUE CONSTRAINTif I drop the mmsi,time DESC INDEX. 但是删除该索引当然会为我节省一些空间（该索引目前占用 328MB）

关于专用表解决方案：

存储在core_message表中的每条消息都携带位置信息（位置、速度、航向等）和船舶信息（名称、呼号、尺寸等），以及船舶标识符 (mmsi)。

为我实际尝试做的事情提供更多背景知识：我正在实现一个后端来存储船舶通过AIS 协议发出的消息。

因此，我得到的每一个独特的 mmsi，都是通过这个协议得到的。它不是一个预定义的列表。它不断添加新的 MMSI，直到我让世界上的每艘船都使用 AIS。

在这种情况下，将船舶信息作为最后收到的消息的专用表是有意义的。

我可以避免使用我们在RECURSIVE解决方案中看到的这种表，但是......专用表仍然比这个RECURSIVE解决方案快 50 倍。

该专用表实际上与该test_boat表类似，具有比mmsi字段更多的信息。事实上，mmsi只有一个字段的表或一个表的每个最后信息的core_message表都会给我的应用程序增加相同的复杂性。

最后，我想我会去这张专用桌子。它会给我无与伦比的速度，我仍然有可能使用这个LATERAL技巧core_message，这会给我更多的灵活性。

推荐阅读