Slow queries after Windows startup

Hello,

I’m running PostgreSQL 9.3 on Windows 7 and I’m having a performance issue at startup. I have installed PostgreSQL as a service through Windows installer.

The database size is 3 Go, with 120 tables.

Every time I try to run queries right after Windows startup, it takes a huge amount of time.

If I restart the PostgreSQL Windows service, queries are way faster.

I have activated debug log and here is what I get before Windows restart:

duration: 2.000 ms  parse

duration: 3.000 ms  bind

duration: 0.000 ms  execute

And after Windows restart:

duration: 364.000 ms  parse

duration: 415.000 ms  bind

duration: 0.000 ms  execute

For information, the test query is:

SELECT t.typlen FROM pg_catalog.pg_type t, pg_catalog.pg_namespace n WHERE t.typnamespace=n.oid AND t.typname='name' AND n.nspname='pg_catalog'

It’s not related to the query itself since other queries give the same result (from 10x to 100x longer).

Here are my settings (all log and locale-related settings omitted on purpose):

I run queries through JDBC driver (9.3-1100-jdbc4.jar). I know that the issue is not related to the PC, since it give the same result on a bunch of different computers.

I have two questions:

·         What is the difference between restarting PostgreSQL service and restarting the computer? Is PostgreSQL relying on some kind of OS-level cache outside Windows service?

·         How can I dig down deeper and see what’s causing PostgreSQL slowdown?

Thanks in advance for your help,

BR,

Guillaume POUSSEL | ♠Sogeti High Tech

guillaume.poussel@sogeti.com