Postgres Pro
Downloads
Home   >   mailing lists

Re: constraint exclusion with ineq condition (Re: server hardwaretuning.) - Mailing list pgsql-performance

From suganthi Sekar
Subject Re: constraint exclusion with ineq condition (Re: server hardwaretuning.)
Date
Msg-id SG2PR01MB29676D809DB368CE7112A865BC600@SG2PR01MB2967.apcprd01.prod.exchangelabs.com
Whole thread Raw
In response to Re: constraint exclusion with ineq condition (Re: server hardware tuning.)  (Michael Lewis <mlewis@entrata.com>)
Responses Re: constraint exclusion with ineq condition (Re: server hardware tuning.)  (Benedict Holland <benedict.m.holland@gmail.com>)
List pgsql-performance
Tree view

Hi,


yes i accept , but when i will do for existing tables, i am facing issue.



I have created 100 Function , all the function having  five table join(now all partition by date) , now its not possible to change where condition in all 100 Function.

so that i am trying any other possibilities are there. 



Regards,

Suganthi Sekar

From: Michael Lewis <mlewis@entrata.com>
Sent: 15 February 2019 00:20:00
To: suganthi Sekar
Cc: Justin Pryzby; pgsql-performance@lists.postgresql.org
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
 
Yeah, the planner doesn't know that call_created_date can be limited on both tables unless you tell it specify it in the where condition as Laurenz said on another thread.


Michael Lewis

On Thu, Feb 14, 2019 at 7:35 AM suganthi Sekar <suganthi@uniphore.com> wrote:
Both table Portion by  same column call_created_date
________________________________
From: Michael Lewis <mlewis@entrata.com>
Sent: 14 February 2019 19:35:48
To: suganthi Sekar
Cc: Justin Pryzby; pgsql-performance@lists.postgresql.org
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

What are these two tables partitioned by?

On Thu, Feb 14, 2019, 5:03 AM suganthi Sekar <suganthi@uniphore.com<mailto:suganthi@uniphore.com> wrote:
Hi,

Thanks, i know if explicitly we give in where condition it is working.

i thought with below parameter in Postgresq11 this issue is fixed ?

 enable_partitionwise_join  to 'on';

 what is the use of enable_partitionwise_join  to 'on';

Thanks for your response.

Regards
Suganthi Sekar
________________________________
From: Justin Pryzby <pryzby@telsasoft.com<mailto:pryzby@telsasoft.com>>
Sent: 14 February 2019 16:10:01
To: suganthi Sekar
Cc: pgsql-performance@lists.postgresql.org<mailto:pgsql-performance@lists.postgresql.org>
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)

On Thu, Feb 14, 2019 at 10:38:36AM +0000, suganthi Sekar wrote:
> u mean the below parameter need to set on . its already on only.
>   alter system set  constraint_exclusion  to 'on';

No, I said:
> You can work around it by specifying the same condition on b.call_created_date:
> >  AND b.call_created_date >='2017-11-01' AND b.call_created_date<'2017-11-30'

pgsql-performance by date:

Previous
From: Michael Lewis
Date:
Subject: Re: autovacuum big table taking hours and sometimes seconds
Next
From: Mariel Cherkassky
Date:
Subject: Re: autovacuum big table taking hours and sometimes seconds