Very slow query (3-4mn) on a table with 25millions rows - Mailing list pgsql-performance

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