Odd performance difference in check constraint : SQL(slow) vs plpgsql(fast)
This is the reverse of what I thought I would find.
In short my check constraint is extracting the epoch from a start timestamp,
and an end timestamp to get the number of seconds difference.
It then uses this number to check the array_upper() of an array to make sure
it's the proper size
The SQL version uses a case statement, and the plpgsql uses an IF/ELSE
In a particular insert test
The plpgsql version adds 1 second over the no constraints case.
the sql version adds 10 seconds over the no constraints case.
Why would this be?
--->
CREATE OR REPLACE FUNCTION check_end_time_foo( _start_time TIMESTAMP,
_end_time TIMESTAMP, _granularity SMALLINT, _values DOUBLE PRECISION[] )
RETURNS boolean AS $$
BEGIN
if( _granularity = 5 )
THEN
return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT -
EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 60 *
array_upper( _values,1 ) );
ELSEIF( _granularity = 7 )
THEN
return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT -
EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 900 *
array_upper( _values,1 ) );
ELSEIF( _granularity = 9 )
THEN
return( EXTRACT( EPOCH FROM _end_time at time zone 'utc' )::BIGINT -
EXTRACT( EPOCH FROM _start_time at time zone 'utc' )::BIGINT = 3600 *
array_upper( _values,1 ) );
ELSEIF( _granularity = 12 )
THEN
return( ( (EXTRACT( YEAR FROM (_end_time at time zone 'utc') at time
zone 'utc' )::INT * 12) + EXTRACT( MONTH FROM (_end_time at time zone 'utc'
) at time zone 'utc' )::INT ) - ( (EXTRACT( YEAR FROM (_start_time at time
zone 'utc') at time zone 'utc' )::INT * 12) + EXTRACT( MONTH FROM
(_start_time at time zone 'utc') at time zone 'utc' )::INT ) = array_upper(
_values,1 ) );
END IF;
END;
$$ language plpgsql IMMUTABLE;
alter table timeseries add CONSTRAINT timeseries_valid_end_time CHECK(
check_end_time_foo( series_start_time, series_end_time, granularity,
data_value ) );
-vs-
alter table timeseries add CONSTRAINT timeseries_valid_end_time CHECK( CASE
WHEN granularity = 5
THEN
EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT(
EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 60 *
array_upper( data_value,1 )
WHEN granularity = 7
THEN
EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT(
EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 900 *
array_upper( data_value,1 )
WHEN granularity = 9
THEN
EXTRACT( EPOCH FROM series_end_time at time zone 'utc' )::BIGINT - EXTRACT(
EPOCH FROM series_start_time at time zone 'utc' )::BIGINT = 3600 *
array_upper( data_value,1 )
WHEN granularity = 12
THEN
((EXTRACT( YEAR FROM ( series_end_time at time zone 'utc') at time zone
'utc' )::INT * 12) + EXTRACT( MONTH FROM ( series_end_time at time zone
'utc' ) at time zone 'utc' )::INT ) - ( (EXTRACT( YEAR FROM (
series_start_time at time zone 'utc') at time zone 'utc' )::INT * 12) +
EXTRACT( MONTH FROM ( series_start_time at time zone 'utc') at time zone
'utc' )::INT ) = array_upper( data_value,1 )
ELSE
false
END
);
bubba postgres <bubba.postgres@gmail.com> writes:
This is the reverse of what I thought I would find.
In short my check constraint is extracting the epoch from a start timestamp,
and an end timestamp to get the number of seconds difference.
It then uses this number to check the array_upper() of an array to make sure
it's the proper size
The SQL version uses a case statement, and the plpgsql uses an IF/ELSE
In a particular insert test
The plpgsql version adds 1 second over the no constraints case.
the sql version adds 10 seconds over the no constraints case.
Why would this be?
It would not likely be faster unless it can be inlined, and maybe not
even then, because of plan caching effects (plpgsql is a lot better
about that). In this particular case, I'm suspicious whether all the
operations are immutable; if they aren't, the marking of the function
as immutable will definitely prevent inlining.
regards, tom lane