3

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?

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).

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?

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.

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.

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.

It is clearly a configuration issue, but I am a little bit confused whether it is a PostgreSQL or Ubuntu Server's configuration problem.

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.

Any ideas? I am interested in an Ubuntu Server's solution, not really care about the Windows 7 computer.

Thanks in advance,

Ebl

eblOnSO
  • 31
  • 5
  • Have you looked at the query plans on different machines? `EXPLAIN ANALYZE`? – Ihor Romanchenko Sep 18 '13 at 09:10
  • Yes. I just did it again (on Ubuntu and Windows 7 machines, I have not access to Arch Linux for the moment). The query plans are almost identical. – eblOnSO Sep 18 '13 at 09:19
  • rather than pgAdmin, I would go with psql and `EXPLAIN ANALYZE`, taking the grid drawing out of the equation. – Andrew Lazarus Sep 18 '13 at 15:55
  • I already did that, the `explain analyze` shows similar results. So, there is no need for further investigation. Anyway, thanks for your comments guys! – eblOnSO Sep 19 '13 at 05:06
  • PGAdmin has given me weird behaviour in the past...it seems to make behind the scene calls to system tables that hang and extend runtime. Avoid PGAdmin when you are speed testing. I've also had problems with index's not properly populating, dropping and recreating them has helped me in the past (the explain statement should show if it's using indexing properly).And finally...I've had issues with machines with multiple processors running SQL from PGAdmin...for some reason PGAdmin can decide to use only one and send everything at just that one processor. Monitor your processes and run a query – Twelfth Sep 19 '13 at 18:18

2 Answers2

2

To get execution time on the server without data transfer to the client, use EXPLAIN ANALYZE.

Or use the keyboard shortcut in the pgAdmin query tool: SHIFTF7 (depending on your OS and version, check the query menu for keyboard shortcut).

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

You are measuring the time it takes to transfer the rows to pgAdmin:

http://www.pgadmin.org/docs/1.4/query.html

" If a dataset was returned, not only the elapsed time for server execution is displayed, but also the time to retrieve the data from the server to the Data Output page."

If you try a "select count(*) from sometable" it will as fast as the old pc.

Leo
  • 1,066
  • 1
  • 6
  • 19