Re: Pulling up more complicated subqueries

On Wed, May 17, 2017 at 8:38 PM, Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:

>
> As a final note, I found an interesting paper called "Unnesting Arbitrary
> Queries", by Thomas Neumann and Alfons Kemper
> (http://www.btw-2015.de/res/proceedings/Hauptband/Wiss/Neumann-Unnesting_Arbitrary_Querie.pdf)
> It describes a series of transformations that can be applied to de-correlate
> (de-lateralize?) any lateral subquery join. The trick is to build a
> temporary relation that contains all the distinct outer values of the join,
> and pass that relation down to the subquery. So instead of "executing" the
> subquery for every outer row, passing the outer values as parameters (using
> PostgreSQL terminology), you collect a list of all the outer values first,
> and then execute the subquery only once. In the subquery, you perform a join
> with the temporary relation. And then the paper describes a bunch of rules
> and optimizations, that can optimize away the temporary relation in many
> cases. That might be one way to implement the de-lateralization steps in
> PostgreSQL.
>

IIUC what you describe here, in a query involving foreign scan, we
could pass down a list of parameters to the foreign server instead of
running foreign scan on every parameter in the list. That would
improve performance of such queries by a lot.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company