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=m1Ns96+KrOQ9Qch+EawtT23cp4CtzzcC8mi+QehuRwFb0lhS7tqMpA+eOIRvfkwa0u
	WhCSmz9H9OVJjwivKAZi2YBGOVF4G6uTgFfdXlZRSAHh4daqQg9DL0bAwG7eclSgFBPX
	28b+qDYLMBtYnzIZqSwJ1Ow2U6VsNLLExojKg=
Message-ID: <4BC8204A.7050904@gmail.com>
Date: Fri, 16 Apr 2010 10:31:06 +0200
From: Yeb Havinga <yebhavinga@gmail.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
To: =?UTF-8?B?0JLRltGC0LDQu9GW0Lkg0KLQuNC80YfQuNGI0LjQvQ==?=
 <tivv00@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Planner not using column limit specified for one column
	for another column equal to first
References: <m2y331e40661004160102tff445f9eg165a04efe3b409ed@mail.gmail.com>
In-Reply-To: <m2y331e40661004160102tff445f9eg165a04efe3b409ed@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Віталій Тимчишин wrote:
> Hello.
>
> I have a query that performs very poor because there is a limit on 
> join column that is not applied to other columns:
>
> select * from company this_ left outer join company_tag this_1_ on 
> this_.id=this_1_.company_id left outer join company_measures 
> companymea2_ on this_.id=companymea2_.company_id left outer join 
> company_descr ces3_ on this_.id=ces3_.company_id where this_1_.tag_id 
> = 7 and this_.id>50000000 
> and this_1_.company_id>50000000
> order by this_.id asc limit 1000;
>
> (plan1.txt)
> Total runtime: 7794.692 ms
>
> At the same time if I apply the limit (>50000000) to other columns in 
> query itself it works like a charm:
>
> select * from company this_ left outer join company_tag this_1_ on 
> this_.id=this_1_.company_id left outer join company_measures 
> companymea2_ on this_.id=companymea2_.company_id left outer join 
> company_descr ces3_ on this_.id=ces3_.company_id where this_1_.tag_id 
> = 7 and this_.id>50000000 
> and this_1_.company_id>50000000
> and companymea2_.company_id>50000000 and ces3_.company_id>50000000
> order by this_.id asc limit 1000;
>
> (plan2.txt)
> Total runtime: 27.547 ms
>
> I've thought and someone in this list've told me that this should be 
> done automatically.
Yes, if you have in a query a=b and b=c, then the optimizer figures out 
that a=c as well. (a,b and c are then member of the same equivalence class).

However both queries are not the same, since the joins you're using are 
outer joins. In the first it's possible that records are returned for 
company records with no matching ces3_ records, the ces3_ records is 
null in that case. In the second query no NULL ces3_ information may be 
returned.

Another thing is it seems that the number of rows guessed is far off 
from the actual number of rows, is the number 5000000 artificial or are 
you're statistics old or too small histogram/mcv's?

regards,
Yeb Havinga