Immutable function WAY slower than Stable function?

Started by Ken Tanzerover 7 years ago19 messagesgeneral

ken.tanzer@gmail.com

over 7 years ago

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
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.