pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Justin Pryzby <pryzby@telsasoft.com>
To: Benedict Holland <benedict.m.holland@gmail.com>
Cc: suganthi Sekar <suganthi@uniphore.com>
Cc: Michael Lewis <mlewis@entrata.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
Date: Sat, 16 Feb 2019 16:04:06 -0600
Message-ID: <20190216220406.GA26865@telsasoft.com> (raw)
In-Reply-To: <CAD+mzoydzS1UzmZyfGeSJJt2tgwi_b6NNnn8nB4oZCXb3VNFGw@mail.gmail.com>
References: <SG2PR01MB29673BE6F7AA24424FDBFF60BC670@SG2PR01MB2967.apcprd01.prod.exchangelabs.com>
	<20190214100533.GA30291@telsasoft.com>
	<SG2PR01MB2967106618165F57249C47F0BC670@SG2PR01MB2967.apcprd01.prod.exchangelabs.com>
	<20190214104001.GB30291@telsasoft.com>
	<SG2PR01MB29677E0B9863A71DBD86DF0FBC670@SG2PR01MB2967.apcprd01.prod.exchangelabs.com>
	<CAHOFxGqdAaA_Ryzx22hG-U-dj0c6d55hGj=ythCkYZNLGCpSOQ@mail.gmail.com>
	<SG2PR01MB29677D14AD569C7AB9E411A0BC670@SG2PR01MB2967.apcprd01.prod.exchangelabs.com>
	<CAHOFxGrosn+yrSwOB8wVBjFbbn=gSD6C2psKCVbno3dYHQZXvA@mail.gmail.com>
	<SG2PR01MB29676D809DB368CE7112A865BC600@SG2PR01MB2967.apcprd01.prod.exchangelabs.com>
	<CAD+mzoydzS1UzmZyfGeSJJt2tgwi_b6NNnn8nB4oZCXb3VNFGw@mail.gmail.com>

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




view thread (10+ messages)

Message-ID: <20190216220406.GA26865@telsasoft.com>
Permalink:  ../20190216220406.GA26865@telsasoft.com/
Also on:    postgresql.org/message-id/20190216220406.GA26865@telsasoft.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pryzby@telsasoft.com, benedict.m.holland@gmail.com, suganthi@uniphore.com, mlewis@entrata.com, pgsql-performance@lists.postgresql.org
  Subject: Re: constraint exclusion with ineq condition (Re: server hardware tuning.)
  In-Reply-To: <20190216220406.GA26865@telsasoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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