Re: Partitioning and constraint exclusion - Mailing list pgsql-general

From Sylvain Rabot
Subject Re: Partitioning and constraint exclusion
Date
Msg-id 1308685032.1797.10.camel@kheops
Whole thread Raw
In response to Re: Partitioning and constraint exclusion  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
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
>



pgsql-general by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: LISTEN filtering
Next
From: Scott Frankel
Date:
Subject: pipe text to copy statement stdin input