PostgreSQL PgAdmin与DBeaver图形化管理工具详解

// dbeaver 通过 jdbc 设置最大返回行数
statement.setmaxrows(200);

-- 查看 cpu 信息
select * from pg_sys_cpu_info();
-- 查看 cpu 使用率
select * from pg_sys_cpu_usage_info();
-- 查看内存信息
select * from pg_sys_memory_info();
-- 查看磁盘 i/o 信息
select * from pg_sys_io_info();
-- 查看系统负载均值
select * from pg_sys_load_avg_info();
-- 查看进程信息
select * from pg_sys_process_info();

-- 查看执行计划（不实际执行）
explain select * from pg_class;
-- 查看实际执行计划（实际执行 sql）
explain (analyze, buffers, timing) select * from pg_class;

// dbeaver 默认行为
statement stmt = connection.createstatement();
stmt.setmaxrows(200);  // 限制返回 200 行
resultset rs = stmt.executequery("select * from large_table");

const { client } = require('pg');
// postgresql 连接配置
const config = {
  host: 'localhost',
  port: 5432,
  database: 'testdb',
  user: 'postgres',
  password: 'your_password',
};
async function demo() {
  const client = new client(config);
  try {
    await client.connect();
    console.log('✅ 连接 postgresql 成功');
    // 1. 创建测试表
    await client.query(`
      create table if not exists demo_users (
        id serial primary key,
        name varchar(100),
        created_at timestamp default now()
      )
    `);
    console.log('✅ 测试表创建成功');
    // 2. 插入测试数据（使用 generate_series 生成 1000 行）
    await client.query(`
      insert into demo_users (name)
      select 'user_' || generate_series
      from generate_series(1, 1000)
    `);
    console.log('✅ 插入 1000 条测试数据');
    // 3. 模拟 dbeaver 默认 maxrows=200 的行为
    console.log('\n--- 模拟 dbeaver maxrows=200 ---');
    const reslimited = await client.query('select * from demo_users limit 200');
    console.log(`返回行数: ${reslimited.rows.length}`);
    // 4. 无限制查询（对比）
    console.log('\n--- 无限制查询 ---');
    const resfull = await client.query('select count(*) from demo_users');
    console.log(`总行数: ${resfull.rows[0].count}`);
    // 5. 使用游标分批获取（模拟 dbeaver 分页加载）
    console.log('\n--- 使用游标分批获取 ---');
    const cursorres = await client.query('declare user_cursor cursor for select * from demo_users');
    console.log('游标创建成功');
    let batchsize = 200;
    let totalfetched = 0;
    let hasmore = true;
    while (hasmore) {
      const batch = await client.query(`fetch ${batchsize} from user_cursor`);
      totalfetched += batch.rows.length;
      console.log(`批次获取: ${batch.rows.length} 行，累计: ${totalfetched}`);
      if (batch.rows.length < batchsize) {
        hasmore = false;
      }
    }
    await client.query('close user_cursor');
    console.log(`✅ 游标方式共获取 ${totalfetched} 行`);
    // 6. 执行计划分析（模拟 pgadmin explain analyze）
    console.log('\n--- 执行计划分析 ---');
    const explainres = await client.query(`
      explain (analyze, buffers, timing)
      select * from demo_users where name like 'user_%'
    `);
    console.log('执行计划:');
    explainres.rows.foreach(row => console.log('  ' + row['query plan']));
    // 7. 清理
    await client.query('drop table demo_users');
    console.log('\n✅ 测试表已清理');
  } catch (err) {
    console.error('❌ 错误:', err.message);
  } finally {
    await client.end();
  }
}
demo();

-- 1. 创建测试表
create table demo_users (id serial primary key, name varchar(100));
-- 2. 插入 1000 条测试数据
insert into demo_users (name) select 'user_' || generate_series from generate_series(1, 1000);
-- 3. 限制返回 200 行（模拟 dbeaver maxrows）
select * from demo_users limit 200;
-- 4. 统计总行数
select count(*) from demo_users;
-- 5. 使用游标分批获取
begin;
declare user_cursor cursor for select * from demo_users;
fetch 200 from user_cursor;
fetch 200 from user_cursor;
close user_cursor;
commit;
-- 6. 查看执行计划
explain (analyze, buffers, timing) select * from demo_users where name like 'user_%';
-- 7. 清理
drop table demo_users;

package main
import (
	"context"
	"fmt"
	"log"
	"github.com/jackc/pgx/v5"
)
func main() {
	ctx := context.background()
	// 连接配置（请替换为实际值）
	connstr := "postgres://postgres:your_password@localhost:5432/testdb"
	conn, err := pgx.connect(ctx, connstr)
	if err != nil {
		log.fatalf("❌ 连接失败: %v", err)
	}
	defer conn.close(ctx)
	fmt.println("✅ 连接 postgresql 成功")
	// 1. 创建测试表
	_, err = conn.exec(ctx, `
		create table if not exists demo_users (
			id serial primary key,
			name varchar(100),
			created_at timestamp default now()
		)
	`)
	if err != nil {
		log.fatalf("创建表失败: %v", err)
	}
	fmt.println("✅ 测试表创建成功")
	// 2. 插入 1000 条数据（使用 generate_series）
	_, err = conn.exec(ctx, `
		insert into demo_users (name)
		select 'user_' || generate_series
		from generate_series(1, 1000)
	`)
	if err != nil {
		log.fatalf("插入数据失败: %v", err)
	}
	fmt.println("✅ 插入 1000 条测试数据")
	// 3. 模拟 dbeaver maxrows=200（使用 limit）
	fmt.println("\n--- 模拟 dbeaver maxrows=200 ---")
	rows, err := conn.query(ctx, "select * from demo_users limit 200")
	if err != nil {
		log.fatalf("查询失败: %v", err)
	}
	count := 0
	for rows.next() {
		count++
	}
	rows.close()
	fmt.printf("返回行数: %d\n", count)
	// 4. 无限制查询（总数）
	var total int
	err = conn.queryrow(ctx, "select count(*) from demo_users").scan(&total)
	if err != nil {
		log.fatalf("统计失败: %v", err)
	}
	fmt.printf("总行数: %d\n", total)
	// 5. 使用游标分批获取（模拟 dbeaver 分页加载）
	fmt.println("\n--- 使用游标分批获取 ---")
	_, err = conn.exec(ctx, "declare user_cursor cursor for select * from demo_users")
	if err != nil {
		log.fatalf("创建游标失败: %v", err)
	}
	fmt.println("游标创建成功")
	batchsize := 200
	totalfetched := 0
	for {
		rows, err := conn.query(ctx, fmt.sprintf("fetch %d from user_cursor", batchsize))
		if err != nil {
			log.fatalf("fetch 失败: %v", err)
		}
		cnt := 0
		for rows.next() {
			cnt++
		}
		rows.close()
		totalfetched += cnt
		fmt.printf("批次获取: %d 行，累计: %d\n", cnt, totalfetched)
		if cnt < batchsize {
			break
		}
	}
	_, err = conn.exec(ctx, "close user_cursor")
	if err != nil {
		log.fatalf("关闭游标失败: %v", err)
	}
	fmt.printf("✅ 游标方式共获取 %d 行\n", totalfetched)
	// 6. 执行计划分析（模拟 pgadmin explain analyze）
	fmt.println("\n--- 执行计划分析 ---")
	planrows, err := conn.query(ctx, `
		explain (analyze, buffers, timing)
		select * from demo_users where name like 'user_%'
	`)
	if err != nil {
		log.fatalf("执行计划失败: %v", err)
	}
	fmt.println("执行计划:")
	for planrows.next() {
		var line string
		planrows.scan(&line)
		fmt.println("  " + line)
	}
	planrows.close()
	// 7. 清理
	_, err = conn.exec(ctx, "drop table demo_users")
	if err != nil {
		log.fatalf("清理失败: %v", err)
	}
	fmt.println("\n✅ 测试表已清理")
}

import psycopg2
def demo():
    # 连接配置（请替换为实际值）
    conn = psycopg2.connect(
        host="localhost",
        port=5432,
        database="testdb",
        user="postgres",
        password="your_password"
    )
    conn.autocommit = true
    cur = conn.cursor()
    print("✅ 连接 postgresql 成功")
    try:
        # 1. 创建测试表
        cur.execute("""
            create table if not exists demo_users (
                id serial primary key,
                name varchar(100),
                created_at timestamp default now()
            )
        """)
        print("✅ 测试表创建成功")
        # 2. 插入 1000 条数据
        cur.execute("""
            insert into demo_users (name)
            select 'user_' || generate_series
            from generate_series(1, 1000)
        """)
        print("✅ 插入 1000 条测试数据")
        # 3. 模拟 dbeaver maxrows=200
        print("\n--- 模拟 dbeaver maxrows=200 ---")
        cur.execute("select * from demo_users limit 200")
        rows = cur.fetchall()
        print(f"返回行数: {len(rows)}")
        # 4. 无限制查询（总数）
        cur.execute("select count(*) from demo_users")
        total = cur.fetchone()[0]
        print(f"总行数: {total}")
        # 5. 游标分批获取
        print("\n--- 使用游标分批获取 ---")
        cur.execute("declare user_cursor cursor for select * from demo_users")
        print("游标创建成功")
        batch_size = 200
        total_fetched = 0
        while true:
            cur.execute(f"fetch {batch_size} from user_cursor")
            batch = cur.fetchall()
            cnt = len(batch)
            total_fetched += cnt
            print(f"批次获取: {cnt} 行，累计: {total_fetched}")
            if cnt < batch_size:
                break
        cur.execute("close user_cursor")
        print(f"✅ 游标方式共获取 {total_fetched} 行")
        # 6. 执行计划分析
        print("\n--- 执行计划分析 ---")
        cur.execute("""
            explain (analyze, buffers, timing)
            select * from demo_users where name like 'user_%'
        """)
        print("执行计划:")
        for row in cur.fetchall():
            print("  " + row[0])
        # 7. 清理
        cur.execute("drop table demo_users")
        print("\n✅ 测试表已清理")
    except exception as e:
        print(f"❌ 错误: {e}")
    finally:
        cur.close()
        conn.close()
