Re: [GENERAL] Joining 16 tables seems slow

[Frank Millman]

From: Chris Travers 
Sent: Tuesday, September 12, 2017 3:36 PM
To: Frank Millman 
Cc: Postgres General 
Subject: Re: [GENERAL] Joining 16 tables seems slow

Chris Travers wrote:

On Tue, Sep 12, 2017 at 3:15 PM, Frank Millman <fr...@chagford.com> wrote:


  2017-09-12 12:39 GMT+02:00 Pavel Stehule <pavel.steh...@gmail.com>:


> please use https://explain.depesz.com/ for both plans (slow, fast)

  Here are the results - 

  sql_slow - https://explain.depesz.com/s/9vn3

  sql_fast - https://explain.depesz.com/s/oW0F

> 
> I am not convinced that the nested loop is a problem here.  I cannot think of 
> a faster join plan than a nested loop when you only have one iteration of the 
> loop (and looking through I did not see any loop counts above 1).
> 
> If you read and count ms carefully you will find that ar_tran_inv is scanned 
> 6 times and each of these times is taking about 25ms.  25x6 is half of your 
> query time right there and then you have the overhead in the joins on top of 
> that.  Quick eyeball estimates is that this is where approx 200ms of your 
> query time comes from.  Looking at this in more detail it doesn't look 
> 
> This is not a problem with too many tables in the join but the fact that you 
> are joining the same tables in multiple times in ways you end up needing to 
> repeatedly sequentially scan them.
> 
> I also don't think an index is going to help unless you have accounting data 
> going way back (since you are looking for about a year's worth of data) or 
> unless 90% of your transactions get marked as deleted.  So I think you are 
> stuck with the sequential scans on this table and optimizing will probably 
> mean reducing the number of times you scan that table.

Ok, I have a bit more information.

A couple of general comments first.

1. This is now purely an academic exercise. The SQL query that triggered this 
thread is unnecessarily complex, and I have a better solution. However, I think 
it is still worth the effort to understand what is going on.

2. explain.depesz.com is a brilliant tool – thanks for suggesting it.

As Pavel and Chris have pointed out, the problem seems to be that ar_tran_inv 
is scanned six times. The question is why? I have an idea, but I will need some 
assistance.

I have split my transaction table into three separate tables – ar_tran_inv, 
ar_tran_crn, ar_tran_rec. I then have a VIEW called ar_trans to view the 
transactions in total.

Each physical table has a primary key called ‘row_id’, and an index on 
‘tran_date’.

The view is created like this -

CREATE VIEW ccc.ar_trans AS
  SELECT ‘ar_inv’ AS tran_type, row_id as tran_row_id, tran_number, tran_date 
... FROM ccc.ar_tran_inv
  UNION ALL
  SELECT ‘ar_crn’ AS tran_type, row_id as tran_row_id, tran_number, tran_date 
... FROM ccc.ar_tran_crn
  UNION ALL
  SELECT ‘ar_rec’ AS tran_type, row_id as tran_row_id, tran_number, tran_date 
... FROM ccc.ar_tran_rec

In my sql_slow query, I have this 5 times, using different dates -

LEFT JOIN ccc.ar_trans trans_alloc_curr ON
trans_alloc_curr.tran_type = alloc_curr.tran_type AND
trans_alloc_curr.tran_row_id = alloc_curr.tran_row_id AND
trans_alloc_curr.tran_date <= '2015-09-30'

Is it possible that it has to perform a full scan of each of the underlying 
tables to make the join?

If so, is it possible to speed this up?

Frank

Re: [GENERAL] Joining 16 tables seems slow

[Pavel Stehule]

2017-09-12 14:01 GMT+02:00 Tom Lane :

> "Frank Millman"  writes:
> > Pavel Stehule wrote:
> >> 2017-09-12 8:45 GMT+02:00 Frank Millman :
> >>>   I am experimenting with optimising a SQL statement. One version uses
> 4 LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the
> filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no
> CASE statements.
>
> >> you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher
> number 14 maybe 16
>
> > I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.
>
> For this you need to increase join_collapse_limit, not
> from_collapse_limit.  (Usually, though, there's little reason not to keep
> them the same.)
>

sure - my mistake - I though it.

Thank you

Pavel

>
> regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Joining 16 tables seems slow

[Chris Travers]

On Tue, Sep 12, 2017 at 3:15 PM, Frank Millman  wrote:

>
> 2017-09-12 12:39 GMT+02:00 Pavel Stehule :
>
>>
>>
>> 2017-09-12 12:25 GMT+02:00 Frank Millman :
>>
>>> Pavel Stehule wrote:
>>>
>>> 2017-09-12 9:36 GMT+02:00 Frank Millman :
>>>
 Pavel Stehule wrote:
 >
 > 2017-09-12 8:45 GMT+02:00 Frank Millman :

> I am using 9.4.4 on Fedora 22.
>
> I am experimenting with optimising a SQL statement. One version uses 4
> LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the
> filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no
> CASE statements.
>
> My test involves selecting a single row. Both versions work. The first
> version takes 0.06 seconds. The second takes 0.23 seconds. On further
> experimentation, the time for the second one seems to taken in setting up
> the joins, because if I omit selecting anything from the joined tables, it
> still takes 0.23 seconds.
>
 >
 > please send result of explain analyze
 >
 > you can experimentally try increase FROM_COLLAPSE_LIMIT to some
 higher number 14 maybe 16
 >
 I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.

 I have attached files containing my SQL command, and the results of
 EXPLAIN ANALYSE

>>>
>>> > please use https://explain.depesz.com/ for both plans (slow, fast)
>>>
>>>
>>> Here are the results -
>>>
>>> sql_slow - https://explain.depesz.com/s/9vn3
>>>
>>> sql_fast - https://explain.depesz.com/s/oW0F
>>>
>>
>> I don't see any issue there - it looks like some multi dimensional query
>> and it should not be well optimized due not precious estimations. The slow
>> query has much more complex - some bigger logic is under nested loop -
>> where estimation is not fully correct, probably due dependencies between
>> columns.
>>
>> what does SET enable_nestloop to off;
>>
> >
> > from statistics - the ar_tran_inv table is scanned 6x in slow query and
> 2times in fast query. Maybe there should be some index
> >
>
> Setting enable_nestloop to off makes no difference.
>
> Setting from_collapse_limit and join_collapse_limit to 16, as suggested by
> Tom, actually slowed it down.
>
> I mentioned before that I was running this from python, which complicated
> it slightly. I have now saved the command to a file on the Fedora side, so
> I can execute it in psql using the ‘\i’ command. It makes life easier, and
> I can use ‘\timing’ to time it. It shows exactly the same results.
>
> It could be an index problem, but I have just double-checked that, if I
> remove the lines from the body of the statement that actually select from
> the joined tables, it makes virtually no difference. However, maybe the
> planner checks to see what indexes it has before preparing the query, so
> that does not rule it out as a possibility.
>
> I will play with it some more tomorrow, when my  brain is a bit fresher. I
> will report back with any results.
>

I am not convinced that the nested loop is a problem here.  I cannot think
of a faster join plan than a nested loop when you only have one iteration
of the loop (and looking through I did not see any loop counts above 1).

If you read and count ms carefully you will find that ar_tran_inv is
scanned 6 times and each of these times is taking about 25ms.  25x6 is half
of your query time right there and then you have the overhead in the joins
on top of that.  Quick eyeball estimates is that this is where approx 200ms
of your query time comes from.  Looking at this in more detail it doesn't
look

This is not a problem with too many tables in the join but the fact that
you are joining the same tables in multiple times in ways you end up
needing to repeatedly sequentially scan them.

I also don't think an index is going to help unless you have accounting
data going way back (since you are looking for about a year's worth of
data) or unless 90% of your transactions get marked as deleted.  So I think
you are stuck with the sequential scans on this table and optimizing will
probably mean reducing the number of times you scan that table.

>
> Frank
>
>



-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] Joining 16 tables seems slow

