Re: Query Tuning

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