|
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 -
>
>
There is one thing I have not mentioned. I am pretty sure it has no effect
on the outcome, but just in case, here it is.
The underlying physical tables, ‘ar_tran_inv’, ‘ar_tran_crn’, and
‘ar_tran_rec’, have this index declared -
CREATE UNIQUE INDEX _ar_tran_inv ON ar_tran_inv
(tran_number) WHERE deleted_id = 0;
and similar for the other two tables.
I have not included the column ‘deleted_id’ in the VIEW ‘ar_trans’, so I
cannot add ‘WHERE deleted_id = 0’ to any queries. This could mean a slow result if sorting by ‘tran_number’ or joining on
‘tran_number’.
However, as this particular query joins on ‘tran_type’ (a literal string)
and ‘tran_row_id’ (the primary key to the underlying table), I don’t think
it causes a problem.
[UPDATE] I actually tried adding ‘deleted_id’ to the VIEW, and including it
in the WHERE clause, but the timings did not improve.
Frank
|
