When developing a solution for a new customer request I created a new query over the production data.
Despite the relatively low row counts of the involved tables (all < 100k) I noticed quite a long execution time of
about85 ms to 100 ms.
The explain anaylze plan showed a parallel execution plan with 2 parallels.
The data structure and index structure was not quite optimal for this kind of query (which does not matter in this
case).
The comparison of the explain analyze plans on win-x64 and Linux x64 showed about 3 times longer execution on windows.
For comparison I reinstalled the production data on two test databases on different virtual machines on the same
hardware(the very same machine with Hyper-V virtualization).
The steps were only (on a mostly complete idle machine):
1. create test database
2. pg_restore of the production data from same dump file
3. analyze on the database
4. run the query multiple times (about 5 times) and took the fastest explain analyze.
On fedora 34 64 bit, PG 13.2 unmodified self compiled the query took about 33 ms.
On Windows Server 2019 64 bit, PG 13.2 from EDB download packages the query took about 85 ms.
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.1.1 20210428 (Red Hat 11.1.1-1), 64-bit
version
------------------------------------------------------------
PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit
The corresponding explain plans are available at explain.depesz.com
-- fedora
https://explain.depesz.com/s/Mq3P
-- windows
https://explain.depesz.com/s/VLtZ
The main difference is the time shown for the Gather Merge step (65 ms vs. 7 ms)
The explain plans and the non-standard GUCs are included in the attachments, the configuration for the databases seems
quitecomparable.
Unfortunately I cannot disclose the query and the table data.
My experience with parallel queries is not very wide, but this massive execution time difference of the exact same
queryon the exact same data on the exact same hardware with the same, unmodified last stable Postgres version is very
astonishing.
BTW I generally observed slower execution under Windows, so production has moved now to Linux.
There seem no relevant GUC differences concerning query execution, so the performance penalty of 300% to 900% (one step
only)is not easily explainable.
The databases remain on the system to repeat the queries on request in the queue of further investigation.
Thanks for looking.
Hans Buschmann