Re: Partitioning and constraint exclusion

On Tue, 2011-06-21 at 12:25 -0400, Tom Lane wrote:
> Sylvain Rabot <sylvain(at)abstraction(dot)fr> writes:
> > On Postgres 9.1beta2 when i run this code the first select will use
> > contraint exclusion but the second will not.
> > This apparently has something to do with the size of the array
> > returned by the fake immutable function.
>
> See predtest.c:
>
> /*
> * Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
> * likely to require O(N^2) time, and more often than not fail anyway.
> * So we set an arbitrary limit on the number of array elements that
> * we will allow to be treated as an AND or OR clause.
> * XXX is it worth exposing this as a GUC knob?
> */
> #define MAX_SAOP_ARRAY_SIZE 100
>
> While you could possibly increase that, I think that your approach is
> bound to have terrible performance anyway.

Indeed, data is going to store millions of records for possibly tens of
thousands different id_users per partitions.

I tried not using array with :

CREATE OR REPLACE FUNCTION data_users(
in_data text,
in_id_user integer
) RETURNS integer[] AS $__$

BEGIN
PERFORM data INTO v_return FROM data_partitioning WHERE data = in_data AND users @> ARRAY[id_id_user];

IF FOUND THEN
RETURN in_id_user;
ELSE
RETURN -1;
END IF;
END;

$__$ LANGUAGE plpgsql IMMUTABLE;

CREATE TABLE data_1 (
CHECK (id_user = data_users('data_1', id_user))
) inherits (data);

CREATE TABLE data_2 (
CHECK (id_user = data_users('data_2', id_user))
) inherits (data);

But constraint exclusion is not working. It seems that I can't use
id_user in both side of the expression.

Is there a way to use constraint exclusion with dynamic partitioning and
constraint more complex than basic range check ?

>
> regards, tom lane
>