Re: Too few rows expected by Planner on partitioned tables

On Wed, Aug 26, 2020, 1:37 AM Julian Wolf <julian(dot)wolf(at)invenium(dot)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.

>