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/
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 |