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

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=#

[iterlogo]<http://www.iter.org/>
Lana ABADIE
Database Engineer
CODAC Section

ITER Organization, Building 72/4108, SCOD, Control System Division
Route de Vinon-sur-Verdon - CS 90 046 - 13067 St Paul Lez Durance Cedex – France
Phone: +33 4 42 17 84 02
Get the latest ITER news on http://www.iter.org/whatsnew
From: Félix GERZAGUET [mailto:felix(dot)gerzaguet(at)gmail(dot)com]
Sent: 27 July 2016 11:37
To: Abadie Lana
Cc: Martín Marqués; Tom Lane; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] 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<mailto: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<http://e.name> subquery in another table. As in

create table func_var_name_for_tpl_15 as
select e.name<http://e.name>
from functionalvariables e
, usertemplatevariable ut
where e.usertemplatevar_id=ut.id<http://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<http://t.id>=s.tag_id
and t.status!='Internal'
and vat.fieldtype in ('DBF_INLINK','DBF_OUTLINK','DBF_FWDLINK')
and vat.id<http://vat.id>=s.atttype_id
and split_part(split_part(s.attvalue,' ',1),'.',1) in ( select e.name<http://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<http://utv.id>=utva.usertempvariable_fk
and utv.usertempl_id=15
;

Does it use the vat_funcvaratt_multi_idx index now ?

--
Félix