Dear PostgreSQL community,
we have noticed a severe decrease in performance reading
pg_catalog.pg_settings table in PostgreSQL 12 on MS Windows 10 machines
compared to earlier versions.
```
explain (analyze, buffers, timing)
SELECT * from pg_catalog.pg_settings where name =
'standard_conforming_strings';
```
On *PostgreSQL 12.5, compiled by Visual C++ build 1914, 64-bit:*
Function Scan on pg_show_all_settings a (cost=0.00..12.50 rows=5
width=485) (actual time=343.350..343.356 rows=1 loops=1)
Filter: (name = 'standard_conforming_strings'::text)
Rows Removed by Filter: 313
Planning Time: 0.079 ms
Execution Time: 343.397 ms
Compare to* PostgreSQL 11.13, compiled by Visual C++ build 1914, 64-bit*:
Function Scan on pg_show_all_settings a (cost=0.00..12.50 rows=5
width=485) (actual time=0.723..0.728 rows=1 loops=1)
Filter: (name = 'standard_conforming_strings'::text)
Rows Removed by Filter: 289
Planning Time: 0.125 ms
Execution Time: 0.796 ms
This is standard installation, the changed parameters are:
```
SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');
```
client_encoding UTF8 client
DateStyle ISO, YMD client
default_text_search_config pg_catalog.simple session
default_transaction_isolation read committed session
dynamic_shared_memory_type windows configuration file
extra_float_digits 3 session
lc_messages Lithuanian_Lithuania.1257 configuration file
lc_monetary Lithuanian_Lithuania.1257 configuration file
lc_numeric Lithuanian_Lithuania.1257 configuration file
lc_time Lithuanian_Lithuania.1257 configuration file
listen_addresses * configuration file
log_destination stderr configuration file
log_file_mode 0640 configuration file
log_timezone Europe/Helsinki configuration file
logging_collector on configuration file
max_connections 100 configuration file
max_stack_depth 2MB environment variable
max_wal_size 1GB configuration file
min_wal_size 80MB configuration file
port 5444 configuration file
search_path "$user", public session
shared_buffers 128MB configuration file
TimeZone Europe/Helsinki client
The slowing down is observed on *MS Windows 10 machines only*. We have pg12
on linux (PostgreSQL 12.6 (Debian 12.6-1.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit ) that doesn't show any
decrease in performance.
I've testet different versions and it seems the problem appeared on PG12,
earlier versions up to PG11 work ok. PG13 also suffers from low reading
speed of pg_settings.
The behaviour is reproduced on 3 different machines (2 virtual and one
physical, different hardware).
What is the cause of this? How to fix the issue?
Regards,
Julius Tuskenis
