PostgreSQL查询性能不佳 [英] Poor performance on a PostgreSQL query
问题描述
更新: 我刚想到:pgAdmin是否可能不计算查询的实际时间，而是计算在网格中绘制结果所需的时间?
UPDATE: Just occurred to me: Is it possible that pgAdmin counts not the actual time of the query, but the time required to draw the results in the grid?
我只是使用命令行(psql)执行查询，并将结果输出到txt文件中，而且非常迅速(最高1-2秒).
I just executed the query using the command line (psql) and output the results in a txt file and it was very swift (1-2 seconds tops).
在这种情况下，问题不是postgresql/ubuntu服务器配置；而是而是显示适配器的问题.
In that case, the issue is not a postgresql / ubuntu server configuration; it is rather a display adapter's problem.
我是对的还是搜索方向错误?
Am I right or searching in the wrong direction?
在Arch Linux 64位系统上运行的旧" PC(Core 2 Duo，4GB RAM，250GB SATA HD)上，我可以在0.4ms内运行一个简单的"SELECT * FROM sometable"查询 (我正在使用pgAdmin 3).该数据库是PostgreSQL 9.1(带有PostGIS)，该表包含约60.000行.
On my "old" PC (Core 2 Duo, 4GB RAM, 250GB SATA HD) running on Arch Linux 64bit I can run a simple "SELECT * FROM sometable" query in 0.4ms (I am using pgAdmin 3). The database is a PostgreSQL 9.1 (with PostGIS) and the table contains around 60.000 rows.
我已将数据库转移到较新的计算机(Core i5、8GB RAM，1TB Western Digital Black SATA III)上，同一查询最多需要22秒(!!!) 较新的计算机正在运行64位Ubuntu Server 13.04.
I've transferred the database to a newer computer (Core i5, 8GB RAM, 1TB Western Digital Black SATA III) and the same query takes as much as 22 seconds (!!!) The newer computer is running Ubuntu Server 13.04 64bit.
为了进行进一步的测试，我将数据库复制到了Windows 7 64位PC(Core i5，6GB RAM)上，查询运行了大约10秒钟.
For further testing, I copied the database to a Windows 7 64bit PC (Core i5, 6GB RAM) and the query is running in about 10 seconds.
这显然是一个配置问题，但是对于PostgreSQL还是Ubuntu Server的配置问题，我有点困惑.
It is clearly a configuration issue, but I am a little bit confused whether it is a PostgreSQL or Ubuntu Server's configuration problem.
我已经尝试使用PostgreSQL的conf文件(kernel.shmmax，shared_buffers等)，但无济于事. 当然，我已经对VACUUM，VACUUM进行了分析并重新创建了所有索引.
I have already tried to play around with PostgreSQL's conf files (kernel.shmmax, shared_buffers, etc) but to no avail. And of course I have VACUUMed, VACUUM ANALYZed and recreated all the indices.
有什么想法吗?我对Ubuntu Server的解决方案感兴趣，而不是真的在乎Windows 7计算机.
Any ideas? I am interested in an Ubuntu Server's solution, not really care about the Windows 7 computer.
预先感谢
Ebl
推荐答案
To get execution time on the server without data transfer to the client, use EXPLAIN ANALYZE.
或在pgAdmin查询工具中使用键盘快捷键: SHIFT F7 (取决于您的操作系统和版本，请在查询菜单中查看键盘快捷键).
Or use the keyboard shortcut in the pgAdmin query tool: SHIFTF7 (depending on your OS and version, check the query menu for keyboard shortcut).
顺便说一句，如果您查阅pgAdmin手册，请使用 current 版本-截至1.18:
http://www.pgadmin.org/docs/1.18/query.html
BTW, if you consult the pgAdmin manual, use the current release - 1.18 as of now:
http://www.pgadmin.org/docs/1.18/query.html
这篇关于PostgreSQL查询性能不佳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
