Re: Query Tuning

Hello.

At Tue, 1 Oct 2019 12:42:24 +0530, Sonam Sharma <sonams1209(at)gmail(dot)com> wrote in <CAM-M3Tmz-FMGngTiUDuiREUqX6Ck5FBcwfigd8qWD8D0OauNQw(at)mail(dot)gmail(dot)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