Postgres Pro
Downloads
Home   >   mailing lists

Thread: Example query causing param_info to be set in plain rel path

Example query causing param_info to be set in plain rel path

From
Ashutosh Bapat
Date:
Hi All,
In order to test various cases of LATERAL join in Postgres-XC, I am trying to find a query where RelOptInof->lateral_relids would get set for plain base relations. I tried various examples like below. But none of them was able to set lateral_relids in RelOptInfo of the involved relations. I am using current master branch.

select * from tab1 left join lateral (select * from tab2 where tab2.val = tab1.val2) q using(val);
This just pulled up the qual at higher level.

select * from tab1 left join lateral (select sum(val2) sum_val2, val from tab2 where tab2.val2 = tab1.val2 group by tab2.val) q using(val);

This at least caused the outer reference to be added as parameter, but again no lateral_relid.

Is there an example query (however impractial query it would be), which can make the lateral_relids set in RelOptInfo of plain base relation?
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: Example query causing param_info to be set in plain rel path

From
Tom Lane
Date:
Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:
> In order to test various cases of LATERAL join in Postgres-XC, I am trying
> to find a query where RelOptInof->lateral_relids would get set for plain
> base relations.

I think you need a lateral reference in a function or VALUES FROM-item.
As you say, plain sub-selects are likely to get flattened.  (Possibly
if you stuck in a flattening fence such as OFFSET 0, you could get the
case to happen with a sub-select FROM item, but I'm too lazy to check.)
        regards, tom lane

Re: Example query causing param_info to be set in plain rel path

From
Ashutosh Bapat
Date:
No adding OFFSET there too didn't give the expected result. The lateral was handled in subquery and passed as param to the underlying table scan.

I am particularly interested in tables (unlike functions or subqueries) since, the table scans are shipped to the datanodes and I wanted to test the effect of lateral in such cases. OTH, functions involving access to the tables or subqueries are initiated on the coordinators, where lateral gets executed in the same way as PostgreSQL.

If it's so hard to come up with an example query which would cause lateral_relids to be set in RelOptInfo of a table, then it's very likely that relevant code is untested in PostgreSQL.


On Fri, Oct 25, 2013 at 7:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:
> In order to test various cases of LATERAL join in Postgres-XC, I am trying
> to find a query where RelOptInof->lateral_relids would get set for plain
> base relations.

I think you need a lateral reference in a function or VALUES FROM-item.
As you say, plain sub-selects are likely to get flattened.  (Possibly
if you stuck in a flattening fence such as OFFSET 0, you could get the
case to happen with a sub-select FROM item, but I'm too lazy to check.)

                        regards, tom lane



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

Re: Example query causing param_info to be set in plain rel path

From
Ashutosh Bapat
Date:
I got an example where paths for plain rel require param_info i.e. plain rel scans require to take care of the lateral references. Here's the example from PG regression

explain verbose select v.* from
  (int8_tbl x left join (select q1,(select coalesce(q2,0)) q2 from int8_tbl) y on x.q2 = y.q1)
  left join int4_tbl z on z.f1 = x.q2,
  lateral (select x.q1,y.q1 from dual union all select x.q2,y.q2 from dual) v(vx,vy);

There is note in create_scan_plan(), which says,
 324          * If it's a parameterized otherrel, there might be lateral references
 325          * in the tlist, which need to be replaced with Params.  This cannot
 326          * happen for regular baserels, though.  Note use_physical_tlist()
 327          * always fails for otherrels, so we don't need to check this above.
 328          */

Although it doesn't say why this can not happen for regular baserels.

So, we do have a testcase which tests this code path as well.


On Mon, Oct 28, 2013 at 12:30 PM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:
No adding OFFSET there too didn't give the expected result. The lateral was handled in subquery and passed as param to the underlying table scan.

I am particularly interested in tables (unlike functions or subqueries) since, the table scans are shipped to the datanodes and I wanted to test the effect of lateral in such cases. OTH, functions involving access to the tables or subqueries are initiated on the coordinators, where lateral gets executed in the same way as PostgreSQL.

If it's so hard to come up with an example query which would cause lateral_relids to be set in RelOptInfo of a table, then it's very likely that relevant code is untested in PostgreSQL.


On Fri, Oct 25, 2013 at 7:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> writes:
> In order to test various cases of LATERAL join in Postgres-XC, I am trying
> to find a query where RelOptInof->lateral_relids would get set for plain
> base relations.

I think you need a lateral reference in a function or VALUES FROM-item.
As you say, plain sub-selects are likely to get flattened.  (Possibly
if you stuck in a flattening fence such as OFFSET 0, you could get the
case to happen with a sub-select FROM item, but I'm too lazy to check.)

                        regards, tom lane



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



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