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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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 | Content-Type | Size |
---|---|---|
jcigar.vcf | text/x-vcard | 303 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Cigar | 2012-04-05 14:29:32 | Re: bad plan |
Previous Message | Mario Dankoor | 2012-04-05 12:32:00 | Re: bad plan |