Re: Slow performance with left outer join

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Slow performance with left outer join
Дата
Msg-id 8283.1200934555@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Slow performance with left outer join  ("Marten Verhoeven" <m.verhoeven@van-beek.nl>)
Ответы Re: Slow performance with left outer join  ("Pavel Stehule" <pavel.stehule@gmail.com>)
Список pgsql-performance
"Marten Verhoeven" <m.verhoeven@van-beek.nl> writes:
> 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))

If this is slow, it must be that the scan of fpuarticletext actually
returns many more rows than the single row the planner is expecting.
The reason the estimate is off is probably that the planner cannot make
any useful estimate about those COALESCE expressions.  Try rewriting
them in the simpler forms

    (at_type = 1 or at_type is null) AND
    (at_language = 0 or at_language is null)

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Pavel Stehule"
Дата:
Сообщение: Re: Slow performance with left outer join
Следующее
От: "Pavel Stehule"
Дата:
Сообщение: Re: Slow performance with left outer join