Обсуждение: Query Tuning

Поиск
Список
Период
Сортировка

Query Tuning

От
Sonam Sharma
Дата:
We have a query which is running slow and it's taking 26secs to complete.. we have run the analyzer also and it's taking the same time.

Any tool is there for query optimization or any suggestions.

My query plan looks like this :

CTE Scan on approvalwflscreen (cost=8736.21..8737.25 rows=52 width=1214)

CTE constants

-> Result (cost=0.00..0.01 rows=1 width=44)

CTE approval

-> Sort (cost=7793.89..7805.22 rows=4530 width=292)

Sort Key: apv_1.t616_vbu_nbr, apv_1.t617_fnc_typ_cd, apv_1.t8071_cai_ivo_id, apv_1.t8071_add_dm

-> WindowAgg (cost=0.00..7518.80 rows=4530 width=292)

-> Nested Loop (cost=0.00..7450.85 rows=4530 width=72)

Join Filter: ((apv_1.t8118_apv_sts_cd IS NULL) OR (((apv_1.t8118_apv_sts_cd = con.dummy) OR (apv_1.t8118_apv_sts_cd = con.t8118_rejected) OR (apv_1.t8118_apv_sts_cd =

con.t8118_approved) OR (apv_1.t8118_apv_sts_cd = con.t8118_pending)) AND ((apv_1.t8130_apv_job_lvl_cd = con.t8130_deflt) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_processor) OR (apv_1.t81

30_apv_job_lvl_cd = con.t8130_assistant_mgr) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_manager) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_vp) OR (apv_1.t8130_apv_job_lvl_cd = con.t8130_re

ad_only)) AND (SubPlan 2)))

-> CTE Scan on constants con (cost=0.00..0.02 rows=1 width=42)

-> Seq Scan on t8119_cai_ivo_apv_wfl apv_1 (cost=0.00..268.18 rows=9818 width=72)

SubPlan 2

-> Nested Loop (cost=0.29..3913.17 rows=9507 width=0)

-> Seq Scan on t8071_cai_ivo_hdr hdr (cost=0.00..457.98 rows=9760 width=37)

Filter: (ivo_sts_cd = ANY (ARRAY[con.dummy, con.t8070_rejct, con.t8070_pndap, con.t8070_aprvd, con.t8070_pndps, con.t8070_cmplt, con.t8070_rdpmt, con.t8

070_stgap, con.t8070_cmeim, con.t8070_pndrv, con.t8070_delet, con.t8070_cncld]))

-> Index Only Scan using t8119i0 on t8119_cai_ivo_apv_wfl apv (cost=0.29..0.34 rows=1 width=37)

Index Cond: ((t616_vbu_nbr = hdr.t616_vbu_nbr) AND (t617_fnc_typ_cd = hdr.t617_fnc_typ_cd) AND (t8071_cai_ivo_id = hdr.t8071_cai_ivo_id) AND (t8071_add_

dm = hdr.t8071_add_dm))

CTE maxapproval

-> Sort (cost=149.09..150.22 rows=453 width=12)

Sort Key: apv_2.joinkey

-> HashAggregate (cost=124.58..129.11 rows=453 width=12)

Group Key: apv_2.joinkey, apv_2.t8119_apv_seq_nbr

-> CTE Scan on approval apv_2 (cost=0.00..90.60 rows=4530 width=10)

CTE header

-> Limit (cost=508.37..649.77 rows=1 width=618)

-> Nested Loop (cost=508.37..649.77 rows=1 width=618)

Join Filter: ((hdr_1.ivo_sts_cd = con_1.dummy) OR (hdr_1.ivo_sts_cd = con_1.t8070_rejct) OR (hdr_1.ivo_sts_cd = con_1.t8070_pndap) OR (hdr_1.ivo_sts_cd = con_1.t8070_aprvd)

OR (hdr_1.ivo_sts_cd = con_1.t8070_pndps) OR (hdr_1.ivo_sts_cd = con_1.t8070_cmplt) OR (hdr_1.ivo_sts_cd = con_1.t8070_rdpmt) OR (hdr_1.ivo_sts_cd = con_1.t8070_stgap) OR (hdr_1.ivo_sts_cd

= con_1.t8070_cmeim) OR (hdr_1.ivo_sts_cd = con_1.t8070_pndrv) OR (hdr_1.ivo_sts_cd = con_1.t8070_delet) OR (hdr_1.ivo_sts_cd = con_1.t8070_cncld))

-> Hash Join (cost=508.37..646.53 rows=1 width=126)

Hash Cond: ((apv_3.t616_vbu_nbr = hdr_1.t616_vbu_nbr) AND (apv_3.t617_fnc_typ_cd = hdr_1.t617_fnc_typ_cd) AND (apv_3.t8071_cai_ivo_id = hdr_1.t8071_cai_ivo_id) AND (a

pv_3.t8071_add_dm = hdr_1.t8071_add_dm))

-> CTE Scan on approval apv_3 (cost=0.00..90.60 rows=4530 width=114)

-> Hash (cost=306.79..306.79 rows=10079 width=118)

-> Seq Scan on t8071_cai_ivo_hdr hdr_1 (cost=0.00..306.79 rows=10079 width=118)

-> CTE Scan on constants con_1 (cost=0.00..0.02 rows=1 width=

 


Re: Query Tuning

От
Kyotaro Horiguchi
Дата:
Hello.

At Tue, 1 Oct 2019 12:42:24 +0530, Sonam Sharma <sonams1209@gmail.com> wrote in
<CAM-M3Tmz-FMGngTiUDuiREUqX6Ck5FBcwfigd8qWD8D0OauNQw@mail.gmail.com>
> We have a query which is running slow and it's taking 26secs to complete..
> we have run the analyzer also and it's taking the same time.
> 
> Any tool is there for query optimization or any suggestions.

EXPLAIN ANALYZE (not just EXPLAIN) would be that. In many cases
where a query takes an unexpectedly long time, rows estimation in
some nodes would be largely different from actual rows. That
leads to a wrong query plan. EXPLAIN ANALYZE may give you a clue
for such kind of problem.

The following output comes from EXPLAIN. I suppose that you
already have a similar output having a "(actutal time=...)"
clause after the "(cost=..)"  clause.

> My query plan looks like this :
> 
> CTE Scan on approvalwflscreen (cost=8736.21..8737.25 rows=52 width=1214)
> 
> CTE constants
> 
> -> Result (cost=0.00..0.01 rows=1 width=44)
> 
> CTE approval
> 
> -> Sort (cost=7793.89..7805.22 rows=4530 width=292)

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Re: Query Tuning

От
Michael Lewis
Дата:
Both of the below visualizers can help, but require some knowledge about comparing estimated vs actual row estimates, disk sorts vs in memory, etc. Drawing implications about whether your schema needs to change or just the query will take time to master as well.

http://tatiyants.com/pev/#/plans/new --data only stored locally on your computer, nice for security but not great for sharing with others and getting help, since you need to share full json output (and query ideally)

https://explain.depesz.com/ --URL is unique and sharable for help from others


Often you need to provide much more information to get good advice. What version of Postgres are you on? What are stats like for the involved tables? What other indexes are available or might you add? What is the use of this system-- single user or thousands? Heavy writes continually, or load & analysis? Can you share the full query text and output of EXPLAIN ANALYZE on it?