Re: Removing INNER JOINs

On 16 March 2015 at 09:55, David Rowley <dgrowleyml(at)gmail(dot)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 end up a lot of work as the number of calls to make_one_rel() would be
> N^2, where N is the number of relations that may be removable.
>
> My line of thought was more along the lines of that the backup/all purpose
> plan will only be used in very rare cases. Either when a fk has been
> deferred or if the query is being executed from within a volatile function
> which has been called by an UPDATE statement which has just modified the
> table causing a foreign key trigger to be queued. I'm willing to bet someone
> does this somewhere in the world, but the query that's run would also have
> to have a removable join. (One of the regression tests I've added exercises
> this)
>
> For that reason I thought it was best to generate only 2 plans. One with
> *all* possible removable rels removed, and a backup one with nothing removed
> which will be executed if there's any FK triggers queued up.

Agreed, just 2 plans.

> The two ways of doing this have a massively different look in the EXPLAIN
> output. With the method the patch currently implements only 1 of the 2
> alternative plans are seen by EXPLAIN, this is because I've coded
> ExecInitAlternativePlan() to return the root node only 1 of the 2 plans. If
> I had kept the AlternativePlan node around then the EXPLAIN output would
> have 2 plans, both sitting under the AlternativePlan node.

I guess that is at least compatible with how we currently handle other
join elimination, so that is acceptable to me.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services