时间戳

首页 > 解决方案 > 为什么没有。限制影响 Postgresql 处理选择查询？

sql - 为什么没有。限制影响 Postgresql 处理选择查询？

问题描述

我现在有一个名为user_profiles超过 24M 行的表。我需要检索所有数据并将它们索引到 Elasticsearch 中。

我编写了一个程序来使用和转换数据，以便它们可以索引到 ES 中。我使用 xorm 从 DB.Rows()中选择数据，这样内存就不会被炸毁。它曾经工作得很好。

我试图再次重新索引所有文档，但我发现从数据库加载数据的速度要慢得多。过去，当我使用 order by 运行全选查询时，它几乎立即返回第一行，但不是现在。

我检查了解释语句，发现如果我选择 LIMIT 为 13.05M 的文档，它将使用与我的查询顺序匹配的索引，但不会使用超过 13.06M 的索引。

我记得上次索引文档时大约是 10M

和LIMIT 13050000

- Plan: 
    Node Type: "Limit"
    Parallel Aware: false
    Startup Cost: 0.56
    Total Cost: 30928006.04
    Plan Rows: 13050000
    Plan Width: 592
    Plans: 
      - Node Type: "Index Scan"
        Parent Relationship: "Outer"
        Parallel Aware: false
        Scan Direction: "Forward"
        Index Name: "user_profiles_pkey"
        Relation Name: "user_profiles"
        Alias: "user_profiles"
        Startup Cost: 0.56
        Total Cost: 56959518.12
        Plan Rows: 24033936
        Plan Width: 592

与LIMIT 13060000：

- Plan: 
    Node Type: "Limit"
    Parallel Aware: false
    Startup Cost: 30605613.02
    Total Cost: 30638284.91
    Plan Rows: 13060000
    Plan Width: 592
    Plans: 
      - Node Type: "Sort"
        Parent Relationship: "Outer"
        Parallel Aware: false
        Startup Cost: 30605613.02
        Total Cost: 30665697.86
        Plan Rows: 24033936
        Plan Width: 592
        Sort Key: 
          - "user_id"
          - "system_name"
        Plans: 
          - Node Type: "Seq Scan"
            Parent Relationship: "Outer"
            Parallel Aware: false
            Relation Name: "user_profiles"
            Alias: "user_profiles"
            Startup Cost: 0.00
            Total Cost: 2357864.36
            Plan Rows: 24033936
            Plan Width: 592

我看到 AWS RDS 监控工具中有巨大的读写 IOPS。我认为 DB 正在尝试重新创建排序并忽略可以直接使用主索引的事实。我能做些什么？

这是解释查询：

EXPLAIN ( FORMAT YAML )
SELECT *
FROM "user_profiles"
ORDER BY "user_id", "system_name"
LIMIT 13050000;

这是表结构

CREATE TABLE user_profiles
(
    user_id     UUID        NOT NULL,
    system_name VARCHAR(50) NOT NULL,
    key_values  TEXT        NOT NULL
        CONSTRAINT user_profiles_pk PRIMARY KEY (user_id, system_name)
);

标签： sqlpostgresqlindexingdatabase-performance

解决方案

索引扫描是随机读取，通常比顺序读取更昂贵。但这几乎不取决于您的存储设备。例如对于 HDD，它的速度大约慢了 10 倍。Postgres 规划器使用预期的页面读取成本来选择更好的。当 seqscan 预计会更有效时，通过增加限制，您会遇到边界。

请考虑进行多次查询，而不是一次获取 13M 条记录。

推荐阅读