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

Поиск

Список

Период

Сортировка

От	Abadie Lana
Тема	Re: Very slow query (3-4mn) on a table with 25millions rows
Дата	28 июля 2016 г. 12:55:33
Msg-id	E544BB9A64ABD24DA201745FD316D94551128801@XCH2.iter.org обсуждение исходный текст
Ответ на	Re: Very slow query (3-4mn) on a table with 25millions rows (Félix GERZAGUET <felix.gerzaguet@gmail.com>)
Список	pgsql-performance

Дерево обсуждения

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

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