PostgreSQL大数量查询性能优化求助：50万行查询耗时超1秒

API 探路者

2026-8-12

PostgreSQL 大数据量查询优化方案需求

本人首次在项目中使用PostgreSQL，属于新手阶段，首次处理大量数据。当前查询50万行数据耗时约1.5秒，希望将耗时缩短一半以上。PostgreSQL使用默认配置，已提供表结构、查询语句、执行计划、系统环境及C#测试代码，担心数据量达千万级后性能不可接受，希望获取查询优化或配置调整的可行方案。

表结构

table_data_info 表

CREATE SEQUENCE IF NOT EXISTS table_data_info_id_seq;
CREATE TABLE "public"."table_data_info" (
    "id" int4 NOT NULL DEFAULT nextval('table_data_info_id_seq'::regclass),
    "data_id" int4 NOT NULL,
    "tool_id" int4,
    "count_data" int2,
    "type_channel_1" "public"."type_graph",
    "count_channel_1" int2,
    "type_channel_2" "public"."type_graph",
    "count_channel_2" int2,
    "type_sample" "public"."type_sample",
    "revision" int2,
    "decimal_place" int2,
    "date_time" timestamp,
    "event_id" int4,
    "fastening_time" int2,
    "preset" int2,
    "target_torque" int2,
    "torque" int2,
    "speed" int2,
    "angle_1" int2,
    "angle_2" int2,
    "angle_3" int2,
    "screws" int2,
    "error_code" int2,
    "direction" "public"."type_direction",
    "status" "public"."type_status",
    "snug_angle" int2,
    "seating" int2,
    "clamp" int2,
    "prevailing" int2,
    "compensation" int2,
    "unit" "public"."type_unit",
    "barcode" text,
    CONSTRAINT "table_data_info_data_id_fkey" FOREIGN KEY ("data_id") REFERENCES "public"."table_data"("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT "table_data_info_tool_id_fkey" FOREIGN KEY ("tool_id") REFERENCES "public"."table_tool"("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY ("id")
);

table_tool 表

CREATE SEQUENCE IF NOT EXISTS table_tool_id_seq;
CREATE TABLE "public"."table_tool" (
    "id" int4 NOT NULL DEFAULT nextval('table_tool_id_seq'::regclass),
    "name" text NOT NULL,
    "ip" inet NOT NULL,
    "serial_number" text NOT NULL,
    "type" int2 NOT NULL,
    "model" int2 NOT NULL,
    "version" int2 NOT NULL,
    "register_date" timestamp NOT NULL,
    "disabled" bool DEFAULT false,
    "description" text,
    PRIMARY KEY ("id")
);

table_data 表

CREATE SEQUENCE IF NOT EXISTS table_data_id_seq;
CREATE TABLE "public"."table_data" (
    "id" int4 NOT NULL DEFAULT nextval('table_data_id_seq'::regclass),
    "tool_id" int4 NOT NULL,
    "date_time" timestamp NOT NULL DEFAULT now(),
    "status" "public"."type_status",
    CONSTRAINT "table_data_tool_id_fkey" FOREIGN KEY ("tool_id") REFERENCES "public"."table_tool"("id") ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY ("id")
);

查询语句与执行计划

查询语句

SELECT 
   i.data_id, i.tool_id, i.count_data, i.type_channel_1, i.count_channel_1, 
   i.type_channel_2, i.count_channel_2, i.type_sample, i.revision, i.decimal_place,
   i.date_time, i.event_id, i.fastening_time, i.preset, i.target_torque, i.torque,
   i.speed, i.angle_1, i.angle_2, i.angle_3, i.screws, i.error_code, i.direction,
   i.status, i.snug_angle, i.seating, i.clamp, i.prevailing, i.compensation,
   i.unit, i.barcode, t.name
FROM table_data_info as i
INNER JOIN table_tool as t on i.tool_id = t.id

执行计划

Hash Join  (cost=4.25..12246.72 rows=415937 width=99) (actual time=0.043..140.230 rows=418372 loops=1)
  Hash Cond: (i.tool_id = t.id)
  ->  Seq Scan on table_data_info i  (cost=0.00..11104.37 rows=415937 width=85) (actual time=0.010..17.110 rows=418372 loops=1)
  ->  Hash  (cost=3.00..3.00 rows=100 width=18) (actual time=0.029..0.030 rows=100 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 14kB
        ->  Seq Scan on table_tool t  (cost=0.00..3.00 rows=100 width=18) (actual time=0.008..0.017 rows=100 loops=1)
Planning Time: 0.151 ms
Execution Time: 146.977 ms

系统环境

OS : Windows 11 22H2 64bit
Processor : Intel core i7-9700K CPU 3.60GHz
RAM : 16.0GB
SSD : 250.0 GB
PostgreSQL : v15 (latest version)

Test project environment
Visual studio 2019
C# 7.3
.Net Framework 4.8
NpgSQL 4.1.8

测试代码

// connection
using (var conn = new NpgsqlConnection(DbManager.DbString))
{
    // open
    conn.Open();
    // command
    using (var cmd = new NpgsqlCommand(string.Empty, conn))
    {
        // set query
        cmd.CommandText =
            $@" SELECT 
                    i.data_id, i.tool_id, i.count_data, i.type_channel_1, i.count_channel_1, 
                    i.type_channel_2, i.count_channel_2, i.type_sample, i.revision, i.decimal_place,
                    i.date_time, i.event_id, i.fastening_time, i.preset, i.target_torque, i.torque,
                    i.speed, i.angle_1, i.angle_2, i.angle_3, i.screws, i.error_code, i.direction,
                    i.status, i.snug_angle, i.seating, i.clamp, i.prevailing, i.compensation,
                    i.unit, i.barcode, t.name
                FROM table_data_info i
                INNER JOIN table_tool t on i.tool_id = t.id";
        // debug
        var time = DateTime.Now;
        // execute
        using (var reader = cmd.ExecuteReader())
        {
            // read
            while (reader.Read())
            {
            }
        }
        // debug
        Debug.WriteLine($@"{(DateTime.Now - time).TotalMilliseconds} ms");
    }

    // close
    conn.Close();
}

优化方案

1. 索引优化

给table_data_info的tool_id字段创建索引，千万级数据量下可避免全表扫描的性能损耗：
```
CREATE INDEX idx_table_data_info_tool_id ON table_data_info(tool_id);
```

若后续查询会按date_time等字段过滤，提前创建对应索引：

CREATE INDEX idx_table_data_info_date_time ON table_data_info(date_time);

使用覆盖索引，将查询所需的table_data_info字段全部包含，避免回表：

CREATE INDEX idx_table_data_info_tool_id_include ON table_data_info(tool_id) INCLUDE (data_id, count_data, type_channel_1, count_channel_1, type_channel_2, count_channel_2, type_sample, revision, decimal_place, date_time, event_id, fastening_time, preset, target_torque, torque, speed, angle_1, angle_2, angle_3, screws, error_code, direction, status, snug_angle, seating, clamp, prevailing, compensation, unit, barcode);

2. PostgreSQL配置调整

针对你的硬件配置，修改postgresql.conf中的关键参数：

shared_buffers = 4GB（物理内存的1/4，默认128MB严重偏低）
work_mem = 64MB（单个操作可用内存，默认4MB）
maintenance_work_mem = 1GB（维护操作可用内存，默认64MB）
effective_cache_size = 12GB（物理内存的3/4，帮助优化器选优）
wal_buffers = 16MB（默认4MB）
max_parallel_workers_per_gather = 4（开启并行扫描，提升大表查询效率）

3. 查询语句优化

只选择业务必需的字段，减少数据传输量
若业务允许，按id或date_time分页查询，避免一次性加载千万级数据
定期执行VACUUM ANALYZE更新统计信息，帮助优化器生成更优执行计划：
```
VACUUM ANALYZE table_data_info;
```

4. 应用层优化

升级Npgsql到最新稳定版，新版本有性能优化
使用异步读取ExecuteReaderAsync，避免阻塞线程

开启CommandBehavior.SequentialAccess，减少内存占用提升读取速度：

using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
    while (reader.Read())
    {
        // 读取逻辑
    }
}

5. 数据存储优化

若barcode长度固定，将text类型改为varchar(n)，减少存储开销
对table_data_info按date_time或tool_id分区，降低单分区数据量，提升查询速度

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

PostgreSQL大数量查询性能优化求助：50万行查询耗时超1秒

表结构 #

table_data_info 表 #

table_tool 表 #

table_data 表 #

查询语句与执行计划 #

查询语句 #

执行计划 #

系统环境 #

测试代码 #

优化方案 #

1. 索引优化 #

2. PostgreSQL配置调整 #

3. 查询语句优化 #

4. 应用层优化 #

5. 数据存储优化 #

表结构