Have you verified that this is isn't caused by cold filesystem caches?
On 11.01.2018 09:19, POUSSEL, Guillaume wrote: > 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.pous...@sogeti.com> guillaume.pous...@sogeti.com > > > > > > > > > > > > > > > This message contains information that may be privileged or confidential and > is the property of the Capgemini Group. It is intended only for the person to > whom it is addressed. If you are not the intended recipient, you are not > authorized to read, print, retain, copy, disseminate, distribute, or use this > message or any part thereof. If you receive this message in error, please > notify the sender immediately and delete all copies of this message. >
