Thread: Partitioning and constraint exclusion

Hi list,

I'm trying to do some partitioning on a table but I have a problem
with constraint exclusion.

Here the following code : https://gist.github.com/1038133

(this code is just an example to explain the problem)

As you can see I declare a fake immutable function in order to make
constraint exclusion work even
if the function is not immutable at all.

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.

Is there a way to force the use of constraint exclusion no matter the
size of the array used in the check constraint ?

Regards.

--
Sylvain

Sylvain Rabot <sylvain@abstraction.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.

            regards, tom lane

On Tue, 2011-06-21 at 12:25 -0400, Tom Lane wrote:
> Sylvain Rabot <sylvain@abstraction.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
>