I have come across a case where PG 12 with default JIT settings makes a dramatically bad decision. PG11 without JIT,
executesthe query in <1ms, PG12 with JIT takes 7s--and explain analyze attributes all that time to JIT. (The plan is
thesame on both 11 & 12, it's just the JIT.)
It is a complex query, with joins to subqueries etc; there is a decent amount of data (~50M rows), and around 80
partitions(by date) on the main table. The particular query that I'm testing is intended as a sort of base case, in
thatit queries on a small set (4) of unique ids which will not match any rows, thus the complex bits never get
executed,and this is reflected in the plan, where the innermost section is:
-> Index Scan using equities_rds_id on equities e0 (cost=0.42..33.74 rows=1 width=37) (actual time=6751.892..6751.892
rows=0loops=1)
Index Cond: (rds_id = ANY ('{..., ..., ..., ...}'::uuid[]))
Filter: (security_type = 'ETP'::text)
Rows Removed by Filter: 4
And that is ultimately followed by a couple of sets of 80'ish scans of partitions, which show never executed, pretty
muchas expected since there are no rows left to check. The final bit is:
JIT:
Functions: 683
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 86.439 ms, Inlining 21.994 ms, Optimization 3900.318 ms, Emission 2561.409 ms, Total 6570.161 ms
Now I think the query is not so complex that there could possibly be 683 distinct functions. I think this count must be
theresult of a smaller number of functions created per-partition. I can understand how that would make sense, and some
testingin which I added conditions that would restrict the matches to a single partition seem to bear it out (JIT
reports79 functions in that case).
Given the magnitude of the miss in using JIT here, I am wondering: is it possible that the planner does not properly
takeinto account the cost of JIT'ing a function for multiple partitions? Or is it that the planner doesn't have enough
infoabout the restrictiveness of conditions, and is therefore anticipating running the functions against a great many
rows?
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/