Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <zawadaa@wp.pl>) id 1TLBhO-0000dt-Tt
	for pgsql-performance@postgresql.org; Mon, 08 Oct 2012 11:40:39 +0000
Received: from mx4.wp.pl ([212.77.101.8])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <zawadaa@wp.pl>) id 1TLBhN-0000rr-De
	for pgsql-performance@postgresql.org; Mon, 08 Oct 2012 11:40:38 +0000
Received: (wp-smtpd smtp.wp.pl 18057 invoked from network);
	8 Oct 2012 13:40:32 +0200
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=wp.pl; s=1024a;
	t=1349696432; bh=aOMR0yqBFMibXfII41/S1wwiI2YU7NpZeWmOUYpnyNA=;
	h=From:To:CC:Subject;
	b=N1i4Z2EfeaBHiM0iW99IlBGoMWpOIFHKIzfhrWoS1JB1/dEVc2WVMD9V95U2P8sLZ
	Z14RFEt9UBklYi3oFe8BFcP2HucUYQWwKG/xlL7VT0tbpPwVdu6Hr5aMtZ7vtAQD+W
	XprYYQfsT9TcztcAlLXWGQg89dZ9KXsjxza0ZUvg=
Received: from bramka-zew.dfqs.pl (HELO [192.168.20.69])
	(zawadaa@[194.126.222.22]) (envelope-sender <zawadaa@wp.pl>)
	by smtp.wp.pl (WP-SMTPD) with CAMELLIA256-SHA encrypted SMTP
	for <ringerc@ringerc.id.au>; 8 Oct 2012 13:40:32 +0200
Message-ID: <5072BBAF.5050200@wp.pl>
Date: Mon, 08 Oct 2012 13:40:31 +0200
From: Andrzej Zawadzki <zawadaa@wp.pl>
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:15.0) Gecko/20120829 Icedove/15.0
MIME-Version: 1.0
To: Craig Ringer <ringerc@ringerc.id.au>
CC: pgsql-performance@postgresql.org
Subject: Re: Strange behavior after upgrade from 9.0 to 9.2
References: <50728C3E.9050904@wp.pl> <5072A7B3.8090001@ringerc.id.au>
In-Reply-To: <5072A7B3.8090001@ringerc.id.au>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-WP-AV: skaner antywirusowy poczty Wirtualnej Polski S. A.
X-WP-SPAM: NO 0000000 [kcJl]                               
X-Pg-Spam-Score: -1.2 (-)
X-Archive-Number: 201210/41
X-Sequence-Number: 48000

On 08.10.2012 12:15, Craig Ringer wrote:
> On 10/08/2012 04:18 PM, Andrzej Zawadzki wrote:
>> Hi!
>>
>> After upgrade (dump/restore/analyze) query (below) after some time is
>> killed by kernel.
>
> What's `shared_buffers`? `work_mem`?
shared_buffers = 64MB
work_mem = 48MB
effective_cache_size = 512MB

Nothing changed. Config is pretty much similar.

I noticed the correctness: when table

verification_waiting_credit

does not contain any record of user_id = 'value' then query execute very
fast.

>
> https://wiki.postgresql.org/wiki/Server_Configuration
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
I understand but server is the same - KVM image.
Postgresql engine is different.

Table kredyty is big - contains ~3M records.
verification_waiting_credit is small - ~40 records.

Do you need schemas? I don't know if I can post here... :-(

-- 
Andrzej Zawadzki