postgresql-flat-vs-nested-rows.sql

-- Copyright Data Geekery GmbH
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--     http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
DO $$
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT INT := 1000000;
  rec RECORD;
BEGIN

  -- Repeat the whole benchmark several times to avoid warmup penalty
  FOR r IN 1..5 LOOP
    v_ts := clock_timestamp();

    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        -- Paste statement 1 here
        SELECT 1, 2, 3, 4
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    RAISE INFO 'Run %, Statement 1: %', r, (clock_timestamp() - v_ts);
    v_ts := clock_timestamp();

    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        -- Paste statement 2 here
        SELECT 1, ROW(2, ROW(3, ROW(4)))
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    RAISE INFO 'Run %, Statement 2: %', r, (clock_timestamp() - v_ts);
    RAISE INFO '';
  END LOOP;

  RAISE INFO '';
  RAISE INFO 'Copyright Data Geekery GmbH';
  RAISE INFO 'https://www.jooq.org/benchmark';
END$$;

Result on my machine on:

PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Run 1, Statement 1: 00:00:01.735633
Run 1, Statement 2: 00:00:01.495476

Run 2, Statement 1: 00:00:01.747938
Run 2, Statement 2: 00:00:01.494524

Run 3, Statement 1: 00:00:01.744375
Run 3, Statement 2: 00:00:01.494599

Run 4, Statement 1: 00:00:01.740397
Run 4, Statement 2: 00:00:01.499033

Run 5, Statement 1: 00:00:01.746151
Run 5, Statement 2: 00:00:01.498058


Copyright Data Geekery GmbH
https://www.jooq.org/benchmark

The nested row version is consistently faster (!)