Hi all
I'm having a problem with a slow query - I tried several things to optimize the
queries but didn't really help. The output of explain analyse shows sequential
scan on a table of 25 million rows. Even though it is indexed and (I put a
multi-column index on the fields used in the query), the explain utility shows
no usage of the scan...
Query takes around 200 sec...
Before considering a design change...I wanted to make sure that there is no way
to optimize the query....
explain analyze 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) except select s.attvalue
from functionalvarattributes s, tags t, usertemplvarattribute utva,
usertemplatevariable utv, variableattributetypes vat where vat.fieldtype in
('DBF_INLINK','DBF_OUTLINK','DBF_FWDLINK') and vat.id=s.atttype_id and
utv.id=utva.usertempvariable_fk and utv.usertempl_id=15 and t.id=s.tag_id and
t.status!='Internal'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);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------
HashSetOp Except (cost=171505.51..2086914.68 rows=1103 width=8) (actual
time=186584.977..186584.977 rows=0 loops=1)
-> Append (cost=171505.51..2031899.30 rows=22006150 width=8) (actual
time=36550.214..186584.539 rows=320 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=171505.51..905822.16
rows=155062 width=8) (actual time=36550.213..87210.878 rows=2 lo
ops=1)
-> Hash Join (cost=171505.51..904271.54 rows=155062 width=8)
(actual time=36550.212..87210.874 rows=2 loops=1)
Hash Cond: (split_part(split_part((s.attvalue)::text, '
'::text, 1), '.'::text, 1) = (e.name)::text)
-> Hash Join (cost=193.91..726328.81 rows=310124
width=8) (actual time=42.242..63701.027 rows=308287 loops=1)
Hash Cond: (s.tag_id = t.id)
-> Hash Join (cost=188.03..716954.60 rows=1671226
width=16) (actual time=42.154..63387.723 rows=651155 loo
ps=1)
Hash Cond: (s.atttype_id = vat.id)
-> Seq Scan on functionalvarattributes s
(cost=0.00..604691.04 rows=25430204 width=24) (actual time=
0.007..53954.210 rows=25429808 loops=1)
-> Hash (cost=183.18..183.18 rows=388
width=8) (actual time=42.113..42.113 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.
003..41.984 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.064..0.064 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.012..0.052 rows=36 loops=1)
Filter: ((status)::text <>
'Internal'::text)
Rows Removed by Filter: 158
-> Hash (cost=171250.07..171250.07 rows=4923 width=24)
(actual time=23162.533..23162.533 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> HashAggregate (cost=171200.84..171250.07
rows=4923 width=24) (actual time=23162.498..23162.518 rows=16
loops=1)
-> Hash Join (cost=8.95..171188.53 rows=4923
width=24) (actual time=17.642..23162.464 rows=48 loops=
1)
Hash Cond: (e.usertemplatevar_id = ut.id)
-> Seq Scan on functionalvariables e
(cost=0.00..155513.07 rows=4164607 width=32) (actual time
=0.008..21674.864 rows=4164350 loops=1)
-> Hash (cost=8.75..8.75 rows=16
width=8) (actual time=0.058..0.058 rows=16 loops=1)
Buckets: 1024 Batches: 1 Memory
Usage: 1kB
-> Index Scan using
usertemp_utv_idx on usertemplatevariable ut (cost=0.29..8.75 rows=16
width=8) (actual time=0.043..0.052 rows=16 loops=1)
Index Cond: (usertempl_id =
15)
-> Subquery Scan on "*SELECT* 2" (cost=172514.13..1126077.14
rows=21851088 width=8) (actual time=43579.873..99373.299 rows=3
18 loops=1)
-> Hash Join (cost=172514.13..907566.26 rows=21851088 width=8)
(actual time=43579.870..99372.820 rows=318 loops=1)
Hash Cond: (split_part(split_part((s_1.attvalue)::text, '
'::text, 1), '.'::text, 1) = (e_1.name)::text)
-> Hash Join (cost=193.91..726328.81 rows=310124
width=8) (actual time=2.724..71226.183 rows=308287 loops=1)
Hash Cond: (s_1.tag_id = t_1.id)
-> Hash Join (cost=188.03..716954.60 rows=1671226
width=16) (actual time=2.548..70764.941 rows=651155 loop
s=1)
Hash Cond: (s_1.atttype_id = vat_1.id)
-> Seq Scan on functionalvarattributes s_1
(cost=0.00..604691.04 rows=25430204 width=24) (actual tim
e=0.003..57363.539 rows=25429808 loops=1)
-> Hash (cost=183.18..183.18 rows=388
width=8) (actual time=2.450..2.450 rows=388 loops=1)
Buckets: 1024 Batches: 1 Memory Usage:
16kB
-> Seq Scan on variableattributetypes
vat_1 (cost=0.00..183.18 rows=388 width=8) (actual time=
0.014..2.153 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.131..0.131 rows=36 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 2kB
-> Seq Scan on tags t_1 (cost=0.00..5.43
rows=36 width=8) (actual time=0.015..0.100 rows=36 loops=1)
Filter: ((status)::text <>
'Internal'::text)
Rows Removed by Filter: 158
-> Hash (cost=172318.46..172318.46 rows=141 width=24)
(actual time=27594.115..27594.115 rows=2544 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 134kB
-> Nested Loop (cost=171201.54..172318.46 rows=141
width=24) (actual time=27586.058..27592.012 rows=2544 l
oops=1)
-> Nested Loop (cost=171201.12..172243.46
rows=16 width=32) (actual time=27585.957..27586.510 rows=2
56 loops=1)
-> HashAggregate
(cost=171200.84..171250.07 rows=4923 width=24) (actual time=27572.535..27572.
595 rows=16 loops=1)
-> Hash Join
(cost=8.95..171188.53 rows=4923 width=24) (actual time=27.159..27572.439 ro
ws=48 loops=1)
Hash Cond:
(e_1.usertemplatevar_id = ut_1.id)
-> Seq Scan on
functionalvariables e_1 (cost=0.00..155513.07 rows=4164607 width=32
) (actual time=0.163..23959.820 rows=4164350 loops=1)
-> Hash (cost=8.75..8.75
rows=16 width=8) (actual time=0.070..0.070 rows=16 loops=
1)
Buckets: 1024
Batches: 1 Memory Usage: 1kB
-> Index Scan using
usertemp_utv_idx on usertemplatevariable ut_1 (cost=0.29
..8.75 rows=16 width=8) (actual time=0.040..0.057 rows=16 loops=1)
Index Cond:
(usertempl_id = 15)
-> Materialize (cost=0.29..8.83
rows=16 width=8) (actual time=0.839..0.851 rows=16 loops=16)
-> Index Scan using
usertemp_utv_idx on usertemplatevariable utv (cost=0.29..8.75 rows=1
6 width=8) (actual time=0.039..0.080 rows=16 loops=1)
Index Cond: (usertempl_id =
15)
-> Index Only Scan using
usertemplvarattribute_atttypeid_key on usertemplvarattribute utva (cost=0.4
2..4.60 rows=9 width=8) (actual time=0.004..0.011 rows=10 loops=256)
Index Cond: (usertempvariable_fk =
utv.id)
Heap Fetches: 0
Total runtime: 186585.376 ms
(67 rows)
\d functionalvarattributes;
Table "public.functionalvarattributes"
Column | Type |
Modifiers
---------------------+-----------------------------+----------------------------------------------------------------------
id | bigint | not null default
nextval('functionalvarattributes_id_seq'::regclass)
attvalue | character varying(4000) | not null
createdat | timestamp without time zone |
description | character varying(500) |
updatedat | timestamp without time zone |
autosaved | boolean | not null
atttype_id | bigint |
codactemplvaratt_fk | bigint |
funcvar_fk | bigint | not null
tag_id | bigint |
usertemplvaratt_fk | bigint |
useratttype_id | bigint |
keyattvalue | character varying(255) |
Indexes:
"functionalvarattributes_pkey" PRIMARY KEY, btree (id)
"functionalvarattributes_funcvar_fk_tag_id_atttype_id_key" UNIQUE
CONSTRAINT, btree (funcvar_fk, tag_id, atttype_id)
"usertemplvaratt_funcvaratt_idx" btree (usertemplvaratt_fk)
"vat_funcvaratt_multi_idx" btree (atttype_id, attvalue, tag_id)
Foreign-key constraints:
"fk6b514a7b1929df33" FOREIGN KEY (useratttype_id) REFERENCES
userattributetypes(id)
"fk6b514a7b19d38f01" FOREIGN KEY (codactemplvaratt_fk) REFERENCES
codactemplvarattribute(id)
"fk6b514a7b2080a717" FOREIGN KEY (atttype_id) REFERENCES
variableattributetypes(id)
"fk6b514a7ba4d2f942" FOREIGN KEY (funcvar_fk) REFERENCES
functionalvariables(id)
"fk6b514a7bc81d711d" FOREIGN KEY (usertemplvaratt_fk) REFERENCES
usertemplvarattribute(id)
"fk6b514a7bcbbfa8b8" FOREIGN KEY (tag_id) REFERENCES tags(id)
Version of postgresql is 9.3 on linux RHEL
uname -a
Linux 4504DS-SRV-0043.codac.iter.org 2.6.32-431.20.3.el6.x86_64 #1 SMP Fri Jun
6 18:30:54 EDT 2014 x86_64 x86_64 x86_64 GNU/Linux
Thanks for your help
Lana
