Home > mailing lists

Thread: Partitioning and constraint exclusion

Partitioning and constraint exclusion

From

Jayadevan M

Date:

07 September 2015, 12:55:38

Hello ,

I have a parent table and 6 child tables (partitions). The child tables have check constraints defined in the form

CHECK (myuid in (123,456,..)).

myuid is bigint, the constraints for the 6 child tables are definitely mutually exclusive. The number of values in the list ranges from 2-10 for 5 of the child tables. For the 6th child table, the list is 2500+ elements. When I try explain/explain analyze for even a simple query like

select * from parent where myuid in (123,456,789)

the child table with 2500+ elements gets always scanned. I have an index on the column and that does get used. But why doesn't the planner just use constraint exclusion and not go for the index scan? Anyone faced a similar issue?

Thanks,

Jayadevan

Re: Partitioning and constraint exclusion

From

Melvin Davidson

Date:

07 September 2015, 13:43:19

First, what is the PostgresSQL version ??????

Next, in postgresql.conf, what is the value of constraint_exclusion ?

On Mon, Sep 7, 2015 at 8:55 AM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:

Hello ,

I have a parent table and 6 child tables (partitions). The child tables have check constraints defined in the form
CHECK (myuid in (123,456,..)).
myuid is bigint, the constraints for the 6 child tables are definitely mutually exclusive. The number of values in the list ranges from 2-10 for 5 of the child tables. For the 6th child table, the list is 2500+ elements. When I try explain/explain analyze for even a simple query like

select * from parent where myuid in (123,456,789)

the child table with 2500+ elements gets always scanned. I have an index on the column and that does get used. But why doesn't the planner just use constraint exclusion and not go for the index scan? Anyone faced a similar issue?

Thanks,
Jayadevan

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: Partitioning and constraint exclusion

From

"David G. Johnston"

Date:

07 September 2015, 20:48:09

On Mon, Sep 7, 2015 at 8:55 AM, Jayadevan M <maymala.jayadevan@gmail.com> wrote:

Hello ,

I have a parent table and 6 child tables (partitions). The child tables have check constraints defined in the form
CHECK (myuid in (123,456,..)).
myuid is bigint, the constraints for the 6 child tables are definitely mutually exclusive. The number of values in the list ranges from 2-10 for 5 of the child tables. For the 6th child table, the list is 2500+ elements. When I try explain/explain analyze for even a simple query like

select * from parent where myuid in (123,456,789)

the child table with 2500+ elements gets always scanned. I have an index on the column and that does get used. But why doesn't the planner just use constraint exclusion and not go for the index scan? Anyone faced a similar issue?

IIRC The planner doesn't understand

overlaps so having a definition of:

IN (1,2,3,4,5); or nearly equivalently = ANY(ARRAY[1,2,3,4,5]))

and a request for:

IN (1,3,5) / = ANY(ARRAY[1,3,5]) is going to get you nowhere with the planner.

I am not sure but am doubting it is intelligent enough to recognize the functional expression even if all of the values are present. "simple equality" (http://www.postgresql.org/docs/9.4/interactive/ddl-partitioning.html) this is not.

David J.

Re: Partitioning and constraint exclusion

From

Vick Khera

Date:

07 September 2015, 23:39:54

On Mon, Sep 7, 2015 at 4:48 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

IIRC The planner doesn't understand

overlaps so having a definition of:

IN (1,2,3,4,5); or nearly equivalently = ANY(ARRAY[1,2,3,4,5]))

and a request for:

IN (1,3,5) / = ANY(ARRAY[1,3,5]) is going to get you nowhere with the planner.

The partition code does not execute your CHECK condition; it only tests to see if the query includes it explicitly. For example, if you have a split on "id % 100 = 59" for a table, then looking for id = 13059 does you no good; you have to search for "id = 13059 AND id % 100 = 59" to invoke the table exclusions.

Re: Partitioning and constraint exclusion

From

Jayadevan M

Date:

08 September 2015, 04:20:39

On Mon, Sep 7, 2015 at 7:12 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

First, what is the PostgresSQL version ??????

9.3.6

Next, in postgresql.conf, what is the value of constraint_exclusion ?

partition

In response to the other possible issues pointed out - the planner is indeed skipping the rest of the child tables (the ones with just a few values in the check constraint). That is why I feel the number of elements in the check constraint on this particular child table is causing it to be scanned. The query ends up scanning the table where the data will be found and the table with 2500+ values in the check constraint. I may be missing something?

I tried changing the filter from myuid in (1,2,3) to myuid = 1 or myuid = 2 or....

It did not improve the plan. One Index Cond became 3 Index Cond .

Thanks,

Jayadevan

Re: Partitioning and constraint exclusion

From

Jayadevan M

Date:

08 September 2015, 11:49:32

I am not sure but am doubting it is intelligent enough to recognize the functional expression even if all of the values are present. "simple equality" (http://www.postgresql.org/docs/9.4/interactive/ddl-partitioning.html) this is not.

Looks like the tables with about 100+ values in the check list gets pulled in, even with constraint exclusion on. I created a simple test case. One parent table with just one column, and 3 child tables with one column.

test=# \d+ parent

Table "public.parent"

--------+---------+-----------+---------+--------------+-------------

Child tables: child1,

child2,

child3

test=# \d+ child1

Table "public.child1"

--------+---------+-----------+---------+--------------+-------------

Check constraints:

"c" CHECK (id = ANY (ARRAY[1, 2]))

Inherits: parent

test=# \d+ child2

Table "public.child2"

--------+---------+-----------+---------+--------------+-------------

Check constraints:

"c" CHECK (id = ANY (ARRAY[3, 4]))

Inherits: parent

test=# \d+ child3

Table "public.child3"

--------+---------+-----------+---------+--------------+-------------

Check constraints:

"c3" CHECK (id = ANY (ARRAY[5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100]))

Inherits: parent

test=# explain analyze select * from parent where id = 1;

QUERY PLAN

--------------------------------------------------------------------------------------------------------

Append (cost=0.00..40.00 rows=13 width=4) (actual time=0.002..0.002 rows=0 loops=1)

-> Seq Scan on parent (cost=0.00..0.00 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)

Filter: (id = 1)

-> Seq Scan on child1 (cost=0.00..40.00 rows=12 width=4) (actual time=0.000..0.000 rows=0 loops=1)

Filter: (id = 1)

Total runtime: 0.029 ms

If I increase the number of values a bit more.....

with t as (select generate_series(5,110) x ) select 'alter table child3 add constraint c3 check ( id in ( ' || string_agg(x::text,',') || ' )) ; ' from t;

test=# explain analyze select * from parent where id = 1;

QUERY PLAN

--------------------------------------------------------------------------------------------------------

Append (cost=0.00..80.00 rows=25 width=4) (actual time=0.003..0.003 rows=0 loops=1)

-> Seq Scan on parent (cost=0.00..0.00 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)

Filter: (id = 1)

-> Seq Scan on child1 (cost=0.00..40.00 rows=12 width=4) (actual time=0.000..0.000 rows=0 loops=1)

Filter: (id = 1)

-> Seq Scan on child3 (cost=0.00..40.00 rows=12 width=4) (actual time=0.000..0.000 rows=0 loops=1)

Filter: (id = 1)

Thanks,

Jayadevan

David J.