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=64000000 width=24) (actual time=1062.753..285758.085 rows=8000 loops=1)
-> Incremental Sort (cost=10170.87..89363004.90 rows=640000000 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=640000000 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=80000 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=64000000)
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=64000000)
-> Hash (cost=0.11..0.11 rows=10 width=32) (actual time=0.002..0.002 rows=1 loops=64000000)
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=64000000)
Planning Time: 1.023 ms
Execution Time: 285758.551 ms
EXPLAIN (2)
===========
SET enable_incremental_sort = off;
HashAggregate (cost=97605201.00..113245201.00 rows=64000000 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=640000000 width=24)
Hash Cond: (s_1.token = s.token)
-> Nested Loop (cost=250.00..2005.00 rows=80000 width=40)
-> Seq Scan on "BDN_Terretories" (cost=0.00..155.00 rows=8000 width=12)
-> Function Scan on unnest s_1 (cost=250.00..250.10 rows=10 width=32)
-> Hash (cost=1996.00..1996.00 rows=80000 width=48)
-> Nested Loop (cost=250.00..1996.00 rows=80000 width=48)
-> Seq Scan on "BDN_EmployeeTerritories" (cost=0.00..146.00 rows=8000 width=20)
-> Function Scan on unnest s (cost=250.00..250.10 rows=10 width=32)
EXPLAIN (3)
===========
Unique (cost=76139.64..76219.64 rows=8000 width=24) (actual time=38.034..39.457 rows=8000 loops=1)
Output: "BDN_EmployeeTerritories"."BDN_EmployeeTerritories_ID", "BDN_Terretories"."BDN_Terretories_ID", "BDN_EmployeeTerritories"."Reference_Date"
-> Sort (cost=76139.64..76159.64 rows=8000 width=24) (actual time=38.033..38.302 rows=8000 loops=1)
Output: "BDN_EmployeeTerritories"."BDN_EmployeeTerritories_ID", "BDN_Terretories"."BDN_Terretories_ID", "BDN_EmployeeTerritories"."Reference_Date"
Sort Key: "BDN_EmployeeTerritories"."BDN_EmployeeTerritories_ID", "BDN_Terretories"."BDN_Terretories_ID", "BDN_EmployeeTerritories"."Reference_Date"
Sort Method: quicksort Memory: 693kB
-> Hash Join (cost=1846.01..75621.01 rows=8000 width=24) (actual time=18.806..36.903 rows=8000 loops=1)
Output: "BDN_EmployeeTerritories"."BDN_EmployeeTerritories_ID", "BDN_Terretories"."BDN_Terretories_ID", "BDN_EmployeeTerritories"."Reference_Date"
Hash Cond: (s_1.token = s.token)
-> Nested Loop (cost=0.01..1755.01 rows=8000 width=40) (actual time=0.057..15.617 rows=8000 loops=1)
Output: "BDN_Terretories"."BDN_Terretories_ID", s_1.token
-> Seq Scan on public."BDN_Terretories" (cost=0.00..155.00 rows=8000 width=12) (actual time=0.026..0.789 rows=8000 loops=1)
Output: "BDN_Terretories"."BDN_Terretories_ID", "BDN_Terretories"."Title", "BDN_Terretories"."Description", "BDN_Terretories"."Reference_Date", "BDN_Terretories"."TerretoryID", "BDN_Terretories"."EMP_TerretoryID", "BDN_Terretor
ies"."RegionID_545", "BDN_Terretories"."xpl_isUpdated"
-> Function Scan on pg_catalog.unnest s_1 (cost=0.01..0.11 rows=10 width=32) (actual time=0.001..0.001 rows=1 loops=8000)
Output: s_1.token
Function Call: unnest(string_to_array("BDN_Terretories"."EMP_TerretoryID", ';'::text))
-> Hash (cost=1746.01..1746.01 rows=8000 width=48) (actual time=18.733..18.734 rows=8000 loops=1)
Output: "BDN_EmployeeTerritories"."BDN_EmployeeTerritories_ID", "BDN_EmployeeTerritories"."Reference_Date", s.token
Buckets: 8192 Batches: 1 Memory Usage: 477kB
-> Nested Loop (cost=0.01..1746.01 rows=8000 width=48) (actual time=0.020..16.994 rows=8000 loops=1)
Output: "BDN_EmployeeTerritories"."BDN_EmployeeTerritories_ID", "BDN_EmployeeTerritories"."Reference_Date", s.token
-> Seq Scan on public."BDN_EmployeeTerritories" (cost=0.00..146.00 rows=8000 width=20) (actual time=0.011..0.826 rows=8000 loops=1)
Output: "BDN_EmployeeTerritories"."BDN_EmployeeTerritories_ID", "BDN_EmployeeTerritories"."Title", "BDN_EmployeeTerritories"."Description", "BDN_EmployeeTerritories"."Reference_Date", "BDN_EmployeeTerritories"."EMP_Terret
oryID", "BDN_EmployeeTerritories"."EmployeeID", "BDN_EmployeeTerritories"."xpl_isUpdated"
-> Function Scan on pg_catalog.unnest s (cost=0.01..0.11 rows=10 width=32) (actual time=0.002..0.002 rows=1 loops=8000)
Output: s.token
Function Call: unnest(string_to_array("BDN_EmployeeTerritories"."EMP_TerretoryID", ';'::text))
Planning Time: 1.055 ms
Execution Time: 39.810 ms
