From: | "Frank Millman" <frank(at)chagford(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: a JOIN to a VIEW seems slow |
Date: | 2017-09-24 06:09:36 |
Message-ID: | 3BBA6BBA6D3A48AEB98F1E850D2987C7@FrankLaptop |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
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
From | Date | Subject | |
---|---|---|---|
Next Message | Vladimir Mihailenco | 2017-09-24 09:03:14 | Re: shared_buffers smaller than max_wal_size |
Previous Message | Melvin Davidson | 2017-09-23 22:48:17 | Re: Is auto-analyze as thorough as manual analyze? |