[Frank Millman]

2017-09-12 12:39 GMT+02:00 Pavel Stehule :



  2017-09-12 12:25 GMT+02:00 Frank Millman :

Pavel Stehule wrote:

2017-09-12 9:36 GMT+02:00 Frank Millman :

  Pavel Stehule wrote:
  > 
  > 2017-09-12 8:45 GMT+02:00 Frank Millman :

I am using 9.4.4 on Fedora 22.

I am experimenting with optimising a SQL statement. One version uses 4 
LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the 
filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE 
statements.

My test involves selecting a single row. Both versions work. The first 
version takes 0.06 seconds. The second takes 0.23 seconds. On further 
experimentation, the time for the second one seems to taken in setting up the 
joins, because if I omit selecting anything from the joined tables, it still 
takes 0.23 seconds.
  > 
  > please send result of explain analyze
  > 
  > you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher 
number 14 maybe 16
  > 
  I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.

  I have attached files containing my SQL command, and the results of 
EXPLAIN ANALYSE

> please use https://explain.depesz.com/ for both plans (slow, fast)


Here are the results - 

sql_slow - https://explain.depesz.com/s/9vn3

sql_fast - https://explain.depesz.com/s/oW0F

  I don't see any issue there - it looks like some multi dimensional query and 
it should not be well optimized due not precious estimations. The slow query 
has much more complex - some bigger logic is under nested loop - where 
estimation is not fully correct, probably due dependencies between columns.

  what does SET enable_nestloop to off;
