There are going to be SQL commands that you issue repeatedly. To avoid typing them every time you need them, you can use variables in YSQLSH to store the code, then use the variable in their place in your SQL.
Example:
While in the process of tuning a query, you are going to have to request an Explain Plan over and over again. We can define a variable to store the explain plan command text, and then use the variable instead of typing in the command!
yugabyte=# CREATE TABLE ea_test1 (c1 INT PRIMARY KEY, c2 VARCHAR);
CREATE TABLE
yugabyte=# CREATE TABLE ea_test2 (c1 INT PRIMARY KEY, c2 VARCHAR);
CREATE TABLE
yugabyte=# INSERT INTO ea_test1 VALUES (1, 'A'), (2, 'B'), (3, 'C');
INSERT 0 3
yugabyte=# INSERT INTO ea_test2 VALUES (1, 'X'), (2, 'Y'), (3, 'Z');
INSERT 0 3
yugabyte=# EXPLAIN (ANALYZE, COSTS OFF, DIST) SELECT * FROM ea_test1 JOIN ea_test2 USING (c1);
QUERY PLAN
--------------------------------------------------------------------------------------------
Nested Loop (actual time=2.407..4.900 rows=3 loops=1)
-> Seq Scan on ea_test1 (actual time=1.289..2.090 rows=3 loops=1)
Storage Table Read Requests: 3
Storage Table Read Execution Time: 1.681 ms
-> Index Scan using ea_test2_pkey on ea_test2 (actual time=0.910..0.910 rows=1 loops=3)
Index Cond: (c1 = ea_test1.c1)
Storage Table Read Requests: 1
Storage Table Read Execution Time: 0.813 ms
Planning Time: 0.353 ms
Execution Time: 4.995 ms
Storage Read Requests: 6
Storage Read Execution Time: 4.120 ms
Storage Write Requests: 0
Catalog Read Requests: 2
Catalog Read Execution Time: 2.461 ms
Catalog Write Requests: 0
Storage Flush Requests: 0
Storage Execution Time: 6.580 ms
Peak Memory Usage: 56 kB
(19 rows)
Now I’ll define a variable for future explain plan requests using the \set meta-command.
yugabyte=# \set ea 'EXPLAIN (ANALYZE, COSTS OFF, DIST)'
yugabyte=# :ea SELECT * FROM ea_test1 JOIN ea_test2 USING (c1);
QUERY PLAN
--------------------------------------------------------------------------------------------
Nested Loop (actual time=1.850..3.761 rows=3 loops=1)
-> Seq Scan on ea_test1 (actual time=1.046..1.847 rows=3 loops=1)
Storage Table Read Requests: 3
Storage Table Read Execution Time: 1.661 ms
-> Index Scan using ea_test2_pkey on ea_test2 (actual time=0.623..0.623 rows=1 loops=3)
Index Cond: (c1 = ea_test1.c1)
Storage Table Read Requests: 1
Storage Table Read Execution Time: 0.553 ms
Planning Time: 0.152 ms
Execution Time: 3.837 ms
Storage Read Requests: 6
Storage Read Execution Time: 3.320 ms
Storage Write Requests: 0
Catalog Read Requests: 0
Catalog Write Requests: 0
Storage Flush Requests: 0
Storage Execution Time: 3.320 ms
Peak Memory Usage: 56 kB
(18 rows)
Have Fun!