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; What is the performance with this portion simplified out? > 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) Change that to just '0' and rerun the query. If timings are good, I think we want to explore converting this to LATERAL type join. I think (but am not sure) this is defeating the optimizer. Also, is this the actual query you want to run quickly? You are not filtering on cust_row_id? merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
