PSQL比PgAdmin III慢

Question

我一直在对我的应用程序进行性能分析，发现PgAdmin3执行完全相同的脚本比psql快得多。我正在使用Unix套接字、Ubuntu 12.04和Postgresql 9.1以及pgadmin3 v1.14。我有一个简单的脚本，如下所示：

BEGIN
INSERT INTO key_value(section,key,value) VALUES('section','key1','value');
....
INSERT INTO key_value(section,key,value) VALUES('section','key10000','value');
COMMIT;

这里是表格：

CREATE TABLE key_value
(
  key text NOT NULL,
  value text,
  CONSTRAINT key_value_pkey PRIMARY KEY (section , key )
)
WITH (
  OIDS=FALSE
);

这段脚本包含了10,000个插入动作。在pgadmin3中执行所需时间约为0.5秒，在psql中执行则需要2.5到3.5秒的时间。两者都在一个事务中，因此理论上不应有区别。但是在重新创建表、进行完整清理等操作后，结果始终如一。在PostgreSQL服务器中记录的日志显示，当从psql中执行时，每个插入语句都会生成一个日志，而从pgadmin3中执行时只有一个日志。要在psql中执行，需要使用以下方式：

psql  -n -t -f p.sql -o/dev/null

并且已经测试过

psql  -n -t -1 -f p.sql -o/dev/null

我的问题是为什么psql这么慢，为什么postgresql在一个客户端中记录每个语句，而在另一个客户端中只记录整个事务，并且是否有简单的方法可以修复psql。 编辑：仅澄清一下，我记录的是持续时间而不是语句：

在使用psql执行时，服务器返回以下内容：

2012-10-02 12:20:32 CEST LOG:  duration: 0.283 ms
....
2012-10-02 12:20:35 CEST LOG:  duration: 0.285 ms
2012-10-02 12:20:35 CEST LOG:  duration: 0.291 ms
2012-10-02 12:20:35 CEST LOG:  duration: 0.279 ms
2012-10-02 12:20:35 CEST LOG:  duration: 0.284 ms
2012-10-02 12:20:35 CEST LOG:  duration: 0.279 ms
2012-10-02 12:20:35 CEST LOG:  duration: 0.299 ms
....
2012-10-02 12:20:36 CEST LOG:  duration: 5.779 ms

使用pgadmin3执行时：

2012-10-02 12:23:21 CEST LOG:  duration: 532.695 ms

在psql和pgadmin3中，用户和数据库是相同的。

- vseguip

---

虽然这不是一个答案，但你的问题是“在此脚本中有10000个插入”。使用COPY，或者至少使用多值插入。至于不同的日志记录 - 也许你使用了ALTER USER ... SET log_statement = 'all'并且你正在使用不同的用户与psql和PgAdmin？或者你的psqlrc中有一个set？ - Craig Ringer

是的，我意识到这一点（还有其他解决方案，如多行插入等），但该应用程序现在正在使用传统存储方式，这样做需要重新设计应用程序。 - vseguip

刚刚检查了一下，没有psqlrc或ALTER USER语句。 - vseguip

如果您删除BEGIN和COMMIT并将psql传递此选项--single-transaction会发生什么？ - Kuberchaun

那是我评论中的第二次调用（选项为-1）。无论如何，我尝试了你建议的方法，但没有任何改变。 - vseguip

Answer 1

我的建议：使用pgAdmin将脚本作为一个多语句执行，这样可以减少网络（协议）开销。

- Pavel Stehule

---

你好，感谢您的回复。有没有办法让psql将其执行为多语句？ - vseguip

这是否也可以解释日志记录的差异？ - Craig Ringer

@CraigRinger，是的，它解释了日志中的差异。你可以尝试：#1 echo 'select 1; select 2' | psql postgres #2 psql -c "select 1; select 2" postgres - Pavel Stehule

@PavelStehule 嘿，谢谢你的回复，很有趣。 - Craig Ringer

@PavelStehule 就像你所说的，-c 命令行是有限制的。我可以使用 multinsert，但那需要对应用程序进行架构更改，因此我想看看是否有一种方法来测试它是否是多个语句在起作用。有人知道是否有另一个应用程序可以这样发送吗？ - vseguip

Answer 2

psql有一个集成解析器，可以将分号识别为语句分隔符。与Pavel's answer所述的pgadmin相反，它将SQL脚本分解成多个语句。

没有关闭此行为的选项。但是，使用DO匿名代码块应该可以在很少更改的情况下实现类似的结果。如果您像这样编写脚本：

DO $$
BEGIN
INSERT INTO key_value(section,key,value) VALUES('section','key1','value');
....
INSERT INTO key_value(section,key,value) VALUES('section','key10000','value');
END $$;

这将与pgadmin一样一次性发送到服务器（尽管在技术上有很大不同），并且可能会在可比较的时间内执行。

编辑：根据@vseguip的评论，这不是有效的，因为plpgsql解释器在这10k个语句上花费了很多时间。

替代想法：将脚本更改为仅具有一个INSERT和多行，而不是多个INSERT，没有DO块。

BEGIN;
INSERT INTO key_value(section,key,value) VALUES('section','key1','value'),
      ('section','key2','value'),
      ('section','key3','value'),
      ....
      ('section','key10000','value');
END;

- Daniel Vérité

---

由于某种原因（我猜是需要“编译”匿名函数），这比常规的psql慢，用时10秒对3.5秒。 - vseguip

@vseguip：太糟糕了。我已经更新了答案，提供了另一种选择。 - Daniel Vérité

谢谢，但是使用多值插入将需要重新架构应用程序。按照目前的情况，我最好的选择是将查询分组在一个巨大的字符串中，并将它们作为一个查询发送。 - vseguip

这个解决方案对我很有效，但实际上语法是这样的： INSERT INTO key_value(section,key,value) VALUES ('section','key1','value'), .... ('section','key10000','value');。也就是说，VALUES只写一次。 - MartinP

@MartinP：我的错，现在已经在答案中修复了，感谢您指出。 - Daniel Vérité