Re: Too few rows expected by Planner on partitioned tables - Mailing list pgsql-performance

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;
$$;