Re: Adding nextval() to a select caused hang/very slow execution

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Adding nextval() to a select caused hang/very slow execution
Дата
Msg-id CAHOFxGr5FWfykQf4QtUOUap-aRBh-Yw4XAOx=m_GTdA2HKMfqw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Adding nextval() to a select caused hang/very slow execution  (Eric Raskin <eraskin@paslists.com>)
Ответы Re: Adding nextval() to a select caused hang/very slow execution  (Eric Raskin <eraskin@paslists.com>)
Список pgsql-performance
Дерево обсуждения
On Wed, Nov 4, 2020 at 12:12 PM Eric Raskin <eraskin@paslists.com> wrote:
OK - I see.  And to add insult to injury, I tried creating a temporary table to store the intermediate results.  Then I was going to just do an insert... select... to insert the rows.   That would de-couple the nextval() from the query.

Strangely, the first query I tried it on worked great.  But, when I tried to add a second set of data with a similar query to the same temporary table, it slowed right down again.  And, of course, when I remove the insert, it's fine. 

I am not entirely sure I am understanding your process properly, but just a note- If you are getting acceptable results creating the temp table, and the issue is just that you get very bad plans when using it in some query that follows, then it is worth noting that autovacuum does nothing on temp tables and for me it is nearly always worth the small cost to perform an analyze (at least on key fields) after creating a temp table, or rather after inserting/updating/deleting records in a significant way.

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

Предыдущее
От: David Rowley
Дата:
Сообщение: Re: Partition pruning with joins
Следующее
От: Eric Raskin
Дата:
Сообщение: Re: Adding nextval() to a select caused hang/very slow execution