PostgreSQL简单Prisma查询在服务器运行缓慢问题排查求助

我在Node后端使用Prisma执行两个简单的count查询：

const excavationSiteCount: number = (await client.excavation_sites.count({where: {owner: user}}));
const artifactCount: number = (await client.artifacts.count({where: {owner: user}}));

本地测试时查询速度很快，但在VPS服务器上执行耗时接近1秒。我的VPS配置为：Intel Core Processor (Broadwell, no TSX, IBRS) 2.20 GHz、12GB内存、64位系统。我已调整shared_buffers、work_mem和effective_cache_size参数，但问题未解决。想咨询该问题是否与Prisma相关，或其他可能的原因。

auto_explain输出 #

2025-04-24 15:47:21 CEST LOG:  duration: 0.069 ms  plan:
    Query Text: select version()
    Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.040..0.041 rows=1 loops=1)
      Output: version()
2025-04-24 15:47:22 CEST LOG:  duration: 0.221 ms  plan:
    Query Text: select current_database() as a, current_schemas(false) as b
    Result  (cost=0.00..0.01 rows=1 width=96) (actual time=0.177..0.178 rows=1 loops=1)
      Output: current_database(), current_schemas(false)
      Buffers: shared hit=3
2025-04-24 15:47:22 CEST LOG:  duration: 0.217 ms  plan:
    Query Text: SELECT t.*
    FROM public.users t
    LIMIT 501
    Limit  (cost=0.00..1.02 rows=2 width=298) (actual time=0.206..0.208 rows=2 loops=1)
      Output: id, firstname, lastname
      Buffers: shared read=1
      ->  Seq Scan on public.users t  (cost=0.00..1.02 rows=2 width=298) (actual time=0.204..0.205 rows=2 loops=1)
            Output: id, firstname, lastname
            Buffers: shared read=1
