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

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