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

On Wed, Jul 27, 2016 at 11:15 AM, Félix GERZAGUET <felix(dot)gerzaguet(at)gmail(dot)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