> 
> from statistics - the ar_tran_inv table is scanned 6x in slow query and 
> 2times in fast query. Maybe there should be some index 
>

Setting enable_nestloop to off makes no difference.

Setting from_collapse_limit and join_collapse_limit to 16, as suggested by Tom, 
actually slowed it down.

I mentioned before that I was running this from python, which complicated it 
slightly. I have now saved the command to a file on the Fedora side, so I can 
execute it in psql using the ‘\i’ command. It makes life easier, and I can use 
‘\timing’ to time it. It shows exactly the same results.

It could be an index problem, but I have just double-checked that, if I remove 
the lines from the body of the statement that actually select from the joined 
tables, it makes virtually no difference. However, maybe the planner checks to 
see what indexes it has before preparing the query, so that does not rule it 
out as a possibility.

I will play with it some more tomorrow, when my  brain is a bit fresher. I will 
report back with any results.

Frank

Re: [GENERAL] Joining 16 tables seems slow

[Tom Lane]

"Frank Millman"  writes:
> Pavel Stehule wrote:
>> 2017-09-12 8:45 GMT+02:00 Frank Millman :
>>>   I am experimenting with optimising a SQL statement. One version uses 4 
>>> LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the 
>>> filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no 
>>> CASE statements.

>> you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher 
>> number 14 maybe 16

> I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.

For this you need to increase join_collapse_limit, not
from_collapse_limit.  (Usually, though, there's little reason not to keep
them the same.)

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Joining 16 tables seems slow

[Pavel Stehule]

2017-09-12 12:39 GMT+02:00 Pavel Stehule :

>
>
> 2017-09-12 12:25 GMT+02:00 Frank Millman :
>
>> Pavel Stehule wrote:
>>
>> 2017-09-12 9:36 GMT+02:00 Frank Millman :
>>
>>> Pavel Stehule wrote:
>>> >
>>> > 2017-09-12 8:45 GMT+02:00 Frank Millman :
>>>
 I am using 9.4.4 on Fedora 22.

 I am experimenting with optimising a SQL statement. One version uses 4
 LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the
 filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no
 CASE statements.

 My test involves selecting a single row. Both versions work. The first
 version takes 0.06 seconds. The second takes 0.23 seconds. On further
 experimentation, the time for the second one seems to taken in setting up
 the joins, because if I omit selecting anything from the joined tables, it
 still takes 0.23 seconds.

>>> >
>>> > please send result of explain analyze
>>> >
>>> > you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher
>>> number 14 maybe 16
>>> >
>>> I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.
>>>
>>> I have attached files containing my SQL command, and the results of
>>> EXPLAIN ANALYSE
>>>
>>
>> > please use https://explain.depesz.com/ for both plans (slow, fast)
>>
>>
>> Here are the results -
>>
>> sql_slow - https://explain.depesz.com/s/9vn3
>>
>> sql_fast - https://explain.depesz.com/s/oW0F
>>
>
> I don't see any issue there - it looks like some multi dimensional query
> and it should not be well optimized due not precious estimations. The slow
> query has much more complex - some bigger logic is under nested loop -
> where estimation is not fully correct, probably due dependencies between
> columns.
>
> what does SET enable_nestloop to off;
>

from statistics - the ar_tran_inv table is scanned 6x in slow query and
2times in fast query. Maybe there should be some index



> ?
>
> Regards
>
> Pavel
>
>
>
>>
>> Frank
>>
>>
>
>

Re: [GENERAL] Joining 16 tables seems slow

[Pavel Stehule]

2017-09-12 12:25 GMT+02:00 Frank Millman :

> Pavel Stehule wrote:
>
> 2017-09-12 9:36 GMT+02:00 Frank Millman :
>
>> Pavel Stehule wrote:
>> >
>> > 2017-09-12 8:45 GMT+02:00 Frank Millman :
>>
>>> I am using 9.4.4 on Fedora 22.
>>>
>>> I am experimenting with optimising a SQL statement. One version uses 4
>>> LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the
>>> filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no
>>> CASE statements.
>>>
>>> My test involves selecting a single row. Both versions work. The first
>>> version takes 0.06 seconds. The second takes 0.23 seconds. On further
>>> experimentation, the time for the second one seems to taken in setting up
>>> the joins, because if I omit selecting anything from the joined tables, it
>>> still takes 0.23 seconds.
>>>
>> >
>> > please send result of explain analyze
>> >
>> > you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher
>> number 14 maybe 16
>> >
>> I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.
>>
>> I have attached files containing my SQL command, and the results of
>> EXPLAIN ANALYSE
>>
>
> > please use https://explain.depesz.com/ for both plans (slow, fast)
>
>
> Here are the results -
>
> sql_slow - https://explain.depesz.com/s/9vn3
>
> sql_fast - https://explain.depesz.com/s/oW0F
>

I don't see any issue there - it looks like some multi dimensional query
and it should not be well optimized due not precious estimations. The slow
query has much more complex - some bigger logic is under nested loop -
where estimation is not fully correct, probably due dependencies between
columns.

what does SET enable_nestloop to off;

?

Regards

Pavel



>
> Frank
>
>

Re: [GENERAL] Joining 16 tables seems slow

[Frank Millman]

Pavel Stehule wrote:

2017-09-12 9:36 GMT+02:00 Frank Millman :

  Pavel Stehule wrote:
  > 
  > 2017-09-12 8:45 GMT+02:00 Frank Millman :

I am using 9.4.4 on Fedora 22.

I am experimenting with optimising a SQL statement. One version uses 4 LEFT 
JOIN’s and a 5-way CASE statement in the body. The second moves the filtering 
into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE statements.

My test involves selecting a single row. Both versions work. The first 
version takes 0.06 seconds. The second takes 0.23 seconds. On further 
experimentation, the time for the second one seems to taken in setting up the 
joins, because if I omit selecting anything from the joined tables, it still 
takes 0.23 seconds.
  > 
  > please send result of explain analyze
  > 
  > you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher 
number 14 maybe 16
  > 
  I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.

  I have attached files containing my SQL command, and the results of EXPLAIN 
ANALYSE

> please use https://explain.depesz.com/ for both plans (slow, fast)


Here are the results - 

sql_slow - https://explain.depesz.com/s/9vn3

sql_fast - https://explain.depesz.com/s/oW0F

Frank
 

Re: [GENERAL] Joining 16 tables seems slow

[Pavel Stehule]

2017-09-12 9:36 GMT+02:00 Frank Millman :

> Pavel Stehule wrote:
> >
> > 2017-09-12 8:45 GMT+02:00 Frank Millman :
>
>> I am using 9.4.4 on Fedora 22.
>>
>> I am experimenting with optimising a SQL statement. One version uses 4
>> LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the
>> filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no
>> CASE statements.
>>
>> My test involves selecting a single row. Both versions work. The first
>> version takes 0.06 seconds. The second takes 0.23 seconds. On further
>> experimentation, the time for the second one seems to taken in setting up
>> the joins, because if I omit selecting anything from the joined tables, it
>> still takes 0.23 seconds.
>>
> >
> > please send result of explain analyze
> >
> > you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher
> number 14 maybe 16
> >
> I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.
>
> I have attached files containing my SQL command, and the results of
> EXPLAIN ANALYSE
>

please use https://explain.depesz.com/ for both plans (slow, fast)

Regards

Pavel


>
> Frank
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>

Re: [GENERAL] Joining 16 tables seems slow

[Frank Millman]

Ron Johnson wrote:
> 
On 09/12/2017 01:45 AM, Frank Millman wrote:

Hi all

I am using 9.4.4 on Fedora 22.

I am experimenting with optimising a SQL statement. One version uses 4 LEFT 
JOIN’s and a 5-way CASE statement in the body. The second moves the filtering 
into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE statements.

My test involves selecting a single row. Both versions work. The first version 
takes 0.06 seconds. The second takes 0.23 seconds. On further experimentation, 
the time for the second one seems to taken in setting up the joins, because if 
I omit selecting anything from the joined tables, it still takes 0.23 seconds.

Exactly the same exercise on Sql Server results in 0.06 seconds for both 
versions.

I realise that, if I was selecting a large number of rows, 0.23 seconds is 
trivial and the overall result could be different. But still, it seems odd.

> Just out of curiosity, what if you PREPARE the statement, and take multiple 
> timings?


My setup is a bit complicated, as I am executing the commands from a python 
program on Windows against a PostgreSQL database on Fedora, so I hope I did it 
correctly!With that caveat, the results are that the time was reduced from 0.23 
seconds to 0.22 seconds. The difference is consistent, so I think it is 
real.Frank 

Re: [GENERAL] Joining 16 tables seems slow

[Frank Millman]

Pavel Stehule wrote:
> 
> 2017-09-12 8:45 GMT+02:00 Frank Millman :

  I am using 9.4.4 on Fedora 22.

  I am experimenting with optimising a SQL statement. One version uses 4 LEFT 
JOIN’s and a 5-way CASE statement in the body. The second moves the filtering 
into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE statements.

  My test involves selecting a single row. Both versions work. The first 
version takes 0.06 seconds. The second takes 0.23 seconds. On further 
experimentation, the time for the second one seems to taken in setting up the 
joins, because if I omit selecting anything from the joined tables, it still 
takes 0.23 seconds.
> 
> please send result of explain analyze
> 
> you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher number 
> 14 maybe 16
> 
I tried increasing FROM_COLLAPSE_LIMIT, but it made no difference.

I have attached files containing my SQL command, and the results of EXPLAIN 
ANALYSE

Frank
 SELECT a.row_id,
(SELECT (b.inv_net_tot_cus + b.inv_tax_tot_cus + b.crn_net_tot_cus + 
b.crn_tax_tot_cus
+ b.jnl_tot_cus + b.rec_net_tot_cus + b.rec_dsc_tot_cus + b.rec_dtx_tot_cus)
FROM ccc.ar_cust_totals b
WHERE b.cust_row_id = a.row_id AND b.tran_date <= '2015-09-30'
ORDER BY b.tran_date DESC LIMIT 1)
as "balance_cust AS [DECTEXT]",

COALESCE(SUM(due_curr.amount_cust), 0) +
COALESCE(SUM(alloc_curr.alloc_cust + alloc_curr.disc_cust), 0)
AS "bal_cust_curr AS [DECTEXT]",
COALESCE(SUM(due_30.amount_cust), 0) +
COALESCE(SUM(alloc_30.alloc_cust + alloc_30.disc_cust), 0)
AS "bal_cust_30 AS [DECTEXT]",
COALESCE(SUM(due_60.amount_cust), 0) +
COALESCE(SUM(alloc_60.alloc_cust + alloc_60.disc_cust), 0)
AS "bal_cust_60 AS [DECTEXT]",
COALESCE(SUM(due_90.amount_cust), 0) +
COALESCE(SUM(alloc_90.alloc_cust + alloc_90.disc_cust), 0)
AS "bal_cust_90 AS [DECTEXT]",
COALESCE(SUM(due_120.amount_cust), 0) +
COALESCE(SUM(alloc_120.alloc_cust + alloc_120.disc_cust), 0)
AS "bal_cust_120 AS [DECTEXT]"

FROM ccc.ar_customers a
LEFT JOIN ccc.ar_trans trans ON trans.cust_row_id = a.row_id

LEFT JOIN ccc.ar_trans_due due_curr ON
due_curr.tran_type = trans.tran_type AND due_curr.tran_row_id = 
trans.tran_row_id
AND trans.tran_date > '2015-08-31'
LEFT JOIN ccc.ar_trans_alloc alloc_curr ON
alloc_curr.due_row_id = due_curr.row_id
LEFT JOIN ccc.ar_trans trans_alloc_curr ON
trans_alloc_curr.tran_type = alloc_curr.tran_type AND
trans_alloc_curr.tran_row_id = alloc_curr.tran_row_id AND
trans_alloc_curr.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_30 ON
due_30.tran_type = trans.tran_type AND due_30.tran_row_id = 
trans.tran_row_id
AND trans.tran_date > '2015-07-31' AND trans.tran_date <= '2015-08-31'
LEFT JOIN ccc.ar_trans_alloc alloc_30 ON
alloc_30.due_row_id = due_30.row_id
LEFT JOIN ccc.ar_trans trans_alloc_30 ON
trans_alloc_30.tran_type = alloc_30.tran_type AND
trans_alloc_30.tran_row_id = alloc_30.tran_row_id AND
trans_alloc_30.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_60 ON
due_60.tran_type = trans.tran_type AND due_60.tran_row_id = 
trans.tran_row_id
AND trans.tran_date > '2015-06-30' AND trans.tran_date <= '2015-07-31'
LEFT JOIN ccc.ar_trans_alloc alloc_60 ON
alloc_60.due_row_id = due_60.row_id
LEFT JOIN ccc.ar_trans trans_alloc_60 ON
trans_alloc_60.tran_type = alloc_60.tran_type AND
trans_alloc_60.tran_row_id = alloc_60.tran_row_id AND
trans_alloc_60.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_90 ON
due_90.tran_type = trans.tran_type AND due_90.tran_row_id = 
trans.tran_row_id
AND trans.tran_date > '2015-05-31' AND trans.tran_date <= '2015-06-30'
LEFT JOIN ccc.ar_trans_alloc alloc_90 ON
alloc_90.due_row_id = due_90.row_id
LEFT JOIN ccc.ar_trans trans_alloc_90 ON
trans_alloc_90.tran_type = alloc_90.tran_type AND
trans_alloc_90.tran_row_id = alloc_90.tran_row_id AND
trans_alloc_90.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_120 ON
due_120.tran_type = trans.tran_type AND due_120.tran_row_id = 
trans.tran_row_id
AND trans.tran_date <= '2015-05-31'
LEFT JOIN ccc.ar_trans_alloc alloc_120 ON
alloc_120.due_row_id = due_120.row_id
LEFT JOIN ccc.ar_trans trans_alloc_120 ON
trans_alloc_120.tran_type = alloc_120.tran_type AND
trans_alloc_120.tran_row_id = alloc_120.tran_row_id AND
trans_alloc_120.tran_date <= '2015-09-30'

WHERE a.ledger_row_id = ? AND a.party_row_id = ? AND a.deleted_id = ?
GROUP BY a.row_id
('HashAggregate  (cost=11123.83..11211.17 rows=1 width=234) (actual 
time=299.781..299.782 rows=1 loops=1)',)
('  Group Key: a.row_id',)
('  ->  Hash Right Join  (cost=9833.36..11122.59 rows=31 width=234) (actual 
time=295.962..296.496 rows=1801 loops=1)',)
('Hash Cond: (("*SELECT* 1_5".tran_type = (alloc_120.tran_type)::text) 
AND ("*SELECT* 1_5".tran_row_id = alloc_120.tran_row_id))',)
('->  Append  

Re: [GENERAL] Joining 16 tables seems slow

[Ron Johnson]

On 09/12/2017 01:45 AM, Frank Millman wrote:

Hi all
I am using 9.4.4 on Fedora 22.
I am experimenting with optimising a SQL statement. One version uses 4 
LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the 
filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no 
CASE statements.
My test involves selecting a single row. Both versions work. The first 
version takes 0.06 seconds. The second takes 0.23 seconds. On further 
experimentation, the time for the second one seems to taken in setting up 
the joins, because if I omit selecting anything from the joined tables, it 
still takes 0.23 seconds.
Exactly the same exercise on Sql Server results in 0.06 seconds for both 
versions.
I realise that, if I was selecting a large number of rows, 0.23 seconds is 
trivial and the overall result could be different. But still, it seems odd.


Just out of curiosity, what if you PREPARE the statement, and take multiple 
timings?


--
World Peace Through Nuclear Pacification

Re: [GENERAL] Joining 16 tables seems slow

[Pavel Stehule]

hi

2017-09-12 8:45 GMT+02:00 Frank Millman :

> Hi all
>
> I am using 9.4.4 on Fedora 22.
>
> I am experimenting with optimising a SQL statement. One version uses 4
> LEFT JOIN’s and a 5-way CASE statement in the body. The second moves the
> filtering into the JOIN section, and I end up with 16 LEFT JOIN’s and no
> CASE statements.
>
> My test involves selecting a single row. Both versions work. The first
> version takes 0.06 seconds. The second takes 0.23 seconds. On further
> experimentation, the time for the second one seems to taken in setting up
> the joins, because if I omit selecting anything from the joined tables, it
> still takes 0.23 seconds.
>
> Exactly the same exercise on Sql Server results in 0.06 seconds for both
> versions.
>
> I realise that, if I was selecting a large number of rows, 0.23 seconds is
> trivial and the overall result could be different. But still, it seems odd.
>
> Is this normal, or should I investigate further?
>

please send result of explain analyze

you can experimentally try increase FROM_COLLAPSE_LIMIT to some higher
number 14 maybe 16

regards



>
> Frank Millman
>
>

[GENERAL] Joining 16 tables seems slow

[Frank Millman]

Hi all

I am using 9.4.4 on Fedora 22.

I am experimenting with optimising a SQL statement. One version uses 4 LEFT 
JOIN’s and a 5-way CASE statement in the body. The second moves the filtering 
into the JOIN section, and I end up with 16 LEFT JOIN’s and no CASE statements.

My test involves selecting a single row. Both versions work. The first version 
takes 0.06 seconds. The second takes 0.23 seconds. On further experimentation, 
the time for the second one seems to taken in setting up the joins, because if 
I omit selecting anything from the joined tables, it still takes 0.23 seconds.

Exactly the same exercise on Sql Server results in 0.06 seconds for both 
versions.

I realise that, if I was selecting a large number of rows, 0.23 seconds is 
trivial and the overall result could be different. But still, it seems odd.

Is this normal, or should I investigate further?

Frank Millman