David Rowley writes:
> I'm not worried about the cost of the decision at plan init time. I was
> just confused about what Tom was recommending. I couldn't quite decide from
> his email if he meant to keep the alternative plan node around so that the
> executor must transition through it for each r
On 16 March 2015 at 09:55, David Rowley wrote:
> I think it's probably possible to do this, but I think it would require
> calling make_one_rel() with every combination of each possibly removable
> relations included and not included in the join list. I'm thinking this
> could end up a lot of wor
On 1/13/15 5:02 AM, David Rowley wrote:
I can't quite get my head around what you mean here, as the idea sounds quite
similar to something that's been discussed already and ruled out.
If we're joining relation a to relation b, say the plan chosen is a merge join.
If we put some special node as
On 12 January 2015 at 15:57, Robert Haas wrote:
> On Thu, Jan 8, 2015 at 6:31 AM, David Rowley wrote:
> > I'd be keen to know what people's thoughts are about the
> nodeAlternativePlan
> > only surviving until the plan is initialised.
>
> I find it scary, although sometimes I am easily frightene
On 13 January 2015 at 11:29, Jim Nasby wrote:
> On 12/3/14 1:08 PM, Tom Lane wrote:
>
>> Heikki Linnakangas writes:
>>
>>> >Do you need to plan for every combination, where some joins are removed
>>> >and some are not?
>>>
>> I would vote for just having two plans and one switch node. To exploi
On 12/3/14 1:08 PM, Tom Lane wrote:
Heikki Linnakangas writes:
>Do you need to plan for every combination, where some joins are removed
>and some are not?
I would vote for just having two plans and one switch node. To exploit
any finer grain, we'd have to have infrastructure that would let us
On Thu, Jan 8, 2015 at 6:31 AM, David Rowley wrote:
> I'd be keen to know what people's thoughts are about the nodeAlternativePlan
> only surviving until the plan is initialised.
I find it scary, although sometimes I am easily frightened.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
On 4 December 2014 at 12:24, Simon Riggs wrote:
> On 3 December 2014 at 12:18, Atri Sharma wrote:
>
>> So the planner keeps all possibility satisfying plans, or it looks at the
>> possible conditions (like presence of foreign key for this case, for eg) and
>> then lets executor choose between the
On 3 December 2014 at 12:18, Atri Sharma wrote:
> So the planner keeps all possibility satisfying plans, or it looks at the
> possible conditions (like presence of foreign key for this case, for eg) and
> then lets executor choose between them?
I'm suggesting the planner keeps 2 plans: One with
On Wed, Dec 3, 2014 at 2:09 PM, Robert Haas wrote:
> On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane wrote:
>> I would envision the planner starting out generating the first subplan
>> (without the optimization), but as it goes along, noting whether there
>> are any opportunities for join removal. At
On Wed, Dec 03, 2014 at 02:08:27PM -0500, Tom Lane wrote:
> Heikki Linnakangas writes:
> > Do you need to plan for every combination, where some joins are removed
> > and some are not?
>
> I would vote for just having two plans and one switch node. To exploit
> any finer grain, we'd have to hav
Heikki Linnakangas writes:
> Do you need to plan for every combination, where some joins are removed
> and some are not?
I would vote for just having two plans and one switch node. To exploit
any finer grain, we'd have to have infrastructure that would let us figure
out *which* constraints pend
On 12/03/2014 07:41 PM, Robert Haas wrote:
On Wed, Dec 3, 2014 at 12:33 PM, Tom Lane wrote:
Stephen Frost writes:
* Tom Lane (t...@sss.pgh.pa.us) wrote:
However, even granting that that is a concern, so what? You *have* to
do the planning twice, or you're going to be generating a crap plan
On Wed, Dec 3, 2014 at 11:47 PM, Tom Lane wrote:
> Atri Sharma writes:
> > On Wed, Dec 3, 2014 at 11:38 PM, Tom Lane wrote:
> >> I think the right approach for now is to emulate the GEQO precedent as
> >> closely as possible. Build all the single-relation Paths the same as
> >> now, then do a
Atri Sharma writes:
> On Wed, Dec 3, 2014 at 11:38 PM, Tom Lane wrote:
>> I think the right approach for now is to emulate the GEQO precedent as
>> closely as possible. Build all the single-relation Paths the same as
>> now, then do a join search over all the relations, then (if we've noticed
>>
On Wed, Dec 3, 2014 at 11:38 PM, Tom Lane wrote:
> Stephen Frost writes:
> > * Atri Sharma (atri.j...@gmail.com) wrote:
> >> Agreed, but in some cases, we could possibly make some assumptions (if
> >> there is no index, if a large fraction of table will be returned in
> scan,
> >> FunctionScan).
Atri Sharma writes:
> Does it also make sense to recalculate the costs from scratch for the
> replan? It might be, I am just asking.
The join costs would be recalculated from scratch, yes. The
single-relation Paths would already exist and their costs would not
change. Again, if you've not studi
Stephen Frost writes:
> * Atri Sharma (atri.j...@gmail.com) wrote:
>> Agreed, but in some cases, we could possibly make some assumptions (if
>> there is no index, if a large fraction of table will be returned in scan,
>> FunctionScan).
> All neat ideas but how about we get something which works i
On Wed, Dec 3, 2014 at 11:27 PM, Stephen Frost wrote:
> * Atri Sharma (atri.j...@gmail.com) wrote:
> > Agreed, but in some cases, we could possibly make some assumptions (if
> > there is no index, if a large fraction of table will be returned in scan,
> > FunctionScan).
>
> All neat ideas but how
* Atri Sharma (atri.j...@gmail.com) wrote:
> Agreed, but in some cases, we could possibly make some assumptions (if
> there is no index, if a large fraction of table will be returned in scan,
> FunctionScan).
All neat ideas but how about we get something which works in the way
being asked for befo
On Wed, Dec 3, 2014 at 11:19 PM, Tom Lane wrote:
> Atri Sharma writes:
> > Is it possible to only replan part of the plan in case of this
> > optimization? I think that we might need to only replan parts of the
> > original plan (as you mentioned, join search and above). So we could
> reuse
> >
Atri Sharma writes:
> Is it possible to only replan part of the plan in case of this
> optimization? I think that we might need to only replan parts of the
> original plan (as you mentioned, join search and above). So we could reuse
> the original plan in part and not do a lot of replanning (an ob
On Wed, Dec 3, 2014 at 12:33 PM, Tom Lane wrote:
> Stephen Frost writes:
>> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>>> However, even granting that that is a concern, so what? You *have* to
>>> do the planning twice, or you're going to be generating a crap plan for
>>> one case or the other.
>
>>
On Wed, Dec 3, 2014 at 11:03 PM, Tom Lane wrote:
> Stephen Frost writes:
> > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> >> However, even granting that that is a concern, so what? You *have* to
> >> do the planning twice, or you're going to be generating a crap plan for
> >> one case or the other.
Stephen Frost writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> However, even granting that that is a concern, so what? You *have* to
>> do the planning twice, or you're going to be generating a crap plan for
>> one case or the other.
> Yeah, I don't see a way around that..
Also, it occurs to
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Robert Haas writes:
> > On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane wrote:
> >> I would envision the planner starting out generating the first subplan
> >> (without the optimization), but as it goes along, noting whether there
> >> are any opportunities for
Robert Haas writes:
> On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane wrote:
>> I would envision the planner starting out generating the first subplan
>> (without the optimization), but as it goes along, noting whether there
>> are any opportunities for join removal. At the end, if it found that
>> th
On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane wrote:
> I would envision the planner starting out generating the first subplan
> (without the optimization), but as it goes along, noting whether there
> are any opportunities for join removal. At the end, if it found that
> there were such opportunities
Robert Haas writes:
> On Wed, Dec 3, 2014 at 11:23 AM, Andres Freund wrote:
>> Well, the planstate tree is what determines the execution, right? I
>> don't see what would stop us from doing something like replacing:
>> PlanState *
>> ExecInitNode(Plan *node, EState *estate, int eflags)
>> {
>> ..
On Wed, Dec 3, 2014 at 11:23 AM, Andres Freund wrote:
> Well, the planstate tree is what determines the execution, right? I
> don't see what would stop us from doing something like replacing:
> PlanState *
> ExecInitNode(Plan *node, EState *estate, int eflags)
> {
> ...
> case T_NestLoop:
* Atri Sharma (atri.j...@gmail.com) wrote:
> What I am concerned about is that in this case, the option plans are
> competing plans rather than separate plans.
Not sure I follow this thought entirely.. The plans in the plancache
are competeing, but separate, plans.
> My main concern is that we m
On 2014-12-03 11:11:49 -0500, Robert Haas wrote:
> On Wed, Dec 3, 2014 at 10:56 AM, Andres Freund wrote:
> > On 2014-12-03 10:51:19 -0500, Robert Haas wrote:
> >> On Wed, Dec 3, 2014 at 4:29 AM, David Rowley wrote:
> >> > *** Method 1: Removing Inner Joins at planning time:
> >> >
> >> > *** Meth
On Wed, Dec 3, 2014 at 10:56 AM, Andres Freund wrote:
> On 2014-12-03 10:51:19 -0500, Robert Haas wrote:
>> On Wed, Dec 3, 2014 at 4:29 AM, David Rowley wrote:
>> > *** Method 1: Removing Inner Joins at planning time:
>> >
>> > *** Method 2: Marking scans as possibly skippable during planning, an
On 2014-12-03 10:51:19 -0500, Robert Haas wrote:
> On Wed, Dec 3, 2014 at 4:29 AM, David Rowley wrote:
> > *** Method 1: Removing Inner Joins at planning time:
> >
> > *** Method 2: Marking scans as possibly skippable during planning, and
> > skipping joins at execution (Andres' method)
> >
> > **
On Wed, Dec 3, 2014 at 8:32 PM, Stephen Frost wrote:
> * Atri Sharma (atri.j...@gmail.com) wrote:
> > So the planner keeps all possibility satisfying plans, or it looks at the
> > possible conditions (like presence of foreign key for this case, for eg)
> > and then lets executor choose between th
On Wed, Dec 3, 2014 at 4:29 AM, David Rowley wrote:
> *** Method 1: Removing Inner Joins at planning time:
>
> *** Method 2: Marking scans as possibly skippable during planning, and
> skipping joins at execution (Andres' method)
>
> *** Method 3: Marking scans as possibly skippable during planning
On 2014-12-03 11:30:32 +, Simon Riggs wrote:
> I guess we need an Option node. Tom and I discussed that about an aeon ago.
>
> The Option node has a plan for each situation. At execution time, we
> make the test specified in the plan and then select the appropriate
> subplan.
>
> That way we
* Stephen Frost (sfr...@snowman.net) wrote:
> * Atri Sharma (atri.j...@gmail.com) wrote:
> > So the planner keeps all possibility satisfying plans, or it looks at the
> > possible conditions (like presence of foreign key for this case, for eg)
> > and then lets executor choose between them?
>
> Ri
* Atri Sharma (atri.j...@gmail.com) wrote:
> So the planner keeps all possibility satisfying plans, or it looks at the
> possible conditions (like presence of foreign key for this case, for eg)
> and then lets executor choose between them?
Right, this was one of the thoughts that I had.
> So is t
On Wed, Dec 3, 2014 at 5:00 PM, Simon Riggs wrote:
> On 3 December 2014 at 09:29, David Rowley wrote:
> > *** Method 3: Marking scans as possibly skippable during planning and
> > removing redundant join nodes at executor startup (Simon's method)
> >
> > Pros:
> > 1. The plan can be executed as
On 3 December 2014 at 09:29, David Rowley wrote:
> *** Method 3: Marking scans as possibly skippable during planning and
> removing redundant join nodes at executor startup (Simon's method)
>
> Pros:
> 1. The plan can be executed as normal if there are any foreign key triggers
> pending.
> 2. Does
On 3 December 2014 at 08:13, Robert Haas wrote:
> On Sun, Nov 30, 2014 at 12:51 PM, Tom Lane wrote:
> > Bottom line, given all the restrictions on whether the optimization can
> > happen, I have very little enthusiasm for the whole idea. I do not think
> > the benefit will be big enough to just
* Robert Haas (robertmh...@gmail.com) wrote:
> On Sun, Nov 30, 2014 at 12:51 PM, Tom Lane wrote:
> > Bottom line, given all the restrictions on whether the optimization can
> > happen, I have very little enthusiasm for the whole idea. I do not think
> > the benefit will be big enough to justify t
On Sun, Nov 30, 2014 at 12:51 PM, Tom Lane wrote:
> Bottom line, given all the restrictions on whether the optimization can
> happen, I have very little enthusiasm for the whole idea. I do not think
> the benefit will be big enough to justify the amount of mess this will
> introduce.
This optimi
On 1 December 2014 at 06:51, Tom Lane wrote:
> David Rowley writes:
> > I see this is quite a fundamental change to how things currently work and
> > it could cause planning to take place during the execution of PREPAREd
> > statements, which might not impress people too much, but it would
> cer
David Rowley writes:
> I see this is quite a fundamental change to how things currently work and
> it could cause planning to take place during the execution of PREPAREd
> statements, which might not impress people too much, but it would certainly
> fix the weird anomalies that I'm currently facin
On 30 November 2014 at 23:19, Mart Kelder wrote:
>
> I think performance can be greatly improved if the planner is able to use
> information based on the current data. I think these patches are just two
> examples of where assumptions during planning are usefull. I think there
> are
> more possib
Hi David (and others),
David Rowley wrote:
> Hi,
>
> Starting a new thread which continues on from
> http://www.postgresql.org/message-id/caaphdvoec8ygwoahvsri-84en2k0tnh6gpxp1k59y9juc1w...@mail.gmail.com
>
> To give a brief summary for any new readers:
>
> The attached patch allows for INNER J
Hi,
Starting a new thread which continues on from
http://www.postgresql.org/message-id/caaphdvoec8ygwoahvsri-84en2k0tnh6gpxp1k59y9juc1w...@mail.gmail.com
To give a brief summary for any new readers:
The attached patch allows for INNER JOINed relations to be removed from the
plan,
providing none
Sent from my iPad
On 14-Jul-2013, at 22:12, Hannu Krosing wrote:
> On 07/14/2013 06:10 PM, Atri Sharma wrote:
>>
>> Sent from my iPad
>>
>> On 10-Jul-2013, at 13:11, Hannu Krosing wrote:
>>
>>> On 07/10/2013 09:18 AM, Atri Sharma wrote:
> Can you please post an example of such a join r
On 07/14/2013 06:10 PM, Atri Sharma wrote:
>
> Sent from my iPad
>
> On 10-Jul-2013, at 13:11, Hannu Krosing wrote:
>
>> On 07/10/2013 09:18 AM, Atri Sharma wrote:
Can you please post an example of such a join removal? I mean a query
before
and after the removal. Thanks,
>>> Courte
Sent from my iPad
On 10-Jul-2013, at 13:11, Hannu Krosing wrote:
> On 07/10/2013 09:18 AM, Atri Sharma wrote:
>>> Can you please post an example of such a join removal? I mean a query before
>>> and after the removal. Thanks,
>> Courtesy Robert Haas:
>>
>> SELECT foo.x, foo.y, foo.z FROM foo
On Wed, Jul 10, 2013 at 1:11 PM, Hannu Krosing wrote:
> On 07/10/2013 09:18 AM, Atri Sharma wrote:
>>> Can you please post an example of such a join removal? I mean a query before
>>> and after the removal. Thanks,
>> Courtesy Robert Haas:
>>
>> SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = ba
On 07/10/2013 09:18 AM, Atri Sharma wrote:
>> Can you please post an example of such a join removal? I mean a query before
>> and after the removal. Thanks,
> Courtesy Robert Haas:
>
> SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x
>
> Conditions:
>
> 1) foo.x is not null.
I guess that thi
> Can you please post an example of such a join removal? I mean a query before
> and after the removal. Thanks,
Courtesy Robert Haas:
SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x
Conditions:
1) foo.x is not null.
2) foo (x) is a foreign key referencing bar (x).
We can ignore bar co
On 07/10/2013 07:28 AM, Atri Sharma wrote:
I am not sure if the part you mentioned is inline with the case I am
talking about.
Can you please post an example of such a join removal? I mean a query
before and after the removal. Thanks,
//Tony
--
Sent via pgsql-hackers mailing list (pgsql-hac
On Wed, Jul 10, 2013 at 8:29 AM, Ashutosh Bapat
wrote:
> AFAIK,
> There is code to remove the redundant relations (joins too are relations).
> But I don't remember exactly where it is. Start looking at query_planner().
> The removal of relations should happen before actually planning the joins.
>
AFAIK,
There is code to remove the redundant relations (joins too are relations).
But I don't remember exactly where it is. Start looking at query_planner().
The removal of relations should happen before actually planning the joins.
On Tue, Jul 9, 2013 at 12:21 PM, Atri Sharma wrote:
> My main
My main point here is researching how difficult it is to add
functionality in the planner to allow it to to detect and make
decisions on foreign keys present in the outer relation.
I think that if this is added, rest of the work would be much easier.
I amy be completely wrong,though.
Thoughts/Com
On Mon, Jul 8, 2013 at 11:33 PM, Andres Freund wrote:
> That's for outer joins tho.
Oh, right - I spoke too soon. Looks like I missed the whole discussion
on inner join removal.
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your
On Tue, Jul 9, 2013 at 12:01 PM, Peter Geoghegan wrote:
> On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma wrote:
>> Where are we with that functionality atm? Do we have plans to move forward?
>
> PostgreSQL has had this capability for some time. See:
>
> http://git.postgresql.org/gitweb/?p=postgresq
On 2013-07-08 23:31:15 -0700, Peter Geoghegan wrote:
> On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma wrote:
> > Where are we with that functionality atm? Do we have plans to move forward?
> PostgreSQL has had this capability for some time. See:
> http://git.postgresql.org/gitweb/?p=postgresql.git
On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma wrote:
> Where are we with that functionality atm? Do we have plans to move forward?
PostgreSQL has had this capability for some time. See:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=488d70ab46311386801c10691196ec8d755f2283
--
Pe
Hi all,
I was reading about the plans to add functionality to the planner to
remove redundant inner joins where there is no member of the inner
relation present in the target list and the inner relation is only
used for the join clause. Also, we have a foreign key in the outer
relation to the inne
64 matches
Mail list logo
