Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 65A6F632961
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 28 Apr 2010 06:38:31 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 78262-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 28 Apr 2010 09:38:24 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from dc.baikal.ru (dc.baikal.ru [212.176.3.237])
	by mail.postgresql.org (Postfix) with ESMTP id D63FA632588
	for <pgsql-performance@postgresql.org>;
	Wed, 28 Apr 2010 06:38:23 -0300 (ADT)
Received: from it319k5.dc-local (it319k5.dc-local [10.0.4.75])
	(authenticated bits=0)
	by dc.baikal.ru (8.13.8/8.13.8) with ESMTP id o3S9cJXu017662
	for <pgsql-performance@postgresql.org>; Wed, 28 Apr 2010 18:38:19 +0900
Message-ID: <4BD8020C.20509@dc.baikal.ru>
Date: Wed, 28 Apr 2010 18:38:20 +0900
From: Vlad Arkhipov <arhipov@dc.baikal.ru>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Optimization idea
References: <4BD0161A.4010908@dc.baikal.ru> <4BD0430C.1050308@2ndquadrant.com>
	<4BD10805.9060004@dc.baikal.ru>
	<y2y603c8f071004230405r5fcef546l3da84c407ce2655a@mail.gmail.com>
	<4BD4FFE7.9090305@dc.baikal.ru>
	<x2te94e14cd1004260233i45c44966qc4a8b10f4edb2963@mail.gmail.com>
	<q2w603c8f071004271746s43f8669cz45bec5914b1fa9e0@mail.gmail.com>
	<z2te94e14cd1004280049i9f8d6b90u61dac137eede515d@mail.gmail.com>
In-Reply-To: <z2te94e14cd1004280049i9f8d6b90u61dac137eede515d@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.011 tagged_above=-10 required=5
	tests=BAYES_20=-0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201004/326
X-Sequence-Number: 38576


> 2010/4/28 Robert Haas <robertmhaas@gmail.com>:
>   
>> On Mon, Apr 26, 2010 at 5:33 AM, Cédric Villemain
>> <cedric.villemain.debian@gmail.com> wrote:
>>     
>>> In the first query, the planner doesn't use the information of the 2,3,4.
>>> It just does a : I'll bet I'll have 2 rows in t1 (I think it should
>>> say 3, but it doesn't)
>>> So it divide the estimated number of rows in the t2 table by 5
>>> (different values) and multiply by 2 (rows) : 40040.
>>>       
>> I think it's doing something more complicated.  See scalararraysel().
>>     
>
> Thank you for driving me to the right function, Robert.
> It is in fact more complicated :)
>
>   
>>> In the second query the planner use a different behavior : it did
>>> expand the value of t1.t to t2.t for each join relation and find a
>>> costless plan. (than the one using seqscan on t2)
>>>       
>> I think the problem here is one we've discussed before: if the query
>> planner knows that something is true of x (like, say, x =
>> ANY('{2,3,4}')) and it also knows that x = y, it doesn't infer that
>> the same thing holds of y (i.e. y = ANY('{2,3,4}') unless the thing
>> that is known to be true of x is that x is equal to some constant.
>> Tom doesn't think it would be worth the additional CPU time that it
>> would take to make these sorts of deductions.  I'm not sure I believe
>> that, but I haven't tried to write the code, either.
>>     
>
> If I understand correctly, I did have some issues with
> exclusion_constraint= ON for complex queries in datamining where the
> planner failled to understand it must use only one partition because
> the where clause where not enough 'explicit'. But it's long time ago
> and I don't have my use case.
>
> We probably need to find some real case where the planner optimisation
> make sense. But I don't want usual queries to see their CPU time
> increase...
> <joke>Do we need real Planner Hints ?</joke>
>
>   
Even if it will be done it does not solve the original issue. If I
understood you right there is now no any decent way of speeding up the query

select *
from t2
join t1 on t1.t = t2.t
where t1.id = X;

except of the propagating the t1.id value to the table t2 and createing
and index for this column? Then the query will look like

select *
from t2
where t1_id = X;