Message-ID: <4F7DAC4C.3070605@ulb.ac.be>
Date: Thu, 05 Apr 2012 16:29:32 +0200
From: Julien Cigar <jcigar@ulb.ac.be>
Organization: Belgian Biodiversity Platform
User-Agent: Mozilla/5.0 (X11; FreeBSD i386;
	rv:10.0.2) Gecko/20120314 Thunderbird/10.0.2
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: bad plan
References: <4F7D843E.4060304@ulb.ac.be> <4F7D89F5.7090301@gmail.com>
	<4F7D8B3F.1030402@ulb.ac.be> <4F7D90C0.3090800@gmail.com>
	<4F7D937B.6080908@ulb.ac.be>
In-Reply-To: <4F7D937B.6080908@ulb.ac.be>
Content-Type: multipart/mixed; boundary="------------020909020608000302000600"

This is a multi-part message in MIME format.
--------------020909020608000302000600
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

FYI, raising cpu_tuple_cost from 0.01 to 0.5 fixed the problem..!

On 04/05/2012 14:43, Julien Cigar wrote:
> Hi Mario,
>
> Setting it to > -1, or even removing the condition doesn't change 
> anything .. the problem is more that the estimate of the join point 
> sucks pretty much ..
>
> Julien
>
> On 04/05/2012 14:32, Mario Dankoor wrote:
>> Julien,
>>
>> I generally try avoid NOT predicates.
>> If your geo_id is an integer,  try geo_id > -1
>> or if it's a varchar geo_id > ''.
>> The idea is that geo_id > 0 is false for null values.
>>
>> I don't think query optimizers are fond of NOT predicates.
>>
>> SELECT  t_geo.frequence
>>        ,ST_SetSRID(gc.geom, 4326) as geom
>> FROM (
>>        SELECT  geo_id
>>               ,count(*) AS frequence
>>        FROM hit.context_to_context_links
>>        WHERE geo_id > -1
>>        AND taxon_id= ANY (
>>                           SELECT taxon_id
>>                           FROM rab.ancestors
>>                           WHERE ancestors.subphylum_id = 18830
>>                         ) GROUP BY geo_id
>>      ) as t_geo
>> JOIN  hit.geo_candidates gc
>> ON gc.id = t_geo.geo_id;
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On 2012-04-05 2:08 PM, Julien Cigar wrote:
>>> SELECT
>>>   t_geo.frequence, ST_SetSRID(gc.geom, 4326) as geom
>>> FROM (
>>>   SELECT
>>>     geo_id , count(*) AS frequence
>>>   FROM
>>> hit.context_to_context_links
>>>   WHERE
>>>     NOT geo_id IS NULL ANDtaxon_id= ANY (
>>>       SELECT
>>>         taxon_id
>>>       FROM
>>>         rab.ancestors
>>>       WHERE
>>>         ancestors.subphylum_id = 18830
>>>       ) GROUP BY geo_id
>>> ) as t_geo
>>> JOIN
>>>   hit.geo_candidates gc ON gc.id = t_geo.geo_id; 
>>
>>
>
>
>
>


-- 
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.


--------------020909020608000302000600
Content-Type: text/x-vcard; charset=utf-8;
 name="jcigar.vcf"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="jcigar.vcf"

YmVnaW46dmNhcmQNCmZuOkp1bGllbiBDaWdhcg0KbjpDaWdhcjtKdWxpZW4NCm9yZztxdW90
ZWQtcHJpbnRhYmxlOkJlbGdpYW4gQmlvZGl2ZXJzaXR5IFBsYXRmb3JtO1VMQiAoVW5pdmVy
c2l0PUMzPUE5IExpYnJlIGRlIEJydXhlbGxlcykNCmFkcjo7OztCcnVzc2Vsczs7O0JlbGdp
dW0NCmVtYWlsO2ludGVybmV0OmpjaWdhckB1bGIuYWMuYmUNCnRlbDt3b3JrOiszMigwKTI2
NTA1NzUyDQp4LW1vemlsbGEtaHRtbDpGQUxTRQ0KdXJsOmh0dHA6Ly93d3cuYmlvZGl2ZXJz
aXR5LmJlDQp2ZXJzaW9uOjIuMQ0KZW5kOnZjYXJkDQoNCg==
--------------020909020608000302000600--