0

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.

Improve this question
5
  • Those of us who are accustomed to looking at EXPLAIN are probably not accustomed to that format. It would be better to show it in FORMAT TEXT, not FORMAT YAML
    – jjanes
    Commented Jun 27, 2023 at 16:01
  • Is this consistently reproducible? I can't reproduce anything like it.
    – jjanes
    Commented Jun 27, 2023 at 16:06
  • The thing is, that i have no free access to the DB, and i can't invoke it whenever i want, this is actually the reason why i use EXPLAIN my first time. But the request is reproducible, and is stably slow. Sorry that can't give more info.
    – Dmitriy Ten
    Commented Jun 27, 2023 at 18:58
  • 1
    Do you have JIT turned on? If so, try without it. JIT is generally more trouble than it is worth (by quite a bit), and I vaguely recall some of the problems do look like this--a high amount of time showing up in some seemingly random part of the plan. (But those those high times are also reported at the end of the plan, in the JIT specific section)
    – jjanes
    Commented Jun 27, 2023 at 19:19
  • I've added a JIT section to the yaml, could you look at it? Does it looks strange?
    – Dmitriy Ten
    Commented Jun 28, 2023 at 6:21

1 Answer 1

Reset to default
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)

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.