jsonb_benchmark.sql

-- 準備

CREATE EXTENSION plv8;

CREATE TABLE test (
  id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  value jsonb
);

CREATE OR REPLACE PROCEDURE populate_test_table()
LANGUAGE plv8 AS $$
  const plan = plv8.prepare('INSERT INTO test (value) VALUES ($1)', ['jsonb']);

  try {
    for (let i = 0; i < 10000; i++) {
      const value = {
        foo1: new Array(10).fill(0).map((_, outer) => ({
          foo2: new Array(10).fill(0).map((_, inner) => ({
            aaa: `${i} * ${outer} * ${inner}`,
            bbb: i * outer * inner,
          })),
        })),
      };

      plan.execute(value);
    }
  } finally {
    plan.free();
  }
$$;

CALL populate_test_table();

\timing

-- jsonb_array_elementsの繰り返し

SELECT
  SUM((foo2 -> 'bbb')::double precision)
FROM
  test,
  LATERAL jsonb_array_elements(value -> 'foo1') AS foo1,
  LATERAL jsonb_array_elements(foo1 -> 'foo2') AS foo2;


-- jsonb_path_query

SELECT
  SUM(v::double precision)
FROM
  test,
  LATERAL jsonb_path_query(value, '$.foo1[*].foo2[*].bbb') AS v;


-- plv8による方法その1

CREATE FUNCTION sum_test_json(val jsonb) RETURNS double precision AS $$
  let sum = 0.0;

  for (const foo1 of val.foo1) {
    for (const foo2 of foo1.foo2) {
      sum += foo2.bbb;
    }
  }

  return sum;
$$ LANGUAGE plv8 IMMUTABLE STRICT;

SELECT
  SUM(sum_test_json(value))
FROM
  test;

-- plv8による方法その2

CREATE OR REPLACE FUNCTION sum_test_json_all() RETURNS double precision AS $$
  const plan = plv8.prepare('SELECT value FROM test');

  try {
    const cursor = plan.cursor([]);

    try {
      let sum = 0.0;
      let row;

      while (row = cursor.fetch()) {
        const val = row.value;
        for (const foo1 of val.foo1) {
          for (const foo2 of foo1.foo2) {
            sum += foo2.bbb;
          }
        }
      }

      return sum;
    } finally {
      cursor.close();
    }
  } finally {
    plan.free();
  }
$$ LANGUAGE plv8 IMMUTABLE STRICT;

SELECT sum_test_json_all();