Partitioning and constraint exclusion

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Partitioning and constraint exclusion

Jayadevan
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
Reply | Threaded
Open this post in threaded view
|

Re: Partitioning and constraint exclusion

Melvin Davidson-5
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 <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Partitioning and constraint exclusion

David G Johnston
In reply to this post by Jayadevan
On Mon, Sep 7, 2015 at 8:55 AM, Jayadevan M <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Partitioning and constraint exclusion

Vick Khera

On Mon, Sep 7, 2015 at 4:48 PM, David G. Johnston <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Partitioning and constraint exclusion

Jayadevan
In reply to this post by Melvin Davidson-5


On Mon, Sep 7, 2015 at 7:12 PM, Melvin Davidson <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Partitioning and constraint exclusion

Jayadevan
In reply to this post by David G Johnston


​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"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
Child tables: child1,
              child2,
              child3

test=# \d+ child1
                        Table "public.child1"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
Check constraints:
    "c" CHECK (id = ANY (ARRAY[1, 2]))
Inherits: parent

test=# \d+ child2
                        Table "public.child2"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
Check constraints:
    "c" CHECK (id = ANY (ARRAY[3, 4]))
Inherits: parent

test=# \d+ child3
                        Table "public.child3"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              | 
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.