How we sped up a Postgresql procedure 50X
Introduction What I'm about to explain may seem rudimentary to a DBA but it was over looked by our distinguished engineer. And once you learn the inner workings it's a "duh" kinda moment where you look back and wonder why you didn't see it. What is a Postgres procedure Let's start with the basics. There are postgresql functions and procedures. The major differences between the two is that functions returns a result while a procedure does not. In addition to the return differences, you Select a function but Call a procedure. Diving into the specifics, a function is almost like a function in any other programming language. It takes inputs and can manipulate it or use it in a sql statement. Adding two numbers that are fed as inputs: 1 2 3 4 5 6 7 CREATE FUNCTION add_numbers (first_number integer , second_number integer ) RETURNS integer LANGUAGE SQL IMMUTABLE AS $$ SELECT $ 1 + $ 2 ; $$ ; Taking two inputs and inserting