BUG #18306: Slow SQL query Postgresql 15 Debian family linux.

The following bug has been logged on the website:

Bug reference: 18306
Logged by: Przemysław Szustak
Email address: pszustak(at)lehmann-partner(dot)pl
PostgreSQL version: 15.3
Operating system: Ubuntu 22.04.2 LTS
Description:

Hi

Slow SQL query Postgresql 15 Debian family linux.

Some days ago I run SQL on localhost in Windows 10 in my program and it was
fast.
Next I try to run it in production server in Ubuntu 22.04LTS and I was
shocked because this SQL was executed 35x slower!

First I used Postgresql 16 on laptop, Postgresql 15 on porduction host.
I tested this SQL in production host on Postgresql 11 too and then was
executed fast.
Then I intalled (for testing) Ubuntu (VirtualBox) and run Postgres 11, 12,
13, 14, 15, 16. Postgres 11 and 12 was fast, 13 and above was very slow.
Then I try Debian and Mint (VirtualBox; just pg version 15) - was slow
too.
Then I try Fedora (Virutalbox; just pg version 15) - was fast.
Then I try Windows 10 (Virutalbox; just pg version 15) - was fast.

I made more tests.
I installed few OS on VirtualBox in defaults configurations (in 2 differents
laptops):
- Ubuntu 23,
- Debian 12,
- Fedora 39,
- Windows 10,
- Mint 21.3.

Debian based distros works very slow.
Some examples times for execution this SQL SELECT QUERY:
- virutalbox ubuntu 23: 02:48,802[s],
- virtualbox fedora 39: 00:02,815[s],
- production host (pg15): 03:09,086[s],
- production host (pg11): 00:06,294[s],
- laptop windows 10: 00:05.585[s].

Computers for testing:
- laptop: 11th Gen Intel(R) Core(TM) i7-11800H @ 2.30GHz, 32GB RAM, SSD
NVME
- VirtualBox on laptop: 8GB RAM, 8 cors, 50GB ssd nvme (3GB/3GB[r/w]);
- Production host have 500GB RAM, AMD EPYC 7543P 32-Core Processor, 3xSSD
NVME Micron 7400 (RAID, 6GB/3GB[r/w]).

As you can see - production server is more powerfull in all (but one core is
more powerfull in laptop).
EPLAIN and EXPLAIN ANALYZE looks very similar on differents OS.

More logs can be found here:
https://github.com/lehmannwebdev/pg15-slow-ubuntu-select.

