0

I have a problem with libpq on windows. Connecting to a db and running a "select * from some_table;" is very slow.

The table has only 1800 rows, 7 columns. No blobs etc.

The query is taking around 3500ms, in linux it takes around 800ms. (About 500ms is network time, the server is on the opposite side of the world from my location.)

The hardware is identical (dual boot)

Why does this so long in windows? I tested in pqsl, and pgadmin to rule out errors in the app code.

Any advice or clues?

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
Rob
  • 2,511
  • 2
  • 20
  • 31
  • Can you show us times for psql, pgadmin on Windows machine and Linux machine? Can you test both your location using Jython on both locations (for example code you can look at: http://stackoverflow.com/questions/1102704/do-you-observe-postgresql-temp-table-performance-degradation-since-8-3)? – Michał Niklas Nov 08 '10 at 07:33
  • Yes, as above 3500ms on windows in pgadmin, 800ms on linux in pgadmin. Further testing show it is windows xp at fault. A file copy to from the machine to the windows client also take around 5 times longer than it does on the linux client. – Rob Nov 10 '10 at 04:30
  • See this link for more information: http://technet.microsoft.com/en-us/magazine/2007.01.cableguy.aspx – Rob Nov 10 '10 at 04:30

1 Answers1

0

I would be willing to bet that the real problem is antivirus software acting up. It is true that PostgreSQL on Windows may not perform quite as well as on Linux, but the differences you are seeing cannot be simply in relation to the differences between multiple processes and multiple threads (copy on write, etc).

The very first thing to do is to rule out causes like antivirus software. Because this software sits in between reads and writes of disk I/O it has the capability of making your disk I/O significantly slower. Additionally if it is slow enough it may render sequential disk I/O performance more like random disk I/O which is not a good thing. So try with your antivirus switched off (and preferably not connected to a network).

A second thing I would look at is filesystem fragmentation. Are these files heavily fragmented? If so, disk I/O will be more expensive as well. Beyond this, doing a clean boot, starting the service manually, and trying this again may rule out other programs interfering with disk I/O.

Once you have the problem ruled down, then it should be simple to come up with a solution.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182