On Wed, Jul 27, 2016 at 11:15 AM, Félix GERZAGUET <felix.gerzag...@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
