Re: [GENERAL] a JOIN to a VIEW seems slow

Поиск
Список
Период
Сортировка
От Frank Millman
Тема Re: [GENERAL] a JOIN to a VIEW seems slow
Дата
Msg-id 3BBA6BBA6D3A48AEB98F1E850D2987C7@FrankLaptop
обсуждение исходный текст
Ответ на [GENERAL] a JOIN to a VIEW seems slow  ("Frank Millman" <frank@chagford.com>)
Список 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 -
>  
>  
 
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
 

В списке pgsql-general по дате отправления:

Предыдущее
От: Melvin Davidson
Дата:
Сообщение: Re: [GENERAL] Is auto-analyze as thorough as manual analyze?
Следующее
От: Vladimir Mihailenco
Дата:
Сообщение: Re: [GENERAL] shared_buffers smaller than max_wal_size