Re: a JOIN to a VIEW seems slow

On 7 October 2017 at 22:34, Frank Millman <frank(at)chagford(dot)com> wrote:
> 4. Select from ar_trans_due including join to ar_trans,
> plus sub_select from ar_trans_alloc including join to ar_trans
> SELECT *,
> (SELECT SUM(c.alloc_cust)
> FROM ar_trans_alloc c
> LEFT JOIN ar_trans d
> ON d.tran_type = c.tran_type
> AND d.tran_row_id = c.tran_row_id
> WHERE c.due_row_id = a.row_id)
> FROM ar_trans_due a
> LEFT JOIN ar_trans b
> ON b.tran_type = a.tran_type
> AND b.tran_row_id = a.tran_row_id
>
> Sql Server: 1.01 sec; PostgreSQL 1683 sec

Yeah, PostgreSQL does not make any effort to convert subqueries in the
target list into joins. SQL server does.

The way you have written the query might be good if there are not so
many rows in the outer part of the query, however, as the number of
rows increases then performance will get worse pretty quickly.

You'll probably find it'll run faster if you convert the subquery in
the target list into a join with a GROUP BY, like:

SELECT a.*,b.*,c.sum_alloc_cust
FROM ar_trans_due a
LEFT JOIN ar_trans b
ON b.tran_type = a.tran_type
AND b.tran_row_id = a.tran_row_id
LEFT JOIN (SELECT c.due_row_id,
SUM(c.alloc_cust) AS sum_alloc_cust
FROM ar_trans_alloc c
LEFT JOIN ar_trans d
ON d.tran_type = c.tran_type
AND d.tran_row_id = c.tran_row_id
GROUP BY c.due_row_id
) c ON c.due_row_id = a.row_id;

SQL Server will probably be doing this rewrite.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services