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.
