On Wed, Aug 26, 2020, 1:37 AM Julian Wolf <julian.w...@invenium.io> 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. >
