Обсуждение: 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