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

On Thu, Mar 5, 2015 at 4:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)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