This article is half-done without your Comment! *** Please share your thoughts via Comment ***
This is an important article for PostgreSQL Developer because many of the database developers don’t know about the PostgreSQL Function Volatility.
Every function has a volatility classification, with the possibilities being VOLATILE, STABLE, or IMMUTABLE. VOLATILE is the default.
In this post, I am not going to write 1000 words on it. Because I believe in practical so sharing a good practical example here.
But you must read about the PostgreSQL Function Volatility from this official page.
Check the below practical example and you will get to know that how VOLATILE function is choosing the bad Query Execution Plan.
Create a sample function:
|
1 2 3 4 5 6 7 8 9 |
CREATE OR REPLACE FUNCTION fn_getvalues(int, int) RETURNS int AS $$ BEGIN RETURN CASE WHEN $1 = $2 THEN $1 ELSE $2 END; END; $$ LANGUAGE 'plpgsql'; |
Check the result of sample function:
|
1 2 3 4 5 |
SELECT * FROM fn_getvalues(50,80); fn_getvalues ----------------- 80 |
Create a sample table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE tbl_ItemTransactions ( TranID SERIAL ,TransactionDate TIMESTAMPTZ ,TransactionName TEXT ); CREATE INDEX idx_TranID ON tbl_ItemTransactions (TranID); INSERT INTO tbl_ItemTransactions (TransactionDate, TransactionName) SELECT x, 'dbrnd' FROM generate_series('2017-01-01 00:00:00'::timestamptz, '2018-01-31 00:00:00'::timestamptz,'50 seconds'::interval) a(x); |
Check the execution plan for below query:
You can find ‘Index Scan’ because the huge table and index applied properly.
|
1 2 3 4 5 6 |
EXPLAIN SELECT *FROM tbl_ItemTransactions WHERE TranID = 80 'Index Scan using idx_tranid on tbl_itemtransactions (cost=0.42..8.44 rows=1 width=18)' ' Index Cond: (tranid = 80)' |
Now, Use the early created function in the same query:
You can find ‘Seq Scan’ because the function of the filter is creating a problem. Because VOLATILE is the default volatility classification.
|
1 2 3 4 5 6 |
EXPLAIN SELECT *FROM tbl_ItemTransactions WHERE TranID = fn_getvalues (50,80); 'Seq Scan on tbl_itemtransactions (cost=0.00..183520.26 rows=1 width=18)' ' Filter: (tranid = fn_getvalues(50, 80))' |
Now, Let’s create an IMMUTABLE function:
|
1 2 3 4 5 6 7 8 9 |
CREATE OR REPLACE FUNCTION fn_getvalues(int, int) RETURNS int AS $$ BEGIN RETURN CASE WHEN $1 = $2 THEN $1 ELSE $2 END; END; $$ LANGUAGE 'plpgsql' IMMUTABLE; |
Check the same query again:
You can find ‘Index Scan’ which is expected and correct behaviour.
EXPLAIN
|
1 2 3 4 5 |
SELECT *FROM tbl_ItemTransactions WHERE TranID = fn_getvalues (50,80); 'Index Scan using idx_tranid on tbl_itemtransactions (cost=0.42..8.44 rows=1 width=18)' ' Index Cond: (tranid = 80)' |
Leave a Reply