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