Thread: Windows slowness?

Hi all,

I have a query that runs much slower in Postgres on Windows than on 
Linux, and I'm so far unable to explain why - the execution plans are 
identical and the hardware is reasonably the same caliber.

Using explain analyze on the database running on Windows I get

->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1 
width=295) (actual time=0.075..0.075 rows=0 loops=229227)

The server is Postgres 12, and for reasons outside of my control it runs 
on Windows 2012 on a virtual server. It has 4 cores and 32 GB ram 
allocated on a Xeon E5 4660 v4, and running winsat shows satisfactory 
disk and memory bandwidth and CPU performance.

If I copy the database to my laptop running Linux (Postgres 12 on Fedora 
32, i7-9750H, 16 GB ram) I get the exact same execution plan. Explain 
analyze says

->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1 
width=295) (actual time=0.008..0.008 rows=0 loops=229227)

Note that the index scans are more than 9 times faster on my laptop, and 
the entire query executes about 12 times faster. I realize that each 
core in the laptop CPU is faster than a server core and that 
virtualization doesn't help performance, but I wouldn't expect that to 
make the Windows box 10 times slower.

The table is freshly vacuumed. It has about 10M rows and takes about 
2.6G disk space; the index is about 600M. Everything is cached; there's 
basically no disk I/O happening while the query is executing.

The only Postgres configuration difference between the Windows and Linux 
environments is shared_buffers, which is 4G on my laptop and 512M on the 
Windows server, and effective_cache_size which are 8G on the laptop and 
16G on the server.

I suspect that something is rotten in for example the provisioning of 
the virtualization environment, but before I start pestering the 
operations people I would really appreciate any comments on whether the 
performance difference is to be expected or if there's some obvious 
tuning to try.

Best regards & thanks,
   Mikkel Lauritsen

On Thu, 11 Jun 2020 at 07:41, Mikkel Lauritsen <renard@tala.dk> wrote:
> I have a query that runs much slower in Postgres on Windows than on
> Linux

> Using explain analyze on the database running on Windows I get
>
> ->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
> width=295) (actual time=0.075..0.075 rows=0 loops=229227)

> If I copy the database to my laptop running Linux (Postgres 12 on Fedora
> 32, i7-9750H, 16 GB ram) I get the exact same execution plan. Explain
> analyze says
>
> ->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
> width=295) (actual time=0.008..0.008 rows=0 loops=229227)
>

> The table is freshly vacuumed. It has about 10M rows and takes about
> 2.6G disk space; the index is about 600M. Everything is cached; there's
> basically no disk I/O happening while the query is executing.

Can you confirm what: SELECT pg_relation_size('event_pkey'),
pg_relation_size('event'); says on each

> The only Postgres configuration difference between the Windows and Linux
> environments is shared_buffers, which is 4G on my laptop and 512M on the
> Windows server, and effective_cache_size which are 8G on the laptop and
> 16G on the server.

There is some slight advantage to having the buffers directly in
shared buffers. Having them in the kernel's page cache does still
require getting them into shared buffers. Going by these sizes it
seems much more likely that the Linux instance could have all buffers
in shared_buffers, but it seems likely the Windows instance won't. I
can't imagine that counts for 10x, but it surely must count for
something.

It would be good to see:

SET track_io_timing = on;
EXPLAIN (ANALYZE, BUFFERS) <the query>

David

Hi David,

Many thanks for your response - you wrote:

On 2020-06-10 23:08, David Rowley wrote:
> On Thu, 11 Jun 2020 at 07:41, Mikkel Lauritsen <renard@tala.dk> wrote:
>> I have a query that runs much slower in Postgres on Windows than on
>> Linux
> 
>> Using explain analyze on the database running on Windows I get
>> 
>> ->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
>> width=295) (actual time=0.075..0.075 rows=0 loops=229227)
> 
>> If I copy the database to my laptop running Linux (Postgres 12 on 
>> Fedora
>> 32, i7-9750H, 16 GB ram) I get the exact same execution plan. Explain
>> analyze says
>> 
>> ->  Index Scan using event_pkey on event t1  (cost=0.56..0.95 rows=1
>> width=295) (actual time=0.008..0.008 rows=0 loops=229227)

--- snip ---

> Can you confirm what: SELECT pg_relation_size('event_pkey'),
> pg_relation_size('event'); says on each

1011384320 and 2753077248, respectively.

--- snip ---

> It would be good to see:
> 
> SET track_io_timing = on;
> EXPLAIN (ANALYZE, BUFFERS) <the query>

I wasn't aware of that tracing option - thanks! For this particular plan 
entry the output is

  Buffers: shared hit=896304 read=257234
  I/O Timings: read=11426.745

Some rows have been added to the table since my initial mail, so the 
numbers may be slightly off.

As another reply has suggested I need to verify that somebody hasn't 
accidentally misconfigured an antivirus client to scan the database 
files. If that turns out to be the case I guess it's embarrassment of 
the year for me :-/

Best regards,
   Mikkel Lauritsen