|
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
|
