|
Sorry for the delay Still no use of the index 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; SELECT 48 =# analyze func_var_name_for_tpl_15; ANALYZE =# explain analyze 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, usertemplvarattribute utva,
usertemplatevariable utv where utv.id=utva.usertempvariable_fk and utv.usertempl_id=15; QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- Nested Loop (cost=689051.63..698514.55 rows=741512 width=516) (actual time=11043.744..47958.871 rows=318 loops=1) CTE filtered_s -> Hash Join (cost=195.99..689050.93 rows=5262 width=8) (actual time=11043.680..47957.962 rows=2 loops=1) Hash Cond: (s_1.tag_id = t.id) -> Hash Join (cost=190.11..688886.10 rows=28355 width=16) (actual time=11043.499..47957.774 rows=6 loops=1) Hash Cond: (s_1.atttype_id = vat.id) -> Hash Semi Join (cost=2.08..686796.55 rows=431458 width=24) (actual time=11040.920..47955.181 rows=6 loops=1) Hash Cond: (split_part(split_part((s_1.attvalue)::text, ' '::text, 1), '.'::text, 1) = (e.name)::text) -> Seq Scan on functionalvarattributes s_1 (cost=0.00..604679.32 rows=25429032 width=24) (actual time=0.006..2 2378.636 rows=25429808 loops=1) -> Hash (cost=1.48..1.48 rows=48 width=21) (actual time=0.063..0.063 rows=48 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 3kB -> Seq Scan on func_var_name_for_tpl_15 e (cost=0.00..1.48 rows=48 width=21) (actual time=0.006..0.032 r ows=48 loops=1) -> Hash (cost=183.18..183.18 rows=388 width=8) (actual time=2.480..2.480 rows=388 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 16kB -> Seq Scan on variableattributetypes vat (cost=0.00..183.18 rows=388 width=8) (actual time=0.021..2.220 rows= 388 loops=1) Filter: ((fieldtype)::text = ANY ('{DBF_INLINK,DBF_OUTLINK,DBF_FWDLINK}'::text[])) Rows Removed by Filter: 5516 -> Hash (cost=5.43..5.43 rows=36 width=8) (actual time=0.166..0.166 rows=36 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB -> Seq Scan on tags t (cost=0.00..5.43 rows=36 width=8) (actual time=0.015..0.137 rows=36 loops=1) Filter: ((status)::text <> 'Internal'::text) Rows Removed by Filter: 158 -> CTE Scan on filtered_s s (cost=0.00..105.24 rows=5262 width=516) (actual time=11043.686..47957.977 rows=2 loops=1) -> Materialize (cost=0.70..84.46 rows=141 width=0) (actual time=0.027..0.307 rows=159 loops=2) -> Nested Loop (cost=0.70..83.75 rows=141 width=0) (actual time=0.049..0.394 rows=159 loops=1) -> Index Scan using usertemp_utv_idx on usertemplatevariable utv (cost=0.29..8.75 rows=16 width=8) (actual time=0.025. .0.040 rows=16 loops=1) Index Cond: (usertempl_id = 15) -> Index Only Scan using usertemplvarattribute_atttypeid_key on usertemplvarattribute utva (cost=0.42..4.60 rows=9 wid th=8) (actual time=0.005..0.013 rows=10 loops=16) Index Cond: (usertempvariable_fk = utv.id) Heap Fetches: 0 Total runtime: 47959.180 ms (31 rows) sddcryo=#
From: Félix GERZAGUET [mailto:felix.gerzaguet@xxxxxxxxx]
On Wed, Jul 27, 2016 at 11:15 AM, Félix GERZAGUET <felix.gerzaguet@xxxxxxxxx> 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 Then analyse that table Does it use the vat_funcvaratt_multi_idx index now ?
Félix |
