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

[Tom Lane]

Abadie Lana  writes:
> 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...

That index looks pretty useless judging from the rowcounts, so I'm not
surprised that the planner didn't use it.  You might have better luck with
an index on the split_part expression

split_part(split_part((s.attvalue)::text, ' '::text, 1), '.'::text, 1)

since it's the join of that to e.name that seems to be actually selective.
(The planner doesn't appear to realize that it is, but ANALYZE'ing after
creating the index should fix that.)

regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

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

[Abadie Lana]

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