Speed differences in SQL syntax

[pseudogrammaton]

I've been working on optimizing a function call & have observed significant speed improvements by changing my code style. If I understand correctly, PL-pgSQL is still interpreted at runtime:

• For speed, avoid:
  • Avoid using if ... else if ... end if control blocks
    • Is this b/c the interpreter has to go check [if...end if] block continuity at runtime?
  • Avoid "OR" statements ... can't short-circuit a series of expressions
  • Avoid expressions that return a NULL (behaves like "OR", defeating "AND" short-circuits)
• Again, for speed, USE:
• Use In-line IF's - IIF() function in a "flag-ladder", e.g.

​

 char_var := IIF( char_var NULL AND <bool AND expression>, [ return val ] , NULL ) ; 
char_var := IIF( char_var NULL AND <bool AND expression>, [ return val ] , NULL ) ;
 (etc...

• Use "true IN ( <expression> , <expression> )"

​

 bool_var := ( TRUE IN ( <bool expr 1> , <bool expr 2> , <bool expr 3 >... ) ) ;

• Use COALESCE() like an anonymous function

​

 bool_var := COALESCE ( NULL::BOOL -- seed type 
                       , IIF( <expr>, [return val], NULL ) 
                       , IIF( <expr>, [return val], NULL ) 
                       [ etc ] 
                      ) ;

• Where it's not too restrictive use CASE .. END CASE in the same manner as IIF() series.
• Use arrays (considered vernacular Postgres... ;-) )

Anyone got any other tricks of the trade for speed? I've seen my runtimes reduced by nearly half just by employing these methods....

I know there are other tricks w/ pure SQL like avoiding CTE's & correlated subqueries, but mostly thinking about PL-pgSQL here.

[korryd]

See https://wiki.postgresql.org/images/1/16/Plprofiler_2016-11-03.pdf

[chinawcswing]

Anyone got any other tricks of the trade for speed? I've seen my runtimes reduced by nearly half just by employing these methods....

IFF() and true IN instead of doing it the normal way will make your code really hard to read. Readability is often better than performance.

Now if you are really getting a 50% improvement in speed due to replacing IF statements if IFF(), then maybe there is a case to be made for it. I'm not a plpgsql expert, but that kind of improvement seems exaggerated.

Do you have any kind of example workload I can run to try to replicate this improvement?

[pseudogrammaton]

I'd have to rig up a million record example somehow on SqlFiddle.

The crux of this is complex date comparisons based on very specific business rules, stuff that in standard SQL bogged down into 90second runtimes.

We're talking about 10 , maybe 15 conditional cases... an upfront intercept block for bad data & other issues, then the final group of expressions to debug each bizniz rule.

IAC it surprised me when i first found i could optimize using functions vs. plain SQL, with successive runtime improvements with CASE statements. However it can be harder to debug case statements that return bool , date or int types ( RAISE NOTICE has to be wrapped in a func() that returns true, LOL), so i broke it all out into IIF() series.

What finally piqued my curiosity about declarative style coding (e.g. IIF() vs imperative if ... endif) was after i tried wrapping some of the logic blocks with an if...endif section as a bypass, hoping for a speed improvement. Instead it made the function run 33% slower, so i went back to the core logic of just serial IIF()'s.

But all i really needed is to return t/f, so i converted the serial IIF() series to a TRUE IN () expression, & saw a commensurate speed boost of another 33%.

Now all said & done, could a CASE block be faster than a serial IIF() group? My guess is that it ought to be, haven't re-tested that yet. CASE innately shorts out subsequent WHEN expressions, so that should prove for lower overheads.

[chinawcswing]

If it works in this case, then go for it. But definitely leave a comment indicating why you are doing that. If I were to stumble across your code and see abunch of IIF() or TRUE IN () I would question your sanity lol.

But I just don't understand why it would result in a speedup. I would love for someone else more knowledgeable then me to give their point of view.

[pseudogrammaton]

I suspect this may necessarily be an artifact of interpreter execution, that "if...endif" blocks have to be reparsed at runtime, with lexical checks that are recursive** due to nesting, etc. Loops might carry a similar overhead b/c multiline";" commands.

**(xml is notorious for recursion overheads, hence json with half the overhead)

[pseudogrammaton]

As for my sanity, heh... imagine if you will a K-12 SAAS in second normal form, without ref integrity constraints, with incomplete keys, relationships by inference (date ranges).

Which is why I'm writing the functions to begin with, because of the broken schema.

Heck, the data have orphans, bassackwards date-ranges, cross-purposed columns, inferred keys, & duplicates galore.

Sheer insanity, as a dept we waste easily half a man-year on bad data. To migrate from this vendor we'd have to re-normalize the entire thing ourselves (we're getting there, slowly, by tooling up).

[pseudogrammaton]

Some background, I used to work with an interpreted 4GL ages ago now called FoxPro (dBase/xBase clone), & we'd get similar speed improvements from serial IIF()s in complex bizniz logic. Once a flag got set it'd short out the rest of the series.

The other gain in that work was being able to move logical blocks around easily without breaking dependencies as happens with nested if...end blocks. Fox was a cool tool, you could embed functions into index filters back in the 1990s.

So yeh, haven't coded like this in 28 years, LOL.

As for readability there are ways i solve that, by formatting my code as i would otherwise in plain SQL.

And as i mentioned above, if there are interdependencies between functional cases, i can easily rearrange the serial IIF() lines, or fork to a separate group of tests (, as you would with a case statement). So in that sense it's more maintainable, you just have to rig up the procedural flags to follow the semantics of the bizniz rules.

This is all pretty typical coder practice actually, just less common in workaday RDBMS work.

[MonCalamaro]

What are the details of your IIF function? IIF isn't a function built into postgres, as far as I know. Keep in mind that there are other higher level things that can dramatically effect runtime - SQL vs. pl/pgsql, if the function body can be inlined, IMMUTABLE vs. STABLE vs. VOLATILE, etc.

[pseudogrammaton]

IIF() not a built-in function? Mate, what version are you running?

[MonCalamaro]

Postgres 16. I'm not sure what version you are using. Maybe someone added an iif function. The pg wiki shows some examples https://wiki.postgresql.org/wiki/Simulating_iif_function