Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT
Дата
Msg-id CAMkU=1z6c5qw9CGkEsAtouMRtDeFNxn==aMYRTbZwo=+y2bPtA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Дерево обсуждения
On Thu, Mar 5, 2015 at 4:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
> On 5.3.2015 16:01, Gunnlaugur Thor Briem wrote:
>> - postgres version is 9.1.13

> The only thing I can think of is some sort of memory exhaustion,
> resulting in swapping out large amounts of memory.

I'm wondering about the issue addressed by commit fccebe421 ("Use
SnapshotDirty rather than an active snapshot to probe index endpoints").
Now, that was allegedly fixed in 9.1.13 ... but if the OP were confused
and this server were running, say, 9.1.12, that could be a viable
explanation.  Another possibly viable explanation for seeing the issue
in 9.1.13 would be if I fat-fingered the back-patch somehow :-(.


The back patch into 9.1.13 seems to have worked.

psql -c 'create table foo (x integer ); create index on foo(x);insert into foo select * from generate_series(1,10000); analyze foo;'

perl -le 'use DBI; my $dbh=DBI->connect("DBi:Pg:"); $dbh->begin_work(); foreach (1..1e6) {$dbh->do("insert into foo values ($_)") or die; }; $dbh->rollback()' & 

while (true); 
  do pgbench -T5 -c4 -j4 -n -f <(echo "explain select count(*) from foo a join foo b using (x);"); 
done

on 9.1.12 this slows down dramatically and on 9.1.13 it does not.

Cheers,

Jeff

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

Предыдущее
От: Tomas Vondra
Дата:
Сообщение: Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT
Следующее
От: Tom Lane
Дата:
Сообщение: Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT