Re: [HACKERS] Removing INNER JOINs

2015-03-29 Thread Tom Lane
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

Re: [HACKERS] Removing INNER JOINs

2015-03-16 Thread Simon Riggs
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

Re: [HACKERS] Removing INNER JOINs

2015-01-14 Thread Jim Nasby
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

Re: [HACKERS] Removing INNER JOINs

2015-01-13 Thread David Rowley
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,

Re: [HACKERS] Removing INNER JOINs

2015-01-13 Thread David Rowley
On 13 January 2015 at 11:29, Jim Nasby jim.na...@bluetreble.com wrote: 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

Re: [HACKERS] Removing INNER JOINs

2015-01-12 Thread Jim Nasby
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

Re: [HACKERS] Removing INNER JOINs

2015-01-11 Thread Robert Haas
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:

Re: [HACKERS] Removing INNER JOINs

2014-12-07 Thread Simon Riggs
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread David Rowley
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Simon Riggs
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Atri Sharma
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:

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Stephen Frost
* 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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Stephen Frost
* 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,

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Andres Freund
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Robert Haas
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Atri Sharma
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Andres Freund
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'

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Robert Haas
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Andres Freund
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Stephen Frost
* 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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Robert Haas
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) { ...

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Tom Lane
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,

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Robert Haas
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Tom Lane
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.

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Stephen Frost
* 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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Tom Lane
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,

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Atri Sharma
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Robert Haas
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Tom Lane
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Atri Sharma
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Stephen Frost
* 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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Atri Sharma
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Tom Lane
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Tom Lane
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,

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Atri Sharma
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Tom Lane
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Atri Sharma
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Heikki Linnakangas
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Tom Lane
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

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread k...@rice.edu
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,

Re: [HACKERS] Removing INNER JOINs

2014-12-03 Thread Claudio Freire
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

Re: [HACKERS] Removing INNER JOINs

2014-12-02 Thread Robert Haas
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

Re: [HACKERS] Removing INNER JOINs

2014-12-02 Thread Stephen Frost
* 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

Re: [HACKERS] Removing INNER JOINs

2014-11-30 Thread Mart Kelder
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

Re: [HACKERS] Removing INNER JOINs

2014-11-30 Thread David Rowley
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

Re: [HACKERS] Removing INNER JOINs

2014-11-30 Thread Tom Lane
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

Re: [HACKERS] Removing INNER JOINs

2014-11-30 Thread David Rowley
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

[HACKERS] Removing INNER JOINs

2014-11-29 Thread David Rowley
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

Re: [HACKERS] Removing Inner Joins

2013-07-14 Thread Atri Sharma
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

Re: [HACKERS] Removing Inner Joins

2013-07-14 Thread Hannu Krosing
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,

Re: [HACKERS] Removing Inner Joins

2013-07-14 Thread Atri Sharma
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

Re: [HACKERS] Removing Inner Joins

2013-07-10 Thread Antonin Houska
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

Re: [HACKERS] Removing Inner Joins

2013-07-10 Thread Atri Sharma
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

Re: [HACKERS] Removing Inner Joins

2013-07-10 Thread Hannu Krosing
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

Re: [HACKERS] Removing Inner Joins

2013-07-10 Thread Atri Sharma
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

[HACKERS] Removing Inner Joins

2013-07-09 Thread Atri Sharma
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

Re: [HACKERS] Removing Inner Joins

2013-07-09 Thread Peter Geoghegan
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:

Re: [HACKERS] Removing Inner Joins

2013-07-09 Thread Andres Freund
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:

Re: [HACKERS] Removing Inner Joins

2013-07-09 Thread Atri Sharma
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:

Re: [HACKERS] Removing Inner Joins

2013-07-09 Thread Peter Geoghegan
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

Re: [HACKERS] Removing Inner Joins

2013-07-09 Thread Atri Sharma
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.

Re: [HACKERS] Removing Inner Joins

2013-07-09 Thread Ashutosh Bapat
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

Re: [HACKERS] Removing Inner Joins

2013-07-09 Thread 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