Re: [GENERAL] a JOIN to a VIEW seems slow - Mailing list pgsql-general

From Frank Millman
Subject Re: [GENERAL] a JOIN to a VIEW seems slow
Date
Msg-id 0E9487FCEEF44458AEF2CEB2ECEFDCE8@FrankLaptop
Whole thread Raw
In response to [GENERAL] a JOIN to a VIEW seems slow  ("Frank Millman" <frank@chagford.com>)
Responses Re: [GENERAL] a JOIN to a VIEW seems slow
List pgsql-general
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
 

pgsql-general by date:

Previous
From: Jerry Levan
Date:
Subject: [GENERAL] phpPgAdmin 6 on a MAC (High Sierra)
Next
From: David Rowley
Date:
Subject: Re: [GENERAL] a JOIN to a VIEW seems slow