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 | 13E8231EF68D405E93F3928F18AC9B11@FrankLaptop Whole thread Raw |
| In response to | Re: [GENERAL] a JOIN to a VIEW seems slow (Alban Hertroys <haramrae@gmail.com>) |
| List | pgsql-general |
On 4 Oct 2017, at 9:19 PM, Alban Hertroys wrote:
> On 2 Oct 2017, at 8:32, Frank Millman <frank@chagford.com> wrote:
>
>
> 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
> >
>
> 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?
>
>
>
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.
[snip a lot of good suggestions]
[snip a lot of good suggestions]
Thanks for the reply, Alban.
I agree that the query is a bit complex and not easy to analyse. I have taken note of all your suggestions and will investigate them further.
I did attempt to narrow this down to a simple example in one of my earlier posts. I could not find a way to provide a link to a single message, but this is the thread -
and the relevant post is the third one down, dated 21st September.
You will have to read the whole message for the details, but the key point was the difference between these two queries -
2. =======================================================
EXPLAIN SELECT * FROM ar_trans WHERE tran_type = ‘ar_inv’ AND tran_row_id = 1;
QUERY PLAN
-------------------------------------------------------------------------------------------
Append (cost=0.29..8.32 rows=1 width=117)
-> Index Scan using ar_tran_inv_pkey on ar_tran_inv (cost=0.29..8.31 rows=1 width=46)
Index Cond: (row_id = 1)
Filter: posted
(4 rows)
This is a select against the view ‘ar_trans’. It has worked out that the underlying table to use is ‘ar_tran_inv’, and performed an indexed read.
4. =======================================================
EXPLAIN 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 where a.row_id = 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Hash Right Join (cost=8.32..2072.99 rows=1 width=169)
Hash Cond: ((('ar_inv'::text) = (a.tran_type)::text) AND (ar_tran_inv.row_id = a.tran_row_id))
-> Append (cost=0.00..1739.47 rows=43357 width=117)
-> Seq Scan on ar_tran_inv (cost=0.00..676.01 rows=21601 width=46)
Filter: posted
-> Seq Scan on ar_tran_crn (cost=0.00..13.88 rows=155 width=124)
Filter: posted
-> Seq Scan on ar_tran_rec (cost=0.00..616.01 rows=21601 width=40)
Filter: posted
-> Hash (cost=8.31..8.31 rows=1 width=52)
-> Index Scan using ar_trans_due_pkey on ar_trans_due a (cost=0.29..8.31 rows=1 width=52)
Index Cond: (row_id = 1)
(12 rows)
Here I have set up a join against the view ‘ar_trans’. It seems to have all the information necessary to perform an indexed read, but instead it performs a sequential scan of all three of the underlying tables.
If anyone wants to take this further, maybe this is a good place to start.
I do have a workaround. It is not pretty – denormalise my data to avoid the need for a join against the view. But it works, so there is no longer any urgency on my part.
Thanks
Frank
pgsql-general by date: