> On 2 Oct 2017, at 8:32, Frank Millman <fr...@chagford.com> wrote: > > > From: Frank Millman > Sent: Friday, September 22, 2017 7:34 AM > To: pgsql-general@postgresql.org > Subject: Re: a JOIN to a VIEW seems slow > > > On Fri, Sep 22, 2017 at 7:34 AM, Frank Millman wrote: > > > > On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote: > > > Something is not adding up here. Can you EXPLAIN ANALYZE the 26 minute > > > query? > > > > > Here it is - > > > > https://explain.depesz.com/s/cwm > > > > Just checking – is this under investigation, or is this thread considered > closed? > > Frank
There are a few problems keeping track of this issue. First of all, above plan does not include the query (I don't know whether that's a thing with depesz's plan analyzer, but ISTR seeing plans _with_ their queries in other cases). That means we have to track back through the thread (quite a bit) to find a query that _may_ be the one that the plan is for. Add that to the fact that most of us are busy people, so we have to invest too much time into your problem to be able to help - and hence we don't. The second problem is that the query plan highlights a couple of slow sequential scans on ar_tran_inv and ar_tran_rec, but those tables are not in your query from Sep 21st. That makes it impossible for anyone to pinpoint the problem for you. They're probably in your views somewhere, but we can't see where. Looking at that query though, it seems to me that it could help to aggregate the results on cust_row_id in the inner query (aliased as q) to reduce the result set that the outer query needs to handle. It's possible that the query planner is smart enough to detect this, I can't tell from the plan, but I wouldn't bet on it. The query plan for that inner query should be interesting as well, especially if moving the aggregation inside does not help. Another possible optimisation would be to add a lower bound for tran_date, if such is possible for your case. Currently you only have an upper bound: tran_date <= '2015-09-30'. Even if there is no data from, say, before 2015-01-01, the query planner does not necessarily know that and may assume that most rows in the table/view will match the upper-bound condition - in which case a sequential scan is probably fastest. That may be why you don't see Postgres use the index on those columns you mentioned at some point. Now, apparently you have an index on columns tran_type and tran_row_id, while tran_row_id is the primary key? In that case I'd suggest you drop that index: Any value of tran_row_id will have a single value of tran_type and you're joining on the PK already. Meanwhile, the condition on tran_type in the query only serves to complicate the query. Oh, BTW, those LEFT JOINs will behave as INNER JOINs, because you use columns from the right-hand side (alloc_trans.tran_date and due_trans.tran_date respectively) in your WHERE clauses without allowing them to be NULL. If you want those parts to behave like proper LEFT JOINs, either add OR xxx.tran_date IS NULL or move those expressions into the JOIN conditions. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
