Re: [SQL] bad plan

[Julien Cigar]

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>>-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql