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=o1I3gPXdrVz7CgJ1TZ8h8S33OyOEDfDB516PMm4vgyzEo7REBCGRyoTXKuHuDYcF3k
	I8d7BANkVsc/Y8vFczdoXEe0MZxs8hpeTgzcZHX74dyuqaTMwbZoIi3fleXJKdysbRwV
	lj0+ifYkZcY+YehbaYn9yJwXE6fsB0VLCtUQc=
Message-ID: <4D833953.7080607@gmail.com>
Date: Fri, 18 Mar 2011 12:52:03 +0200
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.14) Gecko/20110223 Lightning/1.0b2 Thunderbird/3.1.8
MIME-Version: 1.0
To: =?UTF-8?B?QW5zc2kgS8Okw6RyacOkaW5lbg==?= <anssi.kaariainen@thl.fi>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Disabling nested loops - worst case performance
References: <4D8306A7.7020205@thl.fi>
In-Reply-To: <4D8306A7.7020205@thl.fi>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

18.03.11 09:15, Anssi Kääriäinen написав(ла):
> Hello list,
>
> I am working on a Entity-Attribute-Value (EAV) database using 
> PostgreSQL 8.4.7. The basic problem is that when joining multiple 
> times different entities the planner thinks that there is vastly less 
> rows to join than there is in reality and decides to use multiple 
> nested loops for the join chain. This results in queries where when 
> nested loops are enabled, query time is somewhere around 35 seconds, 
> but with nested loops disabled, the performance is somewhere around 
> 100ms. I don't think there is much hope for getting better statistics, 
> as EAV is just not statistics friendly. The values of an attribute 
> depend on the type of the attribute, and different entities have 
> different attributes defined. The planner has no idea of these 
> correlations.

Hello.

If your queries work on single attribute, you can try adding partial 
indexes for different attributes. Note that in this case parameterized 
statements may prevent index usage, so check also with attribute id inlined.

Best regards, Vitalii Tymchyshyn