[pg15.2]一个亿级聊天记录表的调优

下面的 sql 查询非常慢。。。

select count(distinct "public"."tb_groupchat"."chat_id"), count("public"."tb_groupchat"."id"), count(distinct "public"."tb_groupchat"."sender_id") from "public"."tb_groupchat" where ("public"."tb_groupchat"."timestamp" >= $1 and "public"."tb_groupchat"."timestamp" < $2)

表结构：

  "id"                serial8 PRIMARY KEY NOT NULL,
  "chat_id"           int8                NOT NULL,
  "sender_id"         int8                NOT NULL,
  "timestamp"         int8                NOT NULL

explain 的结果

[
  {
    "Plan": {
      "Node Type": "Aggregate",
      "Strategy": "Plain",
      "Partial Mode": "Simple",
      "Parallel Aware": false,
      "Async Capable": false,
      "Startup Cost": 181904.15,
      "Total Cost": 181904.16,
      "Plan Rows": 1,
      "Plan Width": 24,
      "Output": ["count(DISTINCT chat_id)", "count(id)", "count(DISTINCT sender_id)"],
      "Plans": [
        {
          "Node Type": "Index Scan",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Async Capable": false,
          "Scan Direction": "Forward",
          "Index Name": "idx_timestamp",
          "Relation Name": "tb_groupchat",
          "Schema": "public",
          "Alias": "tb_groupchat",
          "Startup Cost": 0.43,
          "Total Cost": 172205.39,
          "Plan Rows": 1293168,
          "Plan Width": 24,
          "Output": ["id",  "chat_id", "sender_id", "content", "\"timestamp\""],
          "Index Cond": "((tb_groupchat.\"timestamp\" >= '1684944000000'::bigint) AND (tb_groupchat.\"timestamp\" < '1685030400000'::bigint))"
        }
      ]
    },
    "Query Identifier": 6892608323288585066,
    "JIT": {
      "Functions": 5,
      "Options": {
        "Inlining": false,
        "Optimization": false,
        "Expressions": true,
        "Deforming": true
      }
    }
  }
]

不知道各位同学有没有啥高招。。。