PostgreSQL prepared statements in PL/pgSQL

\set timing on
Timing is on.
create table demo(id bigint primary key, quantity int);
CREATE TABLE
Time: 6.354 ms
insert into demo(id, quantity)
select generate_series, 42
from generate_series(1,1e7);
INSERT 0 10000000
Time: 139046.469 ms (02:19.046)

statement in PL/pgSQL loop

do $$ declare o int; begin
-- static statement execution
for i in 1..1e7 loop
select max(quantity) from demo
where id=i
into o;
end loop;
end; $$;
DO
Time: 157222.613 ms (02:37.223)
explain analyze
select max(quantity) from demo
where id=42;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8.46..8.46 rows=1 width=4) (actual time=0.046..0.046 rows=1 loops=1)
-> Index Scan using demo_pkey on demo (cost=0.43..8.45 rows=1 width=4) (actual time=0.041..0.041 rows=1 loops=1)
Index Cond: (id = 42)
Planning Time: 0.242 ms
Execution Time: 0.071 ms

prepared statement in PL/pgSQL

do $$ declare o int; begin
-- prepared statement - doesn't work
prepare q(int) as
select max(quantity) from demo
where id=$1
;
for i in 1..1e7 loop
execute q(i) into o;
end loop;
deallocate q;
end; $$;
ERROR: function q(integer) does not exist
LINE 1: q(i)
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: q(i)
CONTEXT: PL/pgSQL function inline_code_block line 8 at EXECUTE
Time: 1.170 ms
deallocate q;
DEALLOCATE
Time: 0.072 ms
do $$ declare o int; begin
-- prepared statement dynamic execution
prepare q(int) as
select max(quantity) from demo
where id=$1
;
for i in 1..1e7 loop
execute format('execute q(%s)',i) into o;
end loop;
deallocate q;
end; $$;
DO
Time: 268508.538 ms (04:28.509)

dynamic statement

do $$ declare o int; begin
-- dynamic statement execution
for i in 1..1e7 loop
execute format('
select max(quantity) from %I
where id=$1
','demo') using i into o;
end loop;
end; $$;
DO
Time: 1187668.838 ms (19:47.669)
do $$ declare o int; begin
-- dynamic prepared statement and execution
execute format('
prepare q(int) as
select max(quantity) from %I where id=$1;
','demo');
for i in 1..1e7 loop
execute format('execute q(%s)',i) into o;
end loop;
deallocate q;
end; $$;
DO
Time: 256044.967 ms (04:16.045)

Developer Advocate at Yugabyte, Open Source distributed SQL database 🚀 Also Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Comprehensive Guide On Oracle Netsuite ERP In 8 Quick Facts

Android is SOLID

Introduction to Docker

How to Choose the Right Software Vendor for Your Business

Cross-Compiling C++ Projects with dockcross

A new approach to an old-school API

Develop zero trust microservices on Kubernetes and Istio locally with Tilt (Part 4)

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Franck Pachot

Franck Pachot

Developer Advocate at Yugabyte, Open Source distributed SQL database 🚀 Also Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member

More from Medium

How to access private Git repositories during a Docker image build

Filtering Azure Event Hubs events with low latency

Setup PyFlink Development Environment

Setting up Apache Airflow on Kubernetes with GitSync