|
On 4 Oct 2017, at 9:19 PM, Alban Hertroys wrote: > On 2 Oct 2017, at 8:32, Frank Millman <frank@xxxxxxxxxxxx> 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 > > >
>
>
>
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] 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
|
