Skip to content

Instantly share code, notes, and snippets.

@lukaseder
Created April 30, 2021 18:17
Show Gist options
  • Save lukaseder/b4c7660411489c89c066458700085481 to your computer and use it in GitHub Desktop.
Save lukaseder/b4c7660411489c89c066458700085481 to your computer and use it in GitHub Desktop.
-- 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$$;
@lukaseder
Copy link
Author

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 (!)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment