BUG #18306: Slow SQL query Postgresql 15 Debian family linux.
От | PG Bug reporting form |
---|---|
Тема | BUG #18306: Slow SQL query Postgresql 15 Debian family linux. |
Дата | |
Msg-id | 18306-204823ef42dc78ea@postgresql.org обсуждение исходный текст |
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 18306 Logged by: Przemysław Szustak Email address: pszustak@lehmann-partner.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)
В списке pgsql-bugs по дате отправления:
Предыдущее
От: "Hayato Kuroda (Fujitsu)"Дата:
Сообщение: RE: BUG #18280: logical decoding build wrong snapshot for subtransactions
Следующее
От: Tom LaneДата:
Сообщение: Re: BUG #18305: Unexpected error: "WindowFunc not found in subplan target lists" triggered by subqueries