Hello please, send output EXPLAIN ANALYZE statement Regards Pavel Stehule On 21/01/2008, Marten Verhoeven <m.verhoeven@xxxxxxxxxxx> wrote: > > > Hi, > > Since I moved from PostgreSQL 7.3 to 8.2 I have a query which suddenly runs > very slow. In 7.3 it was really fast. It seems that the query analyser makes > other choices, which I don't understand. > > I have the query: > > SELECT * FROM fpuArticle > LEFT OUTER JOIN fpuArticleText ON a_No=at_a_No AND coalesce(at_Type,1)=1 > AND coalesce(at_Language,0)=0 > WHERE strpos(lower(coalesce(a_Code,'') || ' ' || > coalesce(at_Text,'')), 'string')>0 > > when I use a normal join, this query is very fast, but with this left outer > join it is slow. > > This is the query analysis: > > Nested Loop Left Join (cost=1796.69..3327.98 rows=5587 width=516) > Join Filter: (fpuarticle.a_no = fpuarticletext.at_a_no) > Filter: (strpos(lower((((COALESCE(fpuarticle.a_code, ''::character > varying))::text || ' '::text) || (COALESCE(fpuarticletext.at_text, > ''::character varying))::text)), 'string'::text) > 0) > -> Seq Scan on fpuarticle (cost=0.00..944.62 rows=16762 width=386) > -> Materialize (cost=1796.69..1796.70 rows=1 width=130) > -> Seq Scan on fpuarticletext (cost=0.00..1796.69 rows=1 > width=130) > Filter: ((COALESCE((at_type)::integer, 1) = 1) AND > (COALESCE(at_language, 0::numeric) = 0::numeric)) > > It seems that the filter on at_type and at_Language is used at the and, > while it is much faster to use it at the beginning. Why is this, and how can > I influence this? > > With kind regards > > Marten Verhoeven > Van Beek B.V. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
