Disable cross products in postgres
Hi all,
is there some way through which I can disable cross products in postgresql?
This will make the DP join to not to consider join between two relations if
they don't have a join predicate among them.
Thanks,
Gourav Kumar
Computer Science and Automation
Indian Institute of Science
On Fri, Oct 13, 2017 at 3:41 PM, Gourav Kumar <gourav1905@gmail.com> wrote:
is there some way through which I can disable cross products in postgresql?
This will make the DP join to not to consider join between two relations if
they don't have a join predicate among them.
I mean, it would be easy enough to modify the code. We don't have a
configuration option for it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Can you guide me where to look for it?
On 14 October 2017 at 01:35, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Oct 13, 2017 at 3:41 PM, Gourav Kumar <gourav1905@gmail.com>
wrote:is there some way through which I can disable cross products in
postgresql?
This will make the DP join to not to consider join between two relations
if
they don't have a join predicate among them.
I mean, it would be easy enough to modify the code. We don't have a
configuration option for it.--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Thanks,
Gourav Kumar
Computer Science and Automation
Indian Institute of Science
On Fri, Oct 13, 2017 at 4:06 PM, Gourav Kumar <gourav1905@gmail.com> wrote:
Can you guide me where to look for it?
Search for make_rels_by_clauseless_joins.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 13, 2017 at 5:08 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Oct 13, 2017 at 4:06 PM, Gourav Kumar <gourav1905@gmail.com>
wrote:
Can you guide me where to look for it?
Search for make_rels_by_clauseless_joins.
I wonder if it's possible implement it as an extension using some hook
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
Show quoted text
Timbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
Can I use something like joininfo, which will store the join predicates and
I can check if there is no join predicate among the two relations don't
consider them.
On 14 October 2017 at 01:48, Fabrízio de Royes Mello <
fabriziomello@gmail.com> wrote:
On Fri, Oct 13, 2017 at 5:08 PM, Robert Haas <robertmhaas@gmail.com>
wrote:On Fri, Oct 13, 2017 at 4:06 PM, Gourav Kumar <gourav1905@gmail.com>
wrote:
Can you guide me where to look for it?
Search for make_rels_by_clauseless_joins.
I wonder if it's possible implement it as an extension using some hook
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQLTimbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello
--
Thanks,
Gourav Kumar
Computer Science and Automation
Indian Institute of Science
I tried debugging the code, at no point in execution the function
make_rels_by_clauseless_joins
was called. Although optimizer did consider some of the joins which are
cross products.
On 14 October 2017 at 01:57, Gourav Kumar <gourav1905@gmail.com> wrote:
Can I use something like joininfo, which will store the join predicates
and I can check if there is no join predicate among the two relations don't
consider them.On 14 October 2017 at 01:48, Fabrízio de Royes Mello <
fabriziomello@gmail.com> wrote:On Fri, Oct 13, 2017 at 5:08 PM, Robert Haas <robertmhaas@gmail.com>
wrote:On Fri, Oct 13, 2017 at 4:06 PM, Gourav Kumar <gourav1905@gmail.com>
wrote:
Can you guide me where to look for it?
Search for make_rels_by_clauseless_joins.
I wonder if it's possible implement it as an extension using some hook
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQLTimbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello--
Thanks,
Gourav Kumar
Computer Science and Automation
Indian Institute of Science
--
Thanks,
Gourav Kumar
Computer Science and Automation
Indian Institute of Science
For e.g. I am checking for this query
with ss as
(select ca_county,d_qoy, d_year,sum(ss_ext_sales_price) as store_sales
from store_sales,date_dim,customer_address
where ss_sold_date_sk = d_date_sk
and ss_addr_sk=ca_address_sk
group by ca_county,d_qoy, d_year),
ws as
(select ca_county,d_qoy, d_year,sum(ws_ext_sales_price) as web_sales
from web_sales,date_dim,customer_address
where ws_sold_date_sk = d_date_sk
and ws_bill_addr_sk=ca_address_sk
group by ca_county,d_qoy, d_year)
select /* tt */
ss1.ca_county
,ss1.d_year
,ws2.web_sales/ws1.web_sales web_q1_q2_increase
,ss2.store_sales/ss1.store_sales store_q1_q2_increase
,ws3.web_sales/ws2.web_sales web_q2_q3_increase
,ss3.store_sales/ss2.store_sales store_q2_q3_increase
from
ss ss1
,ss ss2
,ss ss3
,ws ws1
,ws ws2
,ws ws3
where
ss1.d_qoy = 1
and ss1.d_year = 2000
and ss1.ca_county = ss2.ca_county
and ss2.d_qoy = 2
and ss2.d_year = 2000
and ss2.ca_county = ss3.ca_county
and ss3.d_qoy = 3
and ss3.d_year = 2000
and ss1.ca_county = ws1.ca_county
and ws1.d_qoy = 1
and ws1.d_year = 2000
and ws1.ca_county = ws2.ca_county
and ws2.d_qoy = 2
and ws2.d_year = 2000
and ws1.ca_county = ws3.ca_county
and ws3.d_qoy = 3
and ws3.d_year =2000
and case when ws1.web_sales > 0 then ws2.web_sales/ws1.web_sales else
null end
case when ss1.store_sales > 0 then ss2.store_sales/ss1.store_sales
else null end
and case when ws2.web_sales > 0 then ws3.web_sales/ws2.web_sales else
null end
case when ss2.store_sales > 0 then ss3.store_sales/ss2.store_sales
else null end
order by web_q2_q3_increase;
It's a TPC-DS benchmark query.
It doesn't has a join predicate between ss1 and ws2 or ss1 and ws3. But
optimizer still considers a join among them.
On 14 October 2017 at 02:20, Gourav Kumar <gourav1905@gmail.com> wrote:
I tried debugging the code, at no point in execution the function
make_rels_by_clauseless_joins was called. Although optimizer did
consider some of the joins which are cross products.On 14 October 2017 at 01:57, Gourav Kumar <gourav1905@gmail.com> wrote:
Can I use something like joininfo, which will store the join predicates
and I can check if there is no join predicate among the two relations don't
consider them.On 14 October 2017 at 01:48, Fabrízio de Royes Mello <
fabriziomello@gmail.com> wrote:On Fri, Oct 13, 2017 at 5:08 PM, Robert Haas <robertmhaas@gmail.com>
wrote:On Fri, Oct 13, 2017 at 4:06 PM, Gourav Kumar <gourav1905@gmail.com>
wrote:
Can you guide me where to look for it?
Search for make_rels_by_clauseless_joins.
I wonder if it's possible implement it as an extension using some hook
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQLTimbira: http://www.timbira.com.br
Blog: http://fabriziomello.github.io
Linkedin: http://br.linkedin.com/in/fabriziomello
Twitter: http://twitter.com/fabriziomello
Github: http://github.com/fabriziomello--
Thanks,
Gourav Kumar
Computer Science and Automation
Indian Institute of Science--
Thanks,
Gourav Kumar
Computer Science and Automation
Indian Institute of Science
--
Thanks,
Gourav Kumar
Computer Science and Automation
Indian Institute of Science
Gourav Kumar <gourav1905@gmail.com> writes:
For e.g. I am checking for this query
...
where
and ss1.ca_county = ss2.ca_county
and ss2.ca_county = ss3.ca_county
and ss1.ca_county = ws1.ca_county
and ws1.ca_county = ws2.ca_county
and ws1.ca_county = ws3.ca_county
It doesn't has a join predicate between ss1 and ws2 or ss1 and ws3. But
optimizer still considers a join among them.
Sure it does, after transitive propagation of those equalities;
for instance we can derive ss1.ca_county = ws2.ca_county from
the above-quoted conditions. And it would be very stupid of the
optimizer not to consider those derived join conditions, because
they may lead to the optimal join order.
In general it's already true that the optimizer doesn't consider
clauseless joins unless there's no other choice. But this example
isn't showing such a case.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
But then is there some way to tell Optimizer not to consider transitive
joins ?
Or to know if the join is transitive or not ?
On 14-Oct-2017 3:43 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Gourav Kumar <gourav1905@gmail.com> writes:
For e.g. I am checking for this query
...
where
and ss1.ca_county = ss2.ca_county
and ss2.ca_county = ss3.ca_county
and ss1.ca_county = ws1.ca_county
and ws1.ca_county = ws2.ca_county
and ws1.ca_county = ws3.ca_countyIt doesn't has a join predicate between ss1 and ws2 or ss1 and ws3. But
optimizer still considers a join among them.Sure it does, after transitive propagation of those equalities;
for instance we can derive ss1.ca_county = ws2.ca_county from
the above-quoted conditions. And it would be very stupid of the
optimizer not to consider those derived join conditions, because
they may lead to the optimal join order.In general it's already true that the optimizer doesn't consider
clauseless joins unless there's no other choice. But this example
isn't showing such a case.regards, tom lane
On 2017-10-14 03:49:57 +0530, Gourav Kumar wrote:
But then is there some way to tell Optimizer not to consider transitive
joins ?
What are you actually trying to achieve here?
- Andres
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
I want to get the join graph of a given query. Which has node for each
relation and an edge between two nodes if they have a join predicate among
them.
On 14-Oct-2017 3:58 AM, "Andres Freund" <andres@anarazel.de> wrote:
Show quoted text
On 2017-10-14 03:49:57 +0530, Gourav Kumar wrote:
But then is there some way to tell Optimizer not to consider transitive
joins ?What are you actually trying to achieve here?
- Andres