I have come across a case where PG 12 with default JIT settings makes a
dramatically bad decision. PG11 without JIT, executes the query in <1ms, PG12
with JIT takes 7s--and explain analyze attributes all that time to JIT. (The
plan is the same 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 that
it 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=0 loops=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 much as 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 the result of a smaller number
of functions created per-partition. I can understand how that would make sense,
and some testing in which I added conditions that would restrict the matches to
a single partition seem to bear it out (JIT reports 79 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 take into account the cost of
JIT'ing a function for multiple partitions? Or is it that the planner doesn't
have enough info about the restrictiveness of conditions, and is therefore
anticipating running the functions against a great many rows?
--
Scott Ribe
scott_r...@elevated-dev.com
https://www.linkedin.com/in/scottribe/
