Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- test1 with 1M rows and 2% NULL
CREATE TABLE test1(num1 numeric, num2 numeric);
INSERT INTO test1
SELECT round((random()* 1000)::numeric, 4)  --  4 fractional digits should be rasonalbe
     , CASE WHEN random() > 0.98 THEN NULL ELSE round((random()* 100)::numeric, 4) END  -- ~ 2 % NULL
FROM generate_series(1, 1000000) g;

-- test2 with 1M rows and 80% NULL
CREATE TABLE test2(num1 numeric, num2 numeric);
INSERT INTO test2
SELECT round((random()* 1000)::numeric, 4)
     , CASE WHEN random() > 0.80 THEN NULL ELSE round((random()* 100)::numeric, 4) END  -- ~ 80 % NULL
FROM generate_series(1, 1000000) g;

CREATE TABLE

INSERT 0 1000000

CREATE TABLE

INSERT 0 1000000

-- 4 test candidates
CREATE FUNCTION f_div0_pl_if(val1 numeric, val2 numeric)     
  RETURNS numeric AS
$func$
BEGIN
   IF $2 = 0 THEN
      RETURN NULL;
   ELSE
      RETURN $1 / $2;
   END IF; 
END
$func$  LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION f_div0_pl_nullif(val1 numeric, val2 numeric)     
  RETURNS numeric AS
$func$
BEGIN
   RETURN $1 / NULLIF($2, numeric '0');
END
$func$  LANGUAGE plpgsql IMMUTABLE; 

CREATE FUNCTION f_div0_sql_case(val1 numeric, val2 numeric)     
  RETURNS numeric LANGUAGE sql IMMUTABLE AS
$$SELECT CASE WHEN $2 = 0 THEN NULL ELSE $1 / $2 END$$;

CREATE FUNCTION f_div0_sql_nullif(val1 numeric, val2 numeric)     
  RETURNS numeric LANGUAGE sql IMMUTABLE AS
$$SELECT $1 / NULLIF($2, numeric '0')$$;

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_pl_if(num1, num2)      FROM test1;

QUERY PLAN

Seq Scan on test1  (cost=0.00..128450.52 rows=473352 width=32) (actual rows=1000000 loops=1)

Planning time: 0.507 ms

Execution time: 2315.196 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_pl_nullif(num1, num2)  FROM test1;

QUERY PLAN

Seq Scan on test1  (cost=0.00..128450.52 rows=473352 width=32) (actual rows=1000000 loops=1)

Planning time: 0.056 ms

Execution time: 2323.869 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_sql_case(num1, num2)   FROM test1;

QUERY PLAN

Seq Scan on test1  (cost=0.00..12479.28 rows=473352 width=32) (actual rows=1000000 loops=1)

Planning time: 0.113 ms

Execution time: 628.525 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_sql_nullif(num1, num2) FROM test1;

QUERY PLAN

Seq Scan on test1  (cost=0.00..12479.28 rows=473352 width=32) (actual rows=1000000 loops=1)

Planning time: 0.084 ms

Execution time: 562.069 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_pl_if(num1, num2)      FROM test2;

QUERY PLAN

Seq Scan on test2  (cost=0.00..124223.76 rows=457776 width=32) (actual rows=1000000 loops=1)

Planning time: 0.107 ms

Execution time: 2370.244 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_pl_nullif(num1, num2)  FROM test2;

QUERY PLAN

Seq Scan on test2  (cost=0.00..124223.76 rows=457776 width=32) (actual rows=1000000 loops=1)

Planning time: 0.045 ms

Execution time: 1738.551 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_sql_case(num1, num2)   FROM test2;

QUERY PLAN

Seq Scan on test2  (cost=0.00..12068.64 rows=457776 width=32) (actual rows=1000000 loops=1)

Planning time: 0.088 ms

Execution time: 474.315 ms

EXPLAIN

EXPLAIN (ANALYZE, TIMING OFF) SELECT f_div0_sql_nullif(num1, num2) FROM test2;

QUERY PLAN

Seq Scan on test2  (cost=0.00..12068.64 rows=457776 width=32) (actual rows=1000000 loops=1)

Planning time: 0.082 ms

Execution time: 447.350 ms

EXPLAIN