if __name__ == "__main__":
    demo()

import java.sql.*;
public class demo {
    private static final string url = "jdbc:postgresql://localhost:5432/testdb";
    private static final string user = "postgres";
    private static final string password = "your_password";
    public static void main(string[] args) {
        try (connection conn = drivermanager.getconnection(url, user, password)) {
            system.out.println("✅ 连接 postgresql 成功");
            // 1. 创建测试表
            try (statement stmt = conn.createstatement()) {
                stmt.executeupdate("""
                    create table if not exists demo_users (
                        id serial primary key,
                        name varchar(100),
                        created_at timestamp default now()
                    )
                """);
                system.out.println("✅ 测试表创建成功");
            }
            // 2. 插入 1000 条数据
            try (statement stmt = conn.createstatement()) {
                stmt.executeupdate("""
                    insert into demo_users (name)
                    select 'user_' || generate_series
                    from generate_series(1, 1000)
                """);
                system.out.println("✅ 插入 1000 条测试数据");
            }
            // 3. 模拟 dbeaver maxrows=200（使用 setmaxrows）
            system.out.println("\n--- 模拟 dbeaver maxrows=200 ---");
            try (statement stmt = conn.createstatement()) {
                stmt.setmaxrows(200);  // 关键点：模拟 dbeaver 行为
                resultset rs = stmt.executequery("select * from demo_users");
                int count = 0;
                while (rs.next()) count++;
                system.out.println("返回行数: " + count);
            }
            // 4. 无限制查询（总数）
            try (statement stmt = conn.createstatement()) {
                resultset rs = stmt.executequery("select count(*) from demo_users");
                rs.next();
                system.out.println("总行数: " + rs.getlong(1));
            }
            // 5. 游标分批获取
            system.out.println("\n--- 使用游标分批获取 ---");
            try (statement stmt = conn.createstatement()) {
                stmt.executeupdate("declare user_cursor cursor for select * from demo_users");
                system.out.println("游标创建成功");
                int batchsize = 200;
                int totalfetched = 0;
                boolean hasmore = true;
                while (hasmore) {
                    try (resultset rs = stmt.executequery("fetch " + batchsize + " from user_cursor")) {
                        int cnt = 0;
                        while (rs.next()) cnt++;
                        totalfetched += cnt;
                        system.out.printf("批次获取: %d 行，累计: %d%n", cnt, totalfetched);
                        if (cnt < batchsize) hasmore = false;
                    }
                }
                stmt.executeupdate("close user_cursor");
                system.out.printf("✅ 游标方式共获取 %d 行%n", totalfetched);
            }
            // 6. 执行计划分析
            system.out.println("\n--- 执行计划分析 ---");
            try (statement stmt = conn.createstatement()) {
                resultset rs = stmt.executequery("""
                    explain (analyze, buffers, timing)
                    select * from demo_users where name like 'user_%'
                """);
                system.out.println("执行计划:");
                while (rs.next()) {
                    system.out.println("  " + rs.getstring(1));
                }
            }
            // 7. 清理
            try (statement stmt = conn.createstatement()) {
                stmt.executeupdate("drop table demo_users");
                system.out.println("\n✅ 测试表已清理");
            }
        } catch (sqlexception e) {
            system.err.println("❌ 错误: " + e.getmessage());
        }
    }
}

-- 1. 创建测试表
create table demo_users (id serial primary key, name varchar(100));
-- 2. 插入 1000 条测试数据
insert into demo_users (name) select 'user_' || generate_series from generate_series(1, 1000);
-- 3. 限制返回 200 行（模拟 dbeaver maxrows）
select * from demo_users limit 200;
-- 4. 统计总行数
select count(*) from demo_users;
-- 5. 使用游标分批获取
begin;
declare user_cursor cursor for select * from demo_users;
fetch 200 from user_cursor;
fetch 200 from user_cursor;
close user_cursor;
commit;
-- 6. 查看执行计划
explain (analyze, buffers, timing) select * from demo_users where name like 'user_%';
-- 7. 清理
drop table demo_users;