Hello Lana, On Wed, Jul 27, 2016 at 8:03 AM, Abadie Lana <lana.aba...@iter.org> wrote:
> Here the result of explain (analyse, buffer). Thanks for your help and let > me know if you need more information. I noticed 3 things in your query: 1. In the second part (after the except), the 2 tables utva and utv are not joined against the others table. Is there a missing join somewhere ? Let that snipset: 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 functionalvariables e , usertemplatevariable ut where e.usertemplatevar_id=ut.id and ut.usertempl_id=15 ) be called A Let that snipset: select * from usertemplvarattribute utva , usertemplatevariable utv where utv.id=utva.usertempvariable_fk and utv.usertempl_id=15 be called B Then you query is: A except A CROSS JOIN B If B is not the empty set, than the above query is guaranteed to always have 0 row. 2. Assuming your query is right (even if I failed to understand its point), we could only do the A snipset once instead of twice using a with clause as in: 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 functionalvariables e , usertemplatevariable ut where e.usertemplatevar_id=ut.id and ut.usertempl_id=15 ) ) 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 ; This rewritten query should run about 2x. faster. 3. The planner believe that the e.name subselect will give 4926 rows (instead of 16 in reality), due to this wrong estimate it will consider the vat_funcvaratt_multi_idx index as not usefull. I don't know how to give the planner more accurate info ... -- Félix