Re: Distinct performance dropped by multiple times in v16
On 6/10/24 13:59, Vitaliy Litovskiy wrote: ) tbl2 on tbl1.token = tbl2.token Observations: 1. query runs for 4-5 seconds on v16 and less than a second on v15 2. in v16 it also goes downs to less than a second if 2.1 distinct is removed 2.2 unnest is removed. it is not really needed for this particular data but this query is autogenerated and unnest makes sense for other data 2.3 "order by token" is uncommented, this is my current way of fixing the problem I would really appreciate some feedback if that is expected behaviour and if there are better solutions The reason for this behaviour is simple: commit 3c6fc58 allowed using incremental_sort with DISTINCT clauses. So, in PostgreSQL 16 we have two concurrent strategies: 1. HashJoin + hashAgg at the end 2, NestLoop, which derives sort order from the index scan and planner can utilise IncrementalSort+Unique instead of full sort. Disabling Incremental Sort (see explain 2) we get good plan with HashJoin at the top. Now we can see, that HashJoin definitely cheaper according to total cost, but has a big startup cost. Optimiser compares cost of incremental cost and, compare to hash agg it is much cheaper because of a reason. Here we already have a couple of questions: 1. Why optimiser overestimates in such a simple situation. 2. Why in the case of big numbers of tuples incremental sort is better than hashAgg? Before the next step just see how optimiser decides in the case of correct prediction. I usually use the AQO extension for that. Executing the query twice with the AQO in 'learn' mode we have correct plannedrows number in each node of the plan and, as you can see in EXPLAIN 3, optimiser chooses good strategy. So, having correct predictions, optimiser ends up with optimal plan. Origins of overestimation lie in internals of the unnest, it is not obvious so far and may be discovered later. The reason, why optimiser likes NestLoop on big data looks enigmatic. Attempting to increase a cost of unnest routing from 1 to 1E5 we don't see any changes in decision. In my opinion, the key reason here may be triggered by unusual width of the JOIN result: Hash Join (cost=0.24..0.47 rows=10 width=0) In my opinion, the cost model can provide too low cost of the join and it is a reason why upper NestLoop looks better than HashJoin. I don't have any general recommendations to resolve this issue, but this case should be discovered by the core developers. [1] https://github.com/postgrespro/aqo -- regards, Andrei Lepikhov Postgres Professional EXPLAIN (1) === Unique (cost=10170.87..94163004.90 rows=6400 width=24) (actual time=1062.753..285758.085 rows=8000 loops=1) -> Incremental Sort (cost=10170.87..89363004.90 rows=64000 width=24) (actual time=1062.751..285756.251 rows=8000 loops=1) Sort Key: "BDN_EmployeeTerritories"."BDN_EmployeeTerritories_ID", "BDN_Terretories"."BDN_Terretories_ID", "BDN_EmployeeTerritories"."Reference_Date" Presorted Key: "BDN_EmployeeTerritories"."BDN_EmployeeTerritories_ID" Full-sort Groups: 250 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB -> Nested Loop (cost=0.52..29242165.28 rows=64000 width=24) (actual time=0.103..285748.981 rows=8000 loops=1) -> Index Scan using "PK_BDN_EmployeeTerritories" on "BDN_EmployeeTerritories" (cost=0.28..285.28 rows=8000 width=20) (actual time=0.030..9.174 rows=8000 loops=1) -> Nested Loop (cost=0.24..2855.24 rows=8 width=8) (actual time=18.264..35.716 rows=1 loops=8000) -> Seq Scan on "BDN_Terretories" (cost=0.00..155.00 rows=8000 width=12) (actual time=0.002..0.754 rows=8000 loops=8000) -> Hash Join (cost=0.24..0.47 rows=10 width=0) (actual time=0.003..0.003 rows=0 loops=6400) Hash Cond: (s.token = s_1.token) -> Function Scan on unnest s (cost=0.01..0.11 rows=10 width=32) (actual time=0.000..0.000 rows=1 loops=6400) -> Hash (cost=0.11..0.11 rows=10 width=32) (actual time=0.002..0.002 rows=1 loops=6400) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Function Scan on unnest s_1 (cost=0.01..0.11 rows=10 width=32) (actual time=0.001..0.001 rows=1 loops=6400) Planning Time: 1.023 ms Execution Time: 285758.551 ms EXPLAIN (2) === SET enable_incremental_sort = off; HashAggregate (cost=97605201.00..113245201.00 rows=6400 width=24) Group Key: "BDN_EmployeeTerritories"."BDN_EmployeeTerritories_ID", "BDN_Terretories"."BDN_Terretories_ID", "BDN_EmployeeTerritories"."Reference_Date" Planned Partitions: 256 -> Hash Join (cost=3246.00..7205201.00 rows=64000 width=24) Hash Cond: (s_1.token = s.token) -> Nested Loop (cost=250.00..2005.00 rows=8 width=40) -> Seq Scan o
Postgresql initialize error
Hi Team, I'm installing postgresql 14 version by using Rpm. However i'm getting error while execute the database initialzation. Please check below error message Error; Hi Team, I'm installing postgresql 14 version by using Rpm. However i'm getting error while execute the database initialzation. Please check below error message Thanks, Nikhil, PostgreSQL DBA.
Re: Postgresql initialize error
Error ; ./ initdbs error while loading shared libraries: libssl.so.1.1: cannot open shared object file: No such file or directory On Wed, 12 Jun 2024 at 1:28 AM, nikhil kumar wrote: > Hi Team, > > I'm installing postgresql 14 version by using Rpm. However i'm getting > error while execute the database initialzation. Please check below error > message > > Error; > Hi Team, I'm installing postgresql 14 version by using Rpm. However i'm > getting error while execute the database initialzation. Please check below > error message > > Thanks, > Nikhil, > PostgreSQL DBA. > >
Re: Postgresql initialize error
Greetings, Check OpenSSL installed on your system openssl version If not installed sudo apt-get install openssl Find library sudo find / -name libssl.so.1.1 Add in Library path export LD_LIBRARY_PATH=/path/to/libssl:$LD_LIBRARY_PATH *Salahuddin (살라후딘**)* On Wed, 12 Jun 2024 at 00:59, nikhil kumar wrote: > Error ; ./ initdbs error while loading shared libraries: libssl.so.1.1: > cannot open shared object file: No such file or directory > > > On Wed, 12 Jun 2024 at 1:28 AM, nikhil kumar > wrote: > >> Hi Team, >> >> I'm installing postgresql 14 version by using Rpm. However i'm getting >> error while execute the database initialzation. Please check below error >> message >> >> Error; >> Hi Team, I'm installing postgresql 14 version by using Rpm. However i'm >> getting error while execute the database initialzation. Please check below >> error message >> >> Thanks, >> Nikhil, >> PostgreSQL DBA. >> >>
Re: Postgresql initialize error
Thanks for your support. I’ll check it On Wed, 12 Jun 2024 at 1:34 AM, Muhammad Salahuddin Manzoor < [email protected]> wrote: > Greetings, > > Check OpenSSL installed on your system > openssl version > > If not installed > sudo apt-get install openssl > > Find library > sudo find / -name libssl.so.1.1 > > Add in Library path > export LD_LIBRARY_PATH=/path/to/libssl:$LD_LIBRARY_PATH > > *Salahuddin (살라후딘**)* > > > > On Wed, 12 Jun 2024 at 00:59, nikhil kumar wrote: > >> Error ; ./ initdbs error while loading shared libraries: libssl.so.1.1: >> cannot open shared object file: No such file or directory >> >> >> On Wed, 12 Jun 2024 at 1:28 AM, nikhil kumar >> wrote: >> >>> Hi Team, >>> >>> I'm installing postgresql 14 version by using Rpm. However i'm getting >>> error while execute the database initialzation. Please check below error >>> message >>> >>> Error; >>> Hi Team, I'm installing postgresql 14 version by using Rpm. However i'm >>> getting error while execute the database initialzation. Please check below >>> error message >>> >>> Thanks, >>> Nikhil, >>> PostgreSQL DBA. >>> >>>
Re: Postgresql initialize error
As I check we have OpenSSL package in that server but that file is not visible On Wed, 12 Jun 2024 at 1:35 AM, nikhil kumar wrote: > Thanks for your support. I’ll check it > > On Wed, 12 Jun 2024 at 1:34 AM, Muhammad Salahuddin Manzoor < > [email protected]> wrote: > >> Greetings, >> >> Check OpenSSL installed on your system >> openssl version >> >> If not installed >> sudo apt-get install openssl >> >> Find library >> sudo find / -name libssl.so.1.1 >> >> Add in Library path >> export LD_LIBRARY_PATH=/path/to/libssl:$LD_LIBRARY_PATH >> >> *Salahuddin (살라후딘**)* >> >> >> >> On Wed, 12 Jun 2024 at 00:59, nikhil kumar >> wrote: >> >>> Error ; ./ initdbs error while loading shared libraries: libssl.so.1.1: >>> cannot open shared object file: No such file or directory >>> >>> >>> On Wed, 12 Jun 2024 at 1:28 AM, nikhil kumar >>> wrote: >>> Hi Team, I'm installing postgresql 14 version by using Rpm. However i'm getting error while execute the database initialzation. Please check below error message Error; Hi Team, I'm installing postgresql 14 version by using Rpm. However i'm getting error while execute the database initialzation. Please check below error message Thanks, Nikhil, PostgreSQL DBA.
RE: Postgresql initialize error
Just be safe over sorry install openssl and openssl-dev packages. You may want to run updatedb, if you have the locate package installed just to double verify [cid:[email protected]] Travis Smith VP Business Technology Circana From: nikhil kumar Sent: Tuesday, June 11, 2024 3:23 PM To: Muhammad Salahuddin Manzoor Cc: [email protected] Subject: Re: Postgresql initialize error ***ATTENTION!! This message originated from outside of Circana. Treat hyperlinks and attachments in this email with caution.*** As I check we have OpenSSL package in that server but that file is not visible On Wed, 12 Jun 2024 at 1:35 AM, nikhil kumar mailto:[email protected]>> wrote: Thanks for your support. I’ll check it On Wed, 12 Jun 2024 at 1:34 AM, Muhammad Salahuddin Manzoor mailto:[email protected]>> wrote: Greetings, Check OpenSSL installed on your system openssl version If not installed sudo apt-get install openssl Find library sudo find / -name libssl.so.1.1 Add in Library path export LD_LIBRARY_PATH=/path/to/libssl:$LD_LIBRARY_PATH Salahuddin (살라후딘) On Wed, 12 Jun 2024 at 00:59, nikhil kumar mailto:[email protected]>> wrote: Error ; ./ initdbs error while loading shared libraries: libssl.so.1.1: cannot open shared object file: No such file or directory On Wed, 12 Jun 2024 at 1:28 AM, nikhil kumar mailto:[email protected]>> wrote: Hi Team, I'm installing postgresql 14 version by using Rpm. However i'm getting error while execute the database initialzation. Please check below error message Error; Hi Team, I'm installing postgresql 14 version by using Rpm. However i'm getting error while execute the database initialzation. Please check below error message Thanks, Nikhil, PostgreSQL DBA. REMINDER: This message came from an external source. Please exercise caution when opening any attachments or clicking on links.
