psql execution plan interpretation, Time spike compare to Cost (no spike)

Question

could you help me to interpret a psql execution plan, i'm a java developer, rather then DB engineer and can't get the below.

I have a big sql (the actual sql is not that important i guess), but it form is

SELECT DISTINCT someFields
FROM (huge inner select)
ORDER BY someField
LIMIT 1000 OFFSET 0

I've made an execution plan to the request and have received the following plan:

     Node Type: "Limit"
     Startup Cost: 1281955.01 - here the same Cost as in Unique step
     Total Cost: 1281960.45
     Actual Startup Time: 218620.633 - this 
     Actual Total Time: 218630.771.  - and this changed dramatically compared to previous step, while the Costs, almost not changed, why so?
     Actual Rows: 93
     Actual Loops: 1
       - Node Type: "Unique"
         Startup Cost: 1281955.01
         Total Cost: 1281960.45
         Actual Startup Time: 4000.387 - compared to this
         Actual Total Time: 4010.517.  - and this
         Actual Rows: 93
         Actual Loops: 1
           - Node Type: "Sort"
             Startup Cost: 1281955.01
             Total Cost: 1281955.26
             Actual Startup Time: 4000.380
             Actual Total Time: 4010.420
             Actual Rows: 93
             Actual Loops: 1
...
then the costs and Time are moving in the same direction without spikes and big differences
...
   JIT:
     Worker Number: -1
     Functions: 10479
     Options:
       Inlining: true
       Optimization: true
       Expressions: true
       Deforming: true
     Timing:
       Generation: 2737.565
       Inlining: 339.475
       Optimization: 124066.840
       Emission: 91849.576
       Total: 218993.455
   Execution Time: 221597.690

And what i'm confused is the huge spike in 'Time' (from 4000.380 to 218620.633) for the Node Type: Limit, but the absence of the same spike for the Cost (i supposed, maybe i'm wrong) that they are sort of the same value, but shown in different units

Is this a normal behaviour for postgres, or it shows me that smth strange happening?

I've assumed that my issue is the Limit step, which increases processing time on 214 seconds Therefore i've tried to remove the LIMIT from the query, but it didn't help, so i'm bit confused what this spike means and how to interpet it.

Thank you.

Answer 1

JIT is taking pretty much all the time for that query, and the time it takes fully explains the gap in actual startup times between the "Unique" and the "Limit". I don't know why the gap shows up in that unintuitive spot in the plan, but weirdness is normal when JIT is involved. Just turn jit off. If you can't get your DBA to turn it off server-wide, you can at least turn it off just for your own user:

alter user your_name_here set jit = off;

(Which will take affect next time you log out and back in)