From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Partitioning and constraint exclusion |
Date: | 2015-09-07 20:48:03 |
Message-ID: | CAKFQuwazwaP7Rt0oyp+3qKtiuL8isaT3pWAQMWzqnnOi0yLaFw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Sep 7, 2015 at 8:55 AM, Jayadevan M <maymala(dot)jayadevan(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Smith | 2015-09-07 21:40:01 | jsonb value retrieval performance |
Previous Message | FarjadFarid(ChkNet) | 2015-09-07 14:14:45 | Re: table dependencies |