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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 
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.gerzaguet@xxxxxxxxx]
Sent: 27 July 2016 11:37
To: Abadie Lana
Cc: Martín Marqués; Tom Lane; pgsql-performance@xxxxxxxxxxxxxx
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.gerzaguet@xxxxxxxxx> wrote:

 I don't know how to give the planner more accurate info ...

 

Could you try to materialize the e.name subquery in another table. As in

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
;

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=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
except
select s.attvalue
  from filtered_s s
     , usertemplvarattribute utva
     , usertemplatevariable utv
  where utv.id=utva.usertempvariable_fk
    and utv.usertempl_id=15
;

 

Does it use the vat_funcvaratt_multi_idx index now ?


--

Félix

 


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux