On 5 Oct 2017, at 9:51 AM, Frank Millman wrote:
>
> I should have re-stated the reason for my original post.
>
> Exactly the same query, on exactly the same data, takes 1.8 seconds on Sql Server, 1.0 seconds on SQLite3, and 1607 seconds, or 26 minutes, on PostgreSQL 9.4.4.
>
I will give this another shot. I have made this as simple as I can. Just as a reminder, ‘ar_trans’ is a VIEW.
1. Simple select from ‘artrans_due’
SELECT *
FROM ar_trans_due
Sql Server: 0.56 sec; PostgreSQL 0.41 sec
2. Select from ‘ar_trans_due’ including join to ‘ar_trans’
SELECT *
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: 0.90 sec; PostgreSQL 0.70 sec
3. Select from ar_trans_due including join to ar_trans,
plus sub-select from ar_trans_alloc
SELECT *,
(SELECT SUM(c.alloc_cust)
FROM ar_trans_alloc c
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: 0.92 sec; PostgreSQL 1.00 sec
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
As you can see, it is the join inside the sub-select that kills it.
Someone has kindly tested this for me on version 9.6.5 and on version 10, and the results are similar.
Here is the EXPLAIN ANALYSE for the last of the above queries -
Frank Millman