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

[Félix GERZAGUET]

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

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

[Félix GERZAGUET]

Hello Lana,

On Wed, Jul 27, 2016 at 8:03 AM, Abadie Lana  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

Re: [PERFORM] Performance bottleneck due to array manipulation

[Félix GERZAGUET]

Hello,

On Fri, Aug 21, 2015 at 2:48 PM, Genc, Ömer oemer.g...@iais.fraunhofer.de
wrote:

 Now I want to delete all entries from ims_point, where the timestamp is
 older than one hour. The currently being referenced ids of the table
 ims_object_header should be excluded from this deletion.




delete from public.ims_point ip
  where ip.timestamp  current_timestamp - interval '1 hour'
and not exists ( select 'reference exists'
   from public.ims_object_header ioh
  where ioh.last_point_id = ip.point_id
 )
;

Does this works for you ?