Hi. I was recently troubleshooting a function, and realized it had incorrectly been declared as Immutable, when it should have been declared Stable. When I changed it to Stable, the query I was running ran dramatically faster. Digging into this a little more, this is what I found:
--
![]()
I've got a function (staff_inspector) that takes two arguments, an integer and a date.
I've got a convenience function that takes just an integer, and fills in the date. (With a Stable function target_date()).
There is no performance difference between the two functions if both arguments are supplied.
If I use the convenience function however, the difference is dramatic. The Stable version clocks in around 1.3 seconds, and the immutable version at around 23 seconds.
So I'm wondering if this is expected behavior, and if someone could explain it to me. Also, if it is expected, I'm wondering whether it's worth noting in Section 36.6 ("Function Volatility Categories"), which led me to believe I might be risking a stale value by marking something as Immutable, but offers no hint about a ginormous performance penalty.
Here's some more detail and information.
I created two versions of the underlying function. (staff_inspector_stable,staff_inspector_imm). There is no noticeable performance difference between them. I created two versions of the convenience function, si_stable and si_imm. They are identical, except for being declared Stable or Immutable.
The first time I run any of these queries, they take about two seconds. All subsequent calls take about 1.3 seconds. Except the Immutable version, which clocks in at 20+ seconds. This is true whether it calls the Stable or Immutable version of the underlying function:
spc=> EXPLAIN ANALYZE SELECT client_id,staff_inspector_stable(client_id,target_date()) FROM tbl_residence_own;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_residence_own (cost=0.00..3734.27 rows=6977 width=8) (actual time=8.311..1990.601 rows=6983 loops=1)
Planning time: 1.976 ms
Execution time: 2001.247 ms
(3 rows)
spc=> EXPLAIN ANALYZE SELECT client_id,staff_inspector_stable(client_id,target_date()) FROM tbl_residence_own;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_residence_own (cost=0.00..3734.27 rows=6977 width=8) (actual time=3.472..1298.537 rows=6983 loops=1)
Planning time: 0.279 ms
Execution time: 1310.831 ms
(3 rows)
spc=> EXPLAIN ANALYZE SELECT client_id,staff_inspector_imm(client_id,target_date()) FROM tbl_residence_own;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_residence_own (cost=0.00..3734.27 rows=6977 width=8) (actual time=3.780..1299.082 rows=6983 loops=1)
Planning time: 0.308 ms
Execution time: 1311.379 ms
(3 rows)
spc=> EXPLAIN ANALYZE SELECT client_id,si_stable(client_id) FROM tbl_residence_own;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_residence_own (cost=0.00..3734.27 rows=6977 width=8) (actual time=3.145..1300.551 rows=6983 loops=1)
Planning time: 0.281 ms
Execution time: 1312.762 ms
(3 rows)
spc=> EXPLAIN ANALYZE SELECT client_id,si_imm(client_id) FROM tbl_residence_own;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_residence_own (cost=0.00..1990.02 rows=6977 width=8) (actual time=3.537..22892.481 rows=6983 loops=1)
Planning time: 0.079 ms
Execution time: 22903.504 ms
(3 rows)
spc=> EXPLAIN ANALYZE SELECT client_id,si_stable_calls_imm(client_id) FROM tbl_residence_own;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_residence_own (cost=0.00..3734.27 rows=6977 width=8) (actual time=2.907..1291.235 rows=6983 loops=1)
Planning time: 0.223 ms
Execution time: 1303.488 ms
(3 rows)
spc=> EXPLAIN ANALYZE SELECT client_id,si_imm_calls_imm(client_id) FROM tbl_residence_own;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on tbl_residence_own (cost=0.00..1990.02 rows=6977 width=8) (actual time=3.664..22868.734 rows=6983 loops=1)
Planning time: 0.134 ms
Execution time: 22879.761 ms
(3 rows)And then here is the definitions of the functions:
CREATE OR REPLACE FUNCTION staff_inspector_stable( client INTEGER, asof DATE ) RETURNS INTEGER AS $$
SELECT
CASE WHEN
(SELECT program_type_code FROM reg_spc WHERE asof BETWEEN reg_spc_date AND COALESCE(reg_spc_date_end,asof) AND client_id=client LIMIT 1)
ILIKE 'SSP%' THEN
--- SSP answer
COALESCE((SELECT staff_id FROM staff_employment_current WHERE staff_position_code='COORD_PROP' AND agency_project_code='SSP' LIMIT 1),(SELECT staff_id FROM staff_employment_current WHERE staff_position_code='MGRPROJ' AND agency_project_code='SSP' LIMIT 1))
ELSE
---SPC answer
(SELECT staff_inspector FROM tbl_residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) WHERE client_id=client AND asof BETWEEN residence_date AND COALESCE(residence_date_end,asof) AND NOT ro.is_deleted LIMIT 1)
END
--LIMIT 1
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION staff_inspector_imm( client INTEGER, asof DATE ) RETURNS INTEGER AS $$
SELECT
CASE WHEN
(SELECT program_type_code FROM reg_spc WHERE asof BETWEEN reg_spc_date AND COALESCE(reg_spc_date_end,asof) AND client_id=client LIMIT 1)
ILIKE 'SSP%' THEN
--- SSP answer
COALESCE((SELECT staff_id FROM staff_employment_current WHERE staff_position_code='COORD_PROP' AND agency_project_code='SSP' LIMIT 1),(SELECT staff_id FROM staff_employment_current WHERE staff_position_code='MGRPROJ' AND agency_project_code='SSP' LIMIT 1))
ELSE
---SPC answer
(SELECT staff_inspector FROM tbl_residence_own ro LEFT JOIN l_housing_project USING (housing_project_code) WHERE client_id=client AND asof BETWEEN residence_date AND COALESCE(residence_date_end,asof) AND NOT ro.is_deleted LIMIT 1)
END
--LIMIT 1
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION si_stable( client INTEGER ) RETURNS INTEGER AS $$
SELECT staff_inspector_stable(client,target_date());
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION si_imm( client INTEGER ) RETURNS INTEGER AS $$
SELECT staff_inspector_stable(client,target_date());
$$ LANGUAGE SQL IMMUTABLE;
CREATE OR REPLACE FUNCTION si_stable_calls_imm( client INTEGER ) RETURNS INTEGER AS $$
SELECT staff_inspector_imm(client,target_date());
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION si_imm_calls_imm( client INTEGER ) RETURNS INTEGER AS $$
SELECT staff_inspector_imm(client,target_date());
$$ LANGUAGE SQL IMMUTABLE;And the version info (Postgres on Centos 6.10)
SELECT version();
version ---------------------------------------------------------------------------------------------------------- PostgreSQL 9.6.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit (1 row)
Happy to provide any additional relevant info, or for someone to point out what obvious thing I'm overlooking. Thanks in advance!
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
