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 ..
> On 04/05/2012 14:32, Mario Dankoor wrote:
>> 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:
>>> t_geo.frequence, ST_SetSRID(gc.geom, 4326) as geom
>>> FROM (
>>> geo_id , count(*) AS frequence
>>> NOT geo_id IS NULL ANDtaxon_id= ANY (
>>> ancestors.subphylum_id = 18830
>>> ) GROUP BY geo_id
>>> ) as t_geo
>>> 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.
Description: text/x-vcard (303 bytes)
In response to
pgsql-sql by date
|Next:||From: Andreas||Date: 2012-04-06 18:58:33|
|Subject: syntax of joins|
|Previous:||From: Julien Cigar||Date: 2012-04-05 12:43:39|
|Subject: Re: bad plan|