Обсуждение: [HACKERS] Disable cross products in postgres
Hi all,
Indian Institute of Science
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 AutomationGourav Kumar
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 AutomationGourav Kumar
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
>> 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 PostgreSQL
>> 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
Thanks,
Gourav Kumar
Computer Science and AutomationGourav Kumar
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 PostgreSQL
>> 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 Indian Institute of ScienceThanks,Computer Science and Automation
Gourav Kumar
Thanks,
Gourav Kumar
Computer Science and AutomationGourav Kumar
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 PostgreSQL
>> 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 Indian Institute of ScienceThanks,Computer Science and Automation
Gourav Kumar--Indian Institute of ScienceThanks,Computer Science and Automation
Gourav Kumar
Thanks,
Gourav Kumar
Computer Science and AutomationGourav Kumar
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:
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
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:
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