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

> On 2 Oct 2017, at 8:32, Frank Millman <frank@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
whetherthat's a thing with depesz's plan analyzer, but ISTR seeing plans _with_ their queries in other cases). That
meanswe have to track back through the thread (quite a bit) to find a query that _may_ be the one that the plan is for.
Addthat 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,
butthose 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
issmart enough to detect this, I can't tell from the plan, but I wouldn't bet on it. The query plan for that inner
queryshould 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
youonly 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
thatmost 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
caseI'd suggest you drop that index: Any value of tran_row_id will have a single value of tran_type and you're joining
onthe 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_dateand due_trans.tran_date respectively) in your WHERE clauses without allowing them to be NULL. If
youwant those parts to behave like proper LEFT JOINs, either add OR xxx.tran_date IS NULL or move those expressions
intothe 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