Search Postgresql Archives

Re: a JOIN to a VIEW seems slow

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Sep 21, 2017 at 12:02 PM, Frank Millman <frank@xxxxxxxxxxxx> wrote:
> On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:
>>
>> > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman <frank@xxxxxxxxxxxx>
>> > wrote:
>> >
>>
>> I did not get any response to this, but I am still persevering, and feel
>>
>> that I am getting closer. Instead of waiting 26 minutes for a result, I
>>
>> realise that I can learn a lot by using EXPLAIN. This is what I have found
>>
>> out.
>>
>
>
>>
> Something is not adding up here.  Can you EXPLAIN ANALYZE the 26 minute
> query?
>>
>
>
> I’m working on it, but my machine is playing up and it is getting late, so I
> will try again tomorrow.
>
> A passing comment – the 26 minute query is more complex, so will need some
> explaining (no pun intended). I was hoping that my simplified example would
> illustrate what I think is the problem.
>
> Anyway, here is the query -
>
> SELECT q.cust_row_id,
> SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END
>     ) AS "balance_curr AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-08-31' AND q.tran_date > '2015-07-31'
> THEN q.balance ELSE 0 END
>     ) AS "balance_30 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-07-31' AND q.tran_date > '2015-06-30'
> THEN q.balance ELSE 0 END
>     ) AS "balance_60 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-06-30' AND q.tran_date > '2015-05-31'
> THEN q.balance ELSE 0 END
>     ) AS "balance_90 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-05-31' THEN q.balance ELSE 0 END
>     ) AS "balance_120 AS [DECTEXT]"
> FROM
> (SELECT
>     due_trans.cust_row_id,
>     due_trans.tran_date,
>     trans_due.amount_cust +
>         COALESCE((SELECT SUM(trans_alloc.alloc_cust+trans_alloc.disc_cust)
>             FROM prop.ar_trans_alloc trans_alloc
>             LEFT JOIN prop.ar_trans alloc_trans ON
>                 alloc_trans.tran_type = trans_alloc.tran_type
>                 AND alloc_trans.tran_row_id = trans_alloc.tran_row_id
>             WHERE trans_alloc.due_row_id = trans_due.row_id
>                 AND alloc_trans.tran_date <= '2015-09-30'
>             ), 0)
>     AS balance
>     FROM prop.ar_trans_due trans_due
>     LEFT JOIN prop.ar_trans due_trans ON
>         due_trans.tran_type = trans_due.tran_type
>         AND due_trans.tran_row_id = trans_due.tran_row_id
>     WHERE due_trans.tran_date <= '2015-09-30'
> ) AS q
> GROUP BY q.cust_row_id
> ORDER BY q.cust_row_id;
>
> I will report back with the EXPLAIN ANALYSE tomorrow.

Thank you.  It's a minor point, but take a look at the FILTER syntax here:
https://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-AGGREGATES

For example,
> SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END
>     ) AS "balance_curr AS [DECTEXT]",

Could be rewritten as:
SUM(q.balance) FILTER (WHERE  q.tran_date > '2015-08-31')
  AS "balance_curr AS [DECTEXT]",

Besides being more terse and clear, FILTER expressions in my
experience tend to be somewhat faster than aggregations over CASE
statements.

merlin


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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux