In the previous tip GENERATE A RANDOM TIMESTAMP BETWEEN TWO TIMESTAMPS, we learned how to create a function to do just that.
In that version of the function we used the PLPGSQL language.
In YugabyteDB, sometimes we can get better performance if a function is called many times if it’s defined using the SQL language instead.
Let’s see why.
First, here is the DDL for the new function which uses the SQL language to generate a random TIMESTAMP bewteen two other TIMESTAMPs:
CREATE OR REPLACE FUNCTION randomtimestamp(TIMESTAMP, TIMESTAMP)
RETURNS TIMESTAMP AS
$$
SELECT $1::TIMESTAMP + random() * ($2::TIMESTAMP - $1::TIMESTAMP);
$$ language sql;
Example:
yugabyte=# CREATE OR REPLACE FUNCTION randomtimestamp(TIMESTAMP, TIMESTAMP)
yugabyte-# RETURNS TIMESTAMP AS
yugabyte-# $$
yugabyte$# SELECT $1::TIMESTAMP + random() * ($2::TIMESTAMP - $1::TIMESTAMP);
yugabyte$# $$ language sql;
CREATE FUNCTION
yugabyte=# SELECT randomtimestamp('01/01/2023', '12/31/2023') rand_ts;
rand_ts
----------------------------
2023-02-05 08:09:55.146292
(1 row)
To figure out why one version is “better” than the other, let’s create two versions of the function, one using PLPGSQL and the other SQL.
yugabyte=# CREATE OR REPLACE FUNCTION randomtimestamp_plpgsql(TIMESTAMP, TIMESTAMP)
yugabyte-# RETURNS TIMESTAMP AS
yugabyte-# $$
yugabyte$# DECLARE
yugabyte$# ts1 TIMESTAMP:=$1;
yugabyte$# ts2 TIMESTAMP:=$2;
yugabyte$# BEGIN
yugabyte$# RETURN ts1 + random() * (ts2 - ts1);
yugabyte$# END;
yugabyte$# $$ language 'plpgsql';
CREATE FUNCTION
yugabyte=# CREATE OR REPLACE FUNCTION randomtimestamp_sql(TIMESTAMP, TIMESTAMP)
yugabyte-# RETURNS TIMESTAMP AS
yugabyte-# $$
yugabyte$# SELECT $1::TIMESTAMP + random() * ($2::TIMESTAMP - $1::TIMESTAMP);
yugabyte$# $$ language sql;
CREATE FUNCTION
Next, we can look at the EXPLAIN PLAN for each.
yugabyte=# EXPLAIN (ANALYZE, DIST) SELECT randomtimestamp_plpgsql('01/01/2023', '12/31/2023') rand_ts;
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.26 rows=1 width=8) (actual time=1.048..1.049 rows=1 loops=1)
Planning Time: 0.017 ms
Execution Time: 2.011 ms
Storage Read Requests: 0
Storage Write Requests: 0
Catalog Read Requests: 4
Catalog Read Execution Time: 4.000 ms
Catalog Write Requests: 0
Storage Flush Requests: 0
Storage Execution Time: 4.000 ms
Peak Memory Usage: 56 kB
(11 rows)
yugabyte=# EXPLAIN (ANALYZE, DIST) SELECT randomtimestamp_sql('01/01/2023', '12/31/2023') rand_ts;
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)
Planning Time: 0.061 ms
Execution Time: 0.024 ms
Storage Read Requests: 0
Storage Write Requests: 0
Catalog Read Requests: 2
Catalog Read Execution Time: 2.000 ms
Catalog Write Requests: 0
Storage Flush Requests: 0
Storage Execution Time: 2.000 ms
Peak Memory Usage: 0 kB
(11 rows)
Notice that the number of catalog read request and the peak memory used for the PGPLSQL version is higher than the SQL version.
Although the differneces are small for a single call, they’ll add up if called a million times!
yugabyte=# \o /dev/null
yugabyte=# \timing on
Timing is on.
yugabyte=# SELECT randomtimestamp_plpgsql('01/01/2023', '12/31/2023') rand_ts FROM generate_series(1, 1000000);
Time: 1460.594 ms (00:01.461)
yugabyte=# SELECT randomtimestamp_sql('01/01/2023', '12/31/2023') rand_ts FROM generate_series(1, 100000
0);
Time: 443.155 ms
Have Fun!
