On Wed, Aug 26, 2020, 1:37 AM Julian Wolf <julian.wolf@xxxxxxxxxxx> wrote:
Hi Justin,
thank you very much for your help and sorry for the late answer.
After testing around with your suggestions, it actually was the daterange type which caused all the problems. Messing around with the statistics value improved performance drastically but did not solve the problem. We decided to replace the daterange type with a BIGINT and calculate the "id" of the daterange by just using the BIGINT (2x 4 bytes) representation of the daterange. Thus, it can be transformed in both directions immutably.
CREATE OR REPLACE FUNCTION to_daterange_id(daterange DATERANGE)
RETURNS BIGINT
IMMUTABLE
LANGUAGE plpgsql
AS
$$
BEGIN
return (extract(EPOCH FROM lower(daterange))::BIGINT << 32) |
extract(EPOCH FROM upper(daterange))::BIGINT;
end;--------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION to_daterange(daterange_id BIGINT)
RETURNS DATERANGE
IMMUTABLE
LANGUAGE plpgsql
AS
$$
BEGIN
RETURN daterange(to_timestamp(daterange_id >> 32)::DATE, to_timestamp(daterange_id & x'FFFFFFFF'::BIGINT)::DATE);
END;
$$;
You might want to consider changing that language declaration to SQL.
