Re: Query optimization advice for beginners

On Mon, 2020-01-27 at 13:15 +0000, Kemal Ortanca wrote:
> There is a query that runs slowly when I look at the logs of the database. When I check the
> resources of the system, there is no problem in the resources, but this query running slowly.
> There is no "Seq Scan" in the queries, so the tables are already indexed. But I did not
> fully understand if the indexes were made correctly. When I analyze the query result on
> explain.depesz, it seems that the query is taking too long.
>
> How should I fix the query below? How should I read the output of explain.depesz?
>
> https://explain.depesz.com/s/G4vq

Normally you focus on where the time is spent and the mis-estimates.

The mis-estimates are notable, but this time not the reason for a
wrong choice of join strategy: evern though there are overestimates,
a nested loop join is chosen.

The time is spent in the 16979 executions of the outer subquery,
particularly in the inner subquery.

Because the query uses correlated subqueries, PostgreSQL has to execute
these conditions in the fashion of a nested loop, that is, the subquery
is executed for every row found.

If you manage to rewrite the query so that it uses (outer) joins instead
of correlated subqueries, the optimizer can use different strategies
that may be more efficient.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com