bad JIT decision

From: Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: bad JIT decision
Date: 2020-07-24 20:46:06
Message-ID: 7736C40E-6DB5-4E7A-8FE3-4B2AB8E22793@elevated-dev.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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_ribe(at)elevated-dev(dot)com
https://www.linkedin.com/in/scottribe/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2020-07-24 21:09:33 Re: Row estimates for empty tables
Previous Message Ted Toth 2020-07-24 20:40:58 Re: when is RLS policy applied