bad JIT decision - Mailing list pgsql-general

From Scott Ribe
Subject bad JIT decision
Date
Msg-id 7736C40E-6DB5-4E7A-8FE3-4B2AB8E22793@elevated-dev.com
Whole thread Raw
Responses Re: bad JIT decision
List pgsql-general
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/






pgsql-general by date:

Previous
From: Ted Toth
Date:
Subject: Re: when is RLS policy applied
Next
From: Tom Lane
Date:
Subject: Re: Row estimates for empty tables