Just few lines of SQL (because all is too long - all SQL was on github -
https://paste.depesz.com/s/3HX)
(SELECT 0 as index, 'SS1000004' as pic_name, s.height, ST_ASEWKT(schw) as
schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330723271066 54.528850348093 69.680675620019,17.330544567372
54.528243269792 69.741144348108,17.329920295303 54.528596373902
69.710909984063,17.329934068348 54.528641238441
69.710909984063,17.330723271066 54.528850348093 69.680675620019))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451234.426000118256 AND
1685451294.426000118256 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 1 as index, 'SS1000009' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330726616982 54.528841082335 69.896716876858,17.330616271172
54.528228960610 70.094864465195,17.329954316630 54.528556686322
69.995790671026,17.329963036227 54.528601945604
69.995790671026,17.330726616982 54.528841082335 69.896716876858))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451237.551000118256 AND
1685451297.551000118256 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 2 as index, 'SS1000014' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330760171623 54.528801910092 70.129867124306,17.330652980363
54.528189335098 70.263723770073,17.329988514887 54.528516705170
70.196795447189,17.329997001879 54.528561998431
70.196795447189,17.330760171623 54.528801910092 70.129867124306))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451239.384999990463 AND
1685451299.384999990463 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 3 as index, 'SS1000019' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330824934278 54.528716492883 70.448450557169,17.330622109132
54.528112020666 70.363722522860,17.330012809448 54.528473950070
70.406086540015,17.330028364405 54.528518613969
70.406086540015,17.330824934278 54.528716492883 70.448450557169))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451240.760999917984 AND
1685451300.760999917984 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 4 as index, 'SS1000024' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330894871140 54.528584485228 70.499279244654,17.330513746007
54.528009811023 70.529840881039,17.330022239123 54.528427506534
70.514560062847,17.330050960032 54.528469911425
70.514560062847,17.330894871140 54.528584485228 70.499279244654))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451241.894000053406 AND
1685451301.894000053406 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 5 as index, 'SS1000029' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330922989958 54.528457982971 70.593207274011,17.330393683377
54.527924660188 70.706567801809,17.330018536474 54.528381350455
70.649887537910,17.330058185021 54.528420650320
70.649887537910,17.330922989958 54.528457982971 70.593207274011))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451242.891000032425 AND
1685451302.891000032425 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 6 as index, 'SS1000034' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330921934875 54.528350692795 70.774972806578,17.330289438050
54.527856671155 70.805894798889,17.330008345331 54.528336099069
70.790433802733,17.330055593535 54.528372458996
70.790433802733,17.330921934875 54.528350692795 70.774972806578))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451243.783999919891 AND
1685451303.783999919891 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 7 as index, 'SS1000039' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330903749075 54.528264526188 70.960719062981,17.330205746886
54.527801313892 70.918615818012,17.329991741374 54.528292257943
70.939667440496,17.330043808119 54.528326318069
70.939667440496,17.330903749075 54.528264526188 70.960719062981))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451244.584000110626 AND
1685451304.584000110626 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 8 as index, 'SS1000044' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330879399160 54.528188657569 71.029679718518,17.330133947995
54.527751192026 71.082092291771,17.329971479779 54.528248565610
71.055886005145,17.330027034562 54.528280706322
71.055886005145,17.330879399160 54.528188657569 71.029679718518))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451245.332000017166 AND
1685451305.332000017166 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 9 as index, 'SS1000049' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330849113058 54.528118292679 71.226529545885,17.330066476932
54.527703326618 71.228400401763,17.329947958345 54.528204924511
71.227464973824,17.330006243877 54.528235388975
71.227464973824,17.330849113058 54.528118292679 71.226529545885))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451246.052000045776 AND
1685451306.052000045776 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 10 as index, 'SS1000054' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330822770214 54.528067905142 71.349352572496,17.330028340625
54.527660745430 71.439549680667,17.329923820899 54.528163081691
71.394451126581,17.329982972459 54.528192965136
71.394451126581,17.330822770214 54.528067905142 71.349352572496))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451246.716000080109 AND
1685451306.716000080109 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 11 as index, 'SS1000059' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330796862994 54.528019585085 71.529361496605,17.329997067321
54.527615843537 71.542196839127,17.329899291182 54.528119131131
71.535779167866,17.329958836499 54.528148759676
71.535779167866,17.330796862994 54.528019585085 71.529361496605))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451247.375999927521 AND
1685451307.375999927521 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 12 as index, 'SS1000064' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330769850976 54.527970634410 71.615570973963,17.329960747258
54.527573321464 71.690538925853,17.329874369385 54.528077262588
71.653054949908,17.329934597947 54.528106412623
71.653054949908,17.330769850976 54.527970634410 71.615570973963))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451247.986000061035 AND
1685451307.986000061035 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 13 as index, 'SS1000069' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330739135993 54.527918722466 71.807263700611,17.329918084356
54.527529743479 71.863665206988,17.329847164254 54.528034440213
71.835464453800,17.329908269195 54.528062969984
71.835464453800,17.330739135993 54.527918722466 71.807263700611))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451248.591000080109 AND
1685451308.591000080109 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)
union all (SELECT 14 as index, 'SS1000074' as pic_name, s.height,
ST_ASEWKT(schw) as schw FROM phoml_trajectory_tmp s,
ST_Centroid(ST_Transform(ST_SETSRID(ST_GeomFromEWKT('Polygon((
17.330710385546 54.527873240364 72.015426610430,17.329886019407
54.527486576672 72.016957303758,17.329819817795 54.527991490197
72.016191957094,17.329881165520 54.528019847644
72.016191957094,17.330710385546 54.527873240364 72.015426610430))') ,
4326),25833)) schw WHERE ST_DWithin(ST_Transform(s.the_geom,25833), schw ,
300) AND s.unix_time BETWEEN 1685451249.174999952316 AND
1685451309.174999952316 ORDER BY
ST_Distance(ST_Transform(s.the_geom,25833), schw) LIMIT 1)