David Rowley dgrowle...@gmail.com 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
On 16 March 2015 at 09:55, David Rowley dgrowle...@gmail.com 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
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
On 12 January 2015 at 15:57, Robert Haas robertmh...@gmail.com wrote:
On Thu, Jan 8, 2015 at 6:31 AM, David Rowley dgrowle...@gmail.com 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,
On 12/3/14 1:08 PM, Tom Lane wrote:
Heikki Linnakangashlinnakan...@vmware.com 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
On Thu, Jan 8, 2015 at 6:31 AM, David Rowley dgrowle...@gmail.com 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:
On 4 December 2014 at 12:24, Simon Riggs si...@2ndquadrant.com wrote:
On 3 December 2014 at 12:18, 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
On 3 December 2014 at 08:13, Robert Haas robertmh...@gmail.com wrote:
On Sun, Nov 30, 2014 at 12:51 PM, Tom Lane t...@sss.pgh.pa.us 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
On 3 December 2014 at 09:29, David Rowley dgrowle...@gmail.com 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
On Wed, Dec 3, 2014 at 5:00 PM, Simon Riggs si...@2ndquadrant.com wrote:
On 3 December 2014 at 09:29, David Rowley dgrowle...@gmail.com wrote:
*** Method 3: Marking scans as possibly skippable during planning and
removing redundant join nodes at executor startup (Simon's method)
Pros:
* 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 the
* 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?
Right,
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 can
On Wed, Dec 3, 2014 at 4:29 AM, David Rowley dgrowle...@gmail.com 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
On Wed, Dec 3, 2014 at 8:32 PM, 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
On 2014-12-03 10:51:19 -0500, Robert Haas wrote:
On Wed, Dec 3, 2014 at 4:29 AM, David Rowley dgrowle...@gmail.com wrote:
*** Method 1: Removing Inner Joins at planning time:
*** Method 2: Marking scans as possibly skippable during planning, and
skipping joins at execution (Andres'
On Wed, Dec 3, 2014 at 10:56 AM, Andres Freund and...@2ndquadrant.com wrote:
On 2014-12-03 10:51:19 -0500, Robert Haas wrote:
On Wed, Dec 3, 2014 at 4:29 AM, David Rowley dgrowle...@gmail.com wrote:
*** Method 1: Removing Inner Joins at planning time:
*** Method 2: Marking scans as
On 2014-12-03 11:11:49 -0500, Robert Haas wrote:
On Wed, Dec 3, 2014 at 10:56 AM, Andres Freund and...@2ndquadrant.com wrote:
On 2014-12-03 10:51:19 -0500, Robert Haas wrote:
On Wed, Dec 3, 2014 at 4:29 AM, David Rowley dgrowle...@gmail.com wrote:
*** Method 1: Removing Inner Joins at
* 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
On Wed, Dec 3, 2014 at 11:23 AM, Andres Freund and...@2ndquadrant.com 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)
{
...
Robert Haas robertmh...@gmail.com writes:
On Wed, Dec 3, 2014 at 11:23 AM, Andres Freund and...@2ndquadrant.com 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,
On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane t...@sss.pgh.pa.us 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
Robert Haas robertmh...@gmail.com writes:
On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane t...@sss.pgh.pa.us 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.
* Tom Lane (t...@sss.pgh.pa.us) wrote:
Robert Haas robertmh...@gmail.com writes:
On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane t...@sss.pgh.pa.us wrote:
I would envision the planner starting out generating the first subplan
(without the optimization), but as it goes along, noting whether there
Stephen Frost sfr...@snowman.net 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,
On Wed, Dec 3, 2014 at 11:03 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Stephen Frost sfr...@snowman.net 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
On Wed, Dec 3, 2014 at 12:33 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Stephen Frost sfr...@snowman.net 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
Atri Sharma atri.j...@gmail.com 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
On Wed, Dec 3, 2014 at 11:19 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Atri Sharma atri.j...@gmail.com 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
* 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 before
On Wed, Dec 3, 2014 at 11:27 PM, Stephen Frost sfr...@snowman.net 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
Stephen Frost sfr...@snowman.net 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
Atri Sharma atri.j...@gmail.com 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,
On Wed, Dec 3, 2014 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Stephen Frost sfr...@snowman.net 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
Atri Sharma atri.j...@gmail.com writes:
On Wed, Dec 3, 2014 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us 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
On Wed, Dec 3, 2014 at 11:47 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Atri Sharma atri.j...@gmail.com writes:
On Wed, Dec 3, 2014 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote:
I think the right approach for now is to emulate the GEQO precedent as
closely as possible. Build all the
On 12/03/2014 07:41 PM, Robert Haas wrote:
On Wed, Dec 3, 2014 at 12:33 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Stephen Frost sfr...@snowman.net 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
Heikki Linnakangas hlinnakan...@vmware.com 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
On Wed, Dec 03, 2014 at 02:08:27PM -0500, Tom Lane wrote:
Heikki Linnakangas hlinnakan...@vmware.com 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,
On Wed, Dec 3, 2014 at 2:09 PM, Robert Haas robertmh...@gmail.com wrote:
On Wed, Dec 3, 2014 at 12:08 PM, Tom Lane t...@sss.pgh.pa.us 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
On Sun, Nov 30, 2014 at 12:51 PM, Tom Lane t...@sss.pgh.pa.us 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
* Robert Haas (robertmh...@gmail.com) wrote:
On Sun, Nov 30, 2014 at 12:51 PM, Tom Lane t...@sss.pgh.pa.us 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
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 JOINed
On 30 November 2014 at 23:19, Mart Kelder m...@kelder31.nl 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
David Rowley dgrowle...@gmail.com 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
On 1 December 2014 at 06:51, Tom Lane t...@sss.pgh.pa.us wrote:
David Rowley dgrowle...@gmail.com 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
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 10-Jul-2013, at 13:11, Hannu Krosing ha...@2ndquadrant.com 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
On 07/14/2013 06:10 PM, Atri Sharma wrote:
Sent from my iPad
On 10-Jul-2013, at 13:11, Hannu Krosing ha...@2ndquadrant.com 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,
Sent from my iPad
On 14-Jul-2013, at 22:12, Hannu Krosing ha...@2ndquadrant.com wrote:
On 07/14/2013 06:10 PM, Atri Sharma wrote:
Sent from my iPad
On 10-Jul-2013, at 13:11, Hannu Krosing ha...@2ndquadrant.com wrote:
On 07/10/2013 09:18 AM, Atri Sharma wrote:
Can you please post an
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
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
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 this is also
On Wed, Jul 10, 2013 at 1:11 PM, Hannu Krosing ha...@2ndquadrant.com 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
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
On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma atri.j...@gmail.com wrote:
Where are we with that functionality atm? Do we have plans to move forward?
PostgreSQL has had this capability for some time. See:
On 2013-07-08 23:31:15 -0700, Peter Geoghegan wrote:
On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma atri.j...@gmail.com wrote:
Where are we with that functionality atm? Do we have plans to move forward?
PostgreSQL has had this capability for some time. See:
On Tue, Jul 9, 2013 at 12:01 PM, Peter Geoghegan p...@heroku.com wrote:
On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma atri.j...@gmail.com wrote:
Where are we with that functionality atm? Do we have plans to move forward?
PostgreSQL has had this capability for some time. See:
On Mon, Jul 8, 2013 at 11:33 PM, Andres Freund and...@2ndquadrant.com 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
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.
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
On Wed, Jul 10, 2013 at 8:29 AM, Ashutosh Bapat
ashutosh.ba...@enterprisedb.com 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
62 matches
Mail list logo
