主页 / dba / 问题 / 302234
Accepted
ConanTheGerbil
Asked: 2021-11-07 07:17:17 +0800 CST

不同 Postgres 数据库上相同查询的不同执行时间

  • 772

我有两个相同的数据库，开发和生活。

我对他们两个都运行这个查询....

select count(*) FROM DeviceB where deviceID not in (SELECT distinct deviceId FROM Device)

在开发盒上查询大约需要 100 毫秒，在现场大约需要一个小时。

这两个表都有一个名为 的主键字段deviceId，两个数据库具有相同的结构，并且都具有非常相似的数据（没有一个表包含超过百万行）。两个数据库都定期清理/分析，都在过去 12 小时内完成。

每个数据库都在不同的计算机上，每个数据库的硬件都不同，但在 99% 的时间里，live box 的速度会慢 10% 左右。

据我所知，两个数据库的配置相同。

我的猜测是每个数据库上的数据分布存在一些细微的差异，这会影响解释计划的选择。

我的问题是：

  • 反正有没有强制数据库使用特定的索引？
  • 是否有不同形式的 SQL 查询会产生相同的结果但速度更快？

解释计划开发- 不到一秒钟：

Aggregate  (cost=7806.68..7806.69 rows=1 width=8)
  ->  Seq Scan on deviceb  (cost=4624.86..7702.06 rows=41848 width=0)
        Filter: (NOT (hashed SubPlan 1))
        SubPlan 1
          ->  Unique  (cost=0.29..4404.59 rows=88108 width=7)
                ->  Index Only Scan using device_pkey on device  (cost=0.29..4184.32 rows=88108 width=7)

现场解释计划- 大约需要一个小时：

Finalize Aggregate  (cost=747368287.67..747368287.68 rows=1 width=8)
  ->  Gather  (cost=747368287.46..747368287.67 rows=2 width=8)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=747367287.46..747367287.47 rows=1 width=8)
              ->  Parallel Seq Scan on deviceb  (cost=0.42..747367217.98 rows=27789 width=0)
                    Filter: (NOT (SubPlan 1))
                    SubPlan 1
                      ->  Materialize  (cost=0.42..26001.96 rows=156913 width=7)
                            ->  Unique  (cost=0.42..22822.40 rows=156913 width=7)
                                  ->  Index Only Scan using device_pkey on device  (cost=0.42..21930.12 rows=156913 width=7)

我已经使用 pgAdmin4 比较了表的开发/实时版本 - 只需突出显示对象并选择每个表的属性及其索引和约束。

另外 - 我的测试（在两个数据库上）是在没有连接其他用户的情况下完成的。

DDL，根据要求：

CREATE TABLE public.device (
    deviceid character(6) COLLATE pg_catalog."default" NOT NULL,
    recentfixtime timestamp without time zone,
    newfixes integer DEFAULT 0,
    lastfixid bigint DEFAULT 0,
    CONSTRAINT device_pkey PRIMARY KEY (deviceid)
        USING INDEX TABLESPACE fastspace
);


CREATE TABLE public.deviceb  (
    deviceid character(6) COLLATE pg_catalog."default" NOT NULL,
    flightid integer DEFAULT 0,
    lastfirstfixtime timestamp without time zone,
    lastprocfixtime timestamp without time zone,
    lastprocfly boolean,
    priority integer DEFAULT 0,
    lastlandtime timestamp without time zone,
    CONSTRAINT deviceb_pkey PRIMARY KEY (deviceid)
        USING INDEX TABLESPACE fastspace
)

更新 - 已解决，感谢 Jjanes，建议的版本在两个数据库上都非常快。

select count(*) FROM DeviceB where NOT EXISTS (
    SELECT 1 from Device where Device.deviceID=DeviceB.deviceID
)

我承认我仍然不确定根本原因是什么，但为了完整起见，我将尝试回答一些进一步的问题。自动分析仍在进行中（pgAdmin/statistics 显示了今天早上早些时候的最后一个自动分析日期。两个表（实时）的行数都少于 200,000，开发上的两个表都少于 100,000。开发/实时盒子上的硬件不同（最大区别是 dev 有 32G 内存，live 只有 16G）。可能最显着的区别是版本，dev 是 13.1，live 仍然是 10.4

postgresql performance

2 个回答

  1. Best Answer

    我已经问了一系列问题来探究根本原因。但不管根本原因的具体细节如何，您都可以通过使用 NOT EXISTS 而不是 NOT IN 来将自己与问题隔离开来。

    select count(*) FROM DeviceB where NOT EXISTS (
    SELECT 1 from Device where Device.deviceID=DeviceB.deviceID
)
    • 1

  2. 反正有没有强制数据库使用特定的索引？

    不，不是直接的。您可以“禁用”其他访问方法以使 Postgres 选择索引扫描或位图索引扫描。看：

    但是你不能直接指示它使用某个索引。无论如何，这不是问题所在。

    是否有不同形式的 SQL 查询会产生相同的结果但速度更快？

    是的。掉落DISTINCT：

    SELECT count(*) FROM deviceb
WHERE deviceid NOT IN (SELECT deviceid FROM device);

    试图从大集合中删除重复项是昂贵的。因为device.deviceid是PRIMARY KEY，所以不能有重复。

    如果可能涉及 NULL 值或重复项，我们将查看其他查询样式，但对于两个主键，查询应该是最佳的。

    并行顺序扫描deviceb是问题所在，正如我们在这里看到的：

    deviceb 上的并行 Seq 扫描（成本=0.42.. 747367217.98行=27789 宽度=0）

    不涉及索引。那应该不会那么贵。我也想知道为什么我们看不到这样的一行：

    '过滤器删除的行：123456'

    那是什么版本的 Postgres？您是如何准确生成查询计划的？

    要么是表有问题：

    • 比你想象的多很多？检查：
    SELECT count(*) FROM deviceb;
    • 极端的表和索引膨胀？检查：
    SELECT pg_size_pretty(pg_table_size('deviceb'))
     , pg_size_pretty(pg_table_size('deviceb_pkey'));

SELECT * FROM pg_stat_user_tables WHERE relname = 'deviceb';

    用另一个修复VACUUM ANALYZE deviceb;，也许即使VACUUM FULL ANALYZE deviceb;没有其他用户连接（？）

    • 独占锁？

    那不可能。你说：

    没有其他用户连接。

    • 硬件问题？

    或者并行性有问题。尝试禁用它（用于调试！）并重新测试：

    SET max_parallel_workers_per_gather = 0;

    还：

    两个数据库都定期清理/分析，都在过去 12 小时内完成。

    听起来您正在运行手动VACUUM/ ANALYZE。你禁用了autovacuum吗？（通常情况下，您不应该这样做。）

    虽然必须研究令人痛苦的缓慢（并行）顺序扫描，但我想知道为什么我们一开始就没有看到仅索引扫描diviceb。有了新鲜VACUUM的桌子和一个TABLESPACE名字 fastspace，我期望同样多。您是否知道可以设置一些关键参数TABLESPACE？最重要的是seq_page_cost和 random_page_cost。喜欢：

    ALTER TABLESPACE fastspace SET (seq_page_cost = 0.5, random_page_cost = 1);

    适应您的“fastspace”的特点。

    • 0

相关问题