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

bytea_output

escape

session

checkpoint_segments

45

configuration file

client_encoding

UNICODE

session

client_min_messages

notice

session

DateStyle

ISO, DMY

session

debug_pretty_print

on

configuration file

debug_print_plan

on

configuration file

default_text_search_config

pg_catalog.french

configuration file

listen_addresses

*

configuration file

logging_collector

on

configuration file

max_connections

100

configuration file

max_stack_depth

2MB

environment variable

port

5432

configuration file

shared_buffers

128MB

configuration file

TimeZone

GMT

user

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

<mailto:guillaume(dot)poussel(at)sogeti(dot)com> guillaume(dot)poussel(at)sogeti(dot)com