2025-04-24 15:47:22 CEST LOG:  duration: 0.572 ms  plan:
    Query Text: SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname, a.attnotnull OR (t.typtype = 'd' AND t.typnotnull), a.attidentity != '' OR pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid = t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 16483 AS oid , 1 AS attnum UNION ALL SELECT 16483, 2 UNION ALL SELECT 16483, 3) vals ON (c.oid = vals.oid AND a.attnum = vals.attnum) 
    Nested Loop Left Join  (cost=0.91..24.15 rows=1 width=200) (actual time=0.238..0.534 rows=3 loops=1)
      Output: c.oid, a.attnum, a.attname, c.relname, n.nspname, (a.attnotnull OR ((t.typtype = 'd'::"char") AND t.typnotnull)), ((a.attidentity <> ''::"char") OR (pg_get_expr(d.adbin, d.adrelid) ~~ '%nextval(%'::text))
      Inner Unique: true
      Buffers: shared hit=41
      ->  Nested Loop  (cost=0.77..23.97 rows=1 width=206) (actual time=0.199..0.489 rows=3 loops=1)
            Output: c.oid, c.relname, n.nspname, a.attnum, a.attname, a.attnotnull, a.attidentity, a.attrelid, t.typtype, t.typnotnull
            Inner Unique: true
            Buffers: shared hit=38
            ->  Nested Loop  (cost=0.49..23.66 rows=1 width=208) (actual time=0.177..0.458 rows=3 loops=1)
                  Output: c.oid, c.relname, n.nspname, a.attnum, a.attname, a.attnotnull, a.attidentity, a.attrelid, a.atttypid
                  Inner Unique: true
                  Buffers: shared hit=29
                  ->  Nested Loop  (cost=0.36..23.46 rows=1 width=148) (actual time=0.162..0.436 rows=3 loops=1)
                        Output: c.oid, c.relname, c.relnamespace, a.attnum, a.attname, a.attnotnull, a.attidentity, a.attrelid, a.atttypid
                        Inner Unique: true
                        Join Filter: (a.attnum = (1))
                        Rows Removed by Join Filter: 21
                        Buffers: shared hit=23
                        ->  Hash Join  (cost=0.08..19.82 rows=3 width=80) (actual time=0.132..0.387 rows=3 loops=1)
                              Output: c.oid, c.relname, c.relnamespace, (16483), (1)
                              Hash Cond: (c.oid = ((16483))::oid)
                              Buffers: shared hit=14
                              ->  Seq Scan on pg_catalog.pg_class c  (cost=0.00..18.15 rows=415 width=72) (actual time=0.031..0.157 rows=480 loops=1)
                                    Output: c.oid, c.relname, c.relnamespace, c.reltype, c.reloftype, c.relowner, c.relam, c.relfilenode, c.reltablespace, c.relpages, c.reltuples, c.relallvisible, c.reltoastrelid, c.relhasindex, c.relisshared, c.relpersistence, c.relkind, c.relnatts, c.relchecks, c.relhasrules, c.relhastriggers, c.relhassubclass, c.relrowsecurity, c.relforcerowsecurity, c.relispopulated, c.relreplident, c.relispartition, c.relrewrite, c.relfrozenxid, c.relminmxid, c.relacl, c.reloptions, c.relpartbound
                                    Buffers: shared hit=14
                              ->  Hash  (cost=0.04..0.04 rows=3 width=8) (actual time=0.035..0.036 rows=3 loops=1)
                                    Output: (16483), (1)
                                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                    ->  Append  (cost=0.00..0.04 rows=3 width=8) (actual time=0.011..0.014 rows=3 loops=1)
                                          ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
                                                Output: 16483, 1
                                          ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)
                                                Output: 16483, 2
                                          ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)
                                                Output: 16483, 3
                        ->  Index Scan using pg_attribute_relid_attnum_index on pg_catalog.pg_attribute a  (cost=0.28..1.11 rows=8 width=76) (actual time=0.009..0.012 rows=8 loops=3)
                              Output: a.attrelid, a.attname, a.atttypid, a.attlen, a.attnum, a.attcacheoff, a.atttypmod, a.attndims, a.attbyval, a.attalign, a.attstorage, a.attcompression, a.attnotnull, a.atthasdef, a.atthasmissing, a.attidentity, a.attgenerated, a.attisdropped, a.attislocal, a.attinhcount, a.attcollation, a.attstattarget, a.attacl, a.attoptions, a.attfdwoptions, a.attmissingval
                              Index Cond: (a.attrelid = c.oid)
                              Buffers: shared hit=9
                  ->  Index Scan using pg_namespace_oid_index on pg_catalog.pg_namespace n  (cost=0.13..0.18 rows=1 width=68) (actual time=0.006..0.006 rows=1 loops=3)
                        Output: n.oid, n.nspname, n.nspowner, n.nspacl
                        Index Cond: (n.oid = c.relnamespace)
                        Buffers: shared hit=6
            ->  Index Scan using pg_type_oid_index on pg_catalog.pg_type t  (cost=0.28..0.32 rows=1 width=6) (actual time=0.008..0.008 rows=1 loops=3)
                  Output: t.oid, t.typname, t.typnamespace, t.typowner, t.typlen, t.typbyval, t.typtype, t.typcategory, t.typispreferred, t.typisdefined, t.typdelim, t.typrelid, t.typsubscript, t.typelem, t.typarray, t.typinput, t.typoutput, t.typreceive, t.typsend, t.typmodin, t.typmodout, t.typanalyze, t.typalign, t.typstorage, t.typnotnull, t.typbasetype, t.typtypmod, t.typndims, t.typcollation, t.typdefaultbin, t.typdefault, t.typacl
                  Index Cond: (t.oid = a.atttypid)
                  Buffers: shared hit=9
      ->  Index Scan using pg_attrdef_adrelid_adnum_index on pg_catalog.pg_attrdef d  (cost=0.14..0.17 rows=1 width=38) (actual time=0.013..0.013 rows=0 loops=3)
            Output: d.oid, d.adrelid, d.adnum, d.adbin
            Index Cond: ((d.adrelid = a.attrelid) AND (d.adnum = a.attnum))
            Buffers: shared hit=3

users表DDL #

create table org_3077c8c7.users
(
    id        varchar(32) not null
        primary key,
    firstname varchar(45) not null,
    lastname  varchar(45) not null
);

DataGrip响应时间截图 #

排查方向分析 #

• Prisma相关检查

  • 确认连接池配置：查看Prisma的connection_limit参数是否合理，避免每次查询新建连接产生开销。
  • 开启查询日志：在Prisma Client配置中添加log: ["query"]，查看实际生成的SQL语句，对比本地与VPS的执行计划差异。
  • 升级Prisma版本：旧版本可能存在性能bug，尝试切换到最新稳定版测试。

• 数据库层面优化

  • 添加索引核心字段：excavation_sites和artifacts表的owner字段如果没有索引，会触发全表扫描。执行以下语句创建索引后重新测试：

    CREATE INDEX idx_excavation_sites_owner ON excavation_sites(owner);
    CREATE INDEX idx_artifacts_owner ON artifacts(owner);
    

  • 检查缓存与扫描统计：通过pg_stat_user_tables查看seq_scan（全表扫描次数）和idx_scan（索引扫描次数），确认查询是否用到了索引；同时观察多次查询后耗时是否下降，判断是否是缓存未预热导致。
  • 排查系统资源瓶颈：用top/htop查看查询时的CPU使用率，确认是否有其他进程抢占资源；检查VPS磁盘类型，机械硬盘的随机读写性能远低于SSD，会影响查询速度。

• 网络与本地验证

  • 如果Node服务和数据库是远程连接，测试VPS到数据库的网络延迟；直接在VPS上用psql执行相同的count查询，耗时正常则排除数据库本身问题，聚焦Node/Prisma或网络环节。

内容的提问来源于stack exchange，提问作者user17145524