From: Julien Cigar <jcigar@ulb.ac.be>
To: pgsql-sql@postgresql.org
Subject: Re: bad plan
Date: Thu, 05 Apr 2012 16:29:32 +0200
Message-ID: <4F7DAC4C.3070605@ulb.ac.be> (raw)
In-Reply-To: <4F7D937B.6080908@ulb.ac.be>
References: <4F7D843E.4060304@ulb.ac.be>
<4F7D89F5.7090301@gmail.com>
<4F7D8B3F.1030402@ulb.ac.be>
<4F7D90C0.3090800@gmail.com>
<4F7D937B.6080908@ulb.ac.be>
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.
Attachments:
[text/x-vcard] jcigar.vcf (304B, ../4F7DAC4C.3070605@ulb.ac.be/2-jcigar.vcf)
download | inline:
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jcigar@ulb.ac.be
Subject: Re: bad plan
In-Reply-To: <4F7DAC4C.3070605@ulb.ac.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox