pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: constraint exclusion with ineq condition (Re: server hardware tuning.)
10+ messages / 4 participants
[nested] [flat]
* Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
@ 2019-02-14 10:05 Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 10+ messages in thread
From: Justin Pryzby @ 2019-02-14 10:05 UTC (permalink / raw)
To: suganthi Sekar <suganthi@uniphore.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Thu, Feb 14, 2019 at 09:38:52AM +0000, suganthi Sekar wrote:
> i am using Postgresql 11, i have 2 partition table , when i joined both table in query
> a table its goes exact partition table , but other table scan all partition
>
> please clarify on this .
>
> Example :
>
> explain analyze
> select * from call_report1 as a inner join call_report2 as b on a.call_id=b.call_id
> where a.call_created_date ='2017-11-01' and '2017-11-30'
Looks like this query waas manally editted and should say:
> where a.call_created_date >='2017-11-01' AND a.call_created_date<'2017-11-30'
Right?
The issue is described well here:
https://www.postgresql.org/message-id/flat/7DF51702-0F6A-4571-80BB-188AAEF260DA%40gmail.com
https://www.postgresql.org/message-id/499.1496696552%40sss.pgh.pa.us
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'
Justin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
@ 2019-02-14 10:38 suganthi Sekar <suganthi@uniphore.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 10+ messages in thread
From: suganthi Sekar @ 2019-02-14 10:38 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
HI,
u mean the below parameter need to set on . its already on only.
alter system set constraint_exclusion to 'on';
Regards,
Suganthi Sekar
________________________________
From: Justin Pryzby <pryzby@telsasoft.com>
Sent: 14 February 2019 15:35:33
To: suganthi Sekar
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
On Thu, Feb 14, 2019 at 09:38:52AM +0000, suganthi Sekar wrote:
> i am using Postgresql 11, i have 2 partition table , when i joined both table in query
> a table its goes exact partition table , but other table scan all partition
>
> please clarify on this .
>
> Example :
>
> explain analyze
> select * from call_report1 as a inner join call_report2 as b on a.call_id=b.call_id
> where a.call_created_date ='2017-11-01' and '2017-11-30'
Looks like this query waas manally editted and should say:
> where a.call_created_date >='2017-11-01' AND a.call_created_date<'2017-11-30'
Right?
The issue is described well here:
https://www.postgresql.org/message-id/flat/7DF51702-0F6A-4571-80BB-188AAEF260DA%40gmail.com
https://www.postgresql.org/message-id/499.1496696552%40sss.pgh.pa.us
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'
Justin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
@ 2019-02-14 10:40 Justin Pryzby <pryzby@telsasoft.com>
parent: suganthi Sekar <suganthi@uniphore.com>
0 siblings, 1 reply; 10+ messages in thread
From: Justin Pryzby @ 2019-02-14 10:40 UTC (permalink / raw)
To: suganthi Sekar <suganthi@uniphore.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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'
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
@ 2019-02-14 10:49 suganthi Sekar <suganthi@uniphore.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 10+ messages in thread
From: suganthi Sekar @ 2019-02-14 10:49 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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>
Sent: 14 February 2019 16:10:01
To: suganthi Sekar
Cc: 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'
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
@ 2019-02-14 14:05 Michael Lewis <mlewis@entrata.com>
parent: suganthi Sekar <suganthi@uniphore.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Lewis @ 2019-02-14 14:05 UTC (permalink / raw)
To: suganthi Sekar <suganthi@uniphore.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
What are these two tables partitioned by?
On Thu, Feb 14, 2019, 5:03 AM suganthi Sekar <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>
> *Sent:* 14 February 2019 16:10:01
> *To:* suganthi Sekar
> *Cc:* 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'
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
@ 2019-02-14 14:35 suganthi Sekar <suganthi@uniphore.com>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 10+ messages in thread
From: suganthi Sekar @ 2019-02-14 14:35 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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'
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
@ 2019-02-14 18:50 Michael Lewis <mlewis@entrata.com>
parent: suganthi Sekar <suganthi@uniphore.com>
0 siblings, 1 reply; 10+ messages in thread
From: Michael Lewis @ 2019-02-14 18:50 UTC (permalink / raw)
To: suganthi Sekar <suganthi@uniphore.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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'
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
@ 2019-02-15 05:09 suganthi Sekar <suganthi@uniphore.com>
parent: Michael Lewis <mlewis@entrata.com>
0 siblings, 1 reply; 10+ messages in thread
From: suganthi Sekar @ 2019-02-15 05:09 UTC (permalink / raw)
To: Michael Lewis <mlewis@entrata.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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<mailto:suganthi@uniphore.com>> wrote:
Both table Portion by same column call_created_date
________________________________
From: Michael Lewis <mlewis@entrata.com<mailto:mlewis@entrata.com>>
Sent: 14 February 2019 19:35:48
To: suganthi Sekar
Cc: Justin Pryzby; pgsql-performance@lists.postgresql.org<mailto: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><mailto: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><mailto: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><mailto: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'
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
@ 2019-02-15 17:24 Benedict Holland <benedict.m.holland@gmail.com>
parent: suganthi Sekar <suganthi@uniphore.com>
0 siblings, 1 reply; 10+ messages in thread
From: Benedict Holland @ 2019-02-15 17:24 UTC (permalink / raw)
To: suganthi Sekar <suganthi@uniphore.com>; +Cc: Michael Lewis <mlewis@entrata.com>; Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi Sugathi,
That sounds like a perfect task for a view if the joins are all the same.
~Ben
On Fri, Feb 15, 2019 at 9:36 AM suganthi Sekar <suganthi@uniphore.com>
wrote:
> 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'
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
@ 2019-02-16 22:04 Justin Pryzby <pryzby@telsasoft.com>
parent: Benedict Holland <benedict.m.holland@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Justin Pryzby @ 2019-02-16 22:04 UTC (permalink / raw)
To: Benedict Holland <benedict.m.holland@gmail.com>; +Cc: suganthi Sekar <suganthi@uniphore.com>; Michael Lewis <mlewis@entrata.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Fri, Feb 15, 2019 at 12:24:18PM -0500, Benedict Holland wrote:
> That sounds like a perfect task for a view if the joins are all the same.
But note that either the view itself needs to have both where clauses (with
hardcoded dates?), or otherwise the view needs to be on only one table, and the
toplevel query needs to have where clause on each view, or else one of the
tables won't get constraint exclusion.
On Fri, Feb 15, 2019 at 9:36 AM suganthi Sekar <suganthi@uniphore.com> wrote:
> > 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.
> > 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'
--
Justin Pryzby
System Administrator
Telsasoft
+1-952-707-8581
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2019-02-16 22:04 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-02-14 10:05 Re: constraint exclusion with ineq condition (Re: server hardware tuning.) Justin Pryzby <pryzby@telsasoft.com>
2019-02-14 10:38 ` suganthi Sekar <suganthi@uniphore.com>
2019-02-14 10:40 ` Justin Pryzby <pryzby@telsasoft.com>
2019-02-14 10:49 ` suganthi Sekar <suganthi@uniphore.com>
2019-02-14 14:05 ` Michael Lewis <mlewis@entrata.com>
2019-02-14 14:35 ` suganthi Sekar <suganthi@uniphore.com>
2019-02-14 18:50 ` Michael Lewis <mlewis@entrata.com>
2019-02-15 05:09 ` suganthi Sekar <suganthi@uniphore.com>
2019-02-15 17:24 ` Benedict Holland <benedict.m.holland@gmail.com>
2019-02-16 22:04 ` Justin Pryzby <pryzby@telsasoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox