DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=dcN0BLvb17u/vsJ5MAovPStzcm07elVVqpHna2y7m53Ys/cVxuQsk5CbddXX1ftNJa
	U25t3ZHgmcroSi+smQA3gBoVmUTl3WBcygK0WWNOwIGjxjQTQDQu9CCD0WeCgTULCvth
	+/B+XDiTyGQuXRi/br2J8UjVWGZrVRkwr+Nro=
Message-ID: <4DEDDF11.2020801@gmail.com>
Date: Tue, 07 Jun 2011 11:19:29 +0300
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110424 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
To: Josh Berkus <josh@agliodbs.com>
CC: pgsql-performance@postgresql.org
Subject: Re: 8.4/9.0 simple query performance regression
References: <4DED4A87.7000901@agliodbs.com>
In-Reply-To: <4DED4A87.7000901@agliodbs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

07.06.11 00:45, Josh Berkus написав(ла):
> All,
>
> Just got this simple case off IRC today:
>
> 8.4.4
> This plan completes in 100ms:
>     Filter: (NOT (hashed SubPlan 1))

> 9.0.2
> This plan does not complete in 15 minutes or more:
>     Filter: (NOT (SubPlan 1))
"Hashed" is the key. Hashed subplans usually has much better performance.
You need to increase work_mem. I suppose it is in default state as you 
need not too much memory for hash of 70K integer values.
BTW: Why do it want to materialize a result of seq scan without filter. 
I can see no benefits (or is it more narrow rows?)

Best regards, Vitalii Tymchyshyn