Skip site navigation (1) Skip section navigation (2)

Re: bad plan

From: Julien Cigar <jcigar(at)ulb(dot)ac(dot)be>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: bad plan
Date: 2012-04-05 12:43:39
Message-ID: 4F7D937B.6080908@ulb.ac.be (view raw or flat)
Thread:
Lists: pgsql-sql
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.


Attachment: jcigar.vcf
Description: text/x-vcard (303 bytes)

In response to

Responses

pgsql-sql by date

Next:From: Julien CigarDate: 2012-04-05 14:29:32
Subject: Re: bad plan
Previous:From: Mario DankoorDate: 2012-04-05 12:32:00
Subject: Re: bad plan

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group