From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Kemal Ortanca <kemal.ortanca@outlook.com>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Query optimization advice for beginners
Date: Tue, 28 Jan 2020 09:37:42 +0100
Message-ID: <9e8e2cc3aa05f979cef2cfe95e443ba5250f33fb.camel@cybertec.at> (raw)
In-Reply-To: <AM0PR03MB4211FF22D3D161031EAC359AF80B0@AM0PR03MB4211.eurprd03.prod.outlook.com>
References: <AM0PR03MB4211FF22D3D161031EAC359AF80B0@AM0PR03MB4211.eurprd03.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, kemal.ortanca@outlook.com, pgsql-performance@lists.postgresql.org
Subject: Re: Query optimization advice for beginners
In-Reply-To: <9e8e2cc3aa05f979cef2cfe95e443ba5250f33fb.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox