Re: Very slow query (3-4mn) on a table with 25millions rows

Поиск
Список
Период
Сортировка
От Félix GERZAGUET
Тема Re: Very slow query (3-4mn) on a table with 25millions rows
Дата
Msg-id CANVwZtsM4u2ZJH4ChzpR5byi74zxG7RL+XD5w292XHZaS6UDng@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Very slow query (3-4mn) on a table with 25millions rows  (Félix GERZAGUET <felix.gerzaguet@gmail.com>)
Ответы Re: Very slow query (3-4mn) on a table with 25millions rows  (Abadie Lana <Lana.Abadie@iter.org>)
Список pgsql-performance
Дерево обсуждения

On Wed, Jul 27, 2016 at 11:15 AM, Félix GERZAGUET <felix.gerzaguet@gmail.com> wrote:
 I don't know how to give the planner more accurate info ...
 
Could you try to materialize the e.name subquery in another table. As in

create table func_var_name_for_tpl_15 as
select e.name
                                                             from functionalvariables e
                                                                , usertemplatevariable ut
                                                            where e.usertemplatevar_id=ut.id
                                                              and ut.usertempl_id=15
;

Then analyse that table
Then try the rewritten query:

with filtered_s as (
select s.attvalue
  from functionalvarattributes s
     , tags t
     , variableattributetypes vat
 where t.id=s.tag_id
   and t.status!='Internal'
   and vat.fieldtype in ('DBF_INLINK','DBF_OUTLINK','DBF_FWDLINK')
   and vat.id=s.atttype_id
   and split_part(split_part(s.attvalue,' ',1),'.',1) in ( select e.name
                                                             from func_var_name_for_tpl_15 e
                                                                )
)
select s.attvalue
  from filtered_s s
except
select s.attvalue
  from filtered_s s
     , usertemplvarattribute utva
     , usertemplatevariable utv
  where utv.id=utva.usertempvariable_fk
    and utv.usertempl_id=15
;

Does it use the vat_funcvaratt_multi_idx index now ?

--
Félix

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

Предыдущее
От: Félix GERZAGUET
Дата:
Сообщение: Re: Very slow query (3-4mn) on a table with 25millions rows
Следующее
От: Abadie Lana
Дата:
Сообщение: Re: Very slow query (3-4mn) on a table with 25millions rows