pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery optimization advice for beginners
5+ messages / 4 participants
[nested] [flat]
* Query optimization advice for beginners
@ 2020-01-27 13:15 Kemal Ortanca <kemal.ortanca@outlook.com>
2020-01-27 13:57 ` Re: Query optimization advice for beginners Andreas Kretschmer <andreas@a-kretschmer.de>
2020-01-27 16:46 ` Re: Query optimization advice for beginners Michael Lewis <mlewis@entrata.com>
2020-01-28 08:37 ` Re: Query optimization advice for beginners Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 5+ messages in thread
From: Kemal Ortanca @ 2020-01-27 13:15 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hello mail group members,
I started a new job as PostgreSQL DBA. This is my first mail, I hope the mail I sent meets the rules.
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?
Thank you in advance for your help.
select pro.id as pro_id
, pro.code
, coalesce(s.is_pick, false)
, coalesce(sum(sb.quantity), 0) as pick_quantity
from mainproduct_productmetaproduction pro, order_basketitemdetail bid
left join shelf_shelvedproductbatch sb on sb.basketitem_detail_id = bid.id
left join shelf_shelvedproducts sp on sp.id = sb.shelved_product_id
left join shelf_shelf s on s.id = sp.shelf_id
where pro.id = bid.production_id
and (
select coalesce(sum(bid.quantity), 0)
from order_basketitem bi
, order_basketitemdetail bid
, order_order o
where o.type in (2,7,9) and o.id = bi.order_id
and o.is_cancelled = false
and bi.is_cancelled = false
and o.is_closed = false
and o.is_picked = false
and o.is_invoiced = false
and o.is_sent = false
and bi.id = bid.basketitem_id
and bid.quantity > (
select coalesce(sum(picked_quantity),0)
from order_basketitembatch bib
where bib.detail_id=bid.id
)
and bid.code = pro.code
) > 0
group by 1,2,3 --,bid.pallet_item_quantity
having coalesce(s.is_pick, false)
and round((coalesce(sum(sb.quantity), 0) / GREATEST(MAX(bid.pallet_item_quantity), 1)::float)::numeric, 2) <= 0.15
https://explain.depesz.com/s/G4vq
Yours truly,
Kemal Ortanca
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query optimization advice for beginners
2020-01-27 13:15 Query optimization advice for beginners Kemal Ortanca <kemal.ortanca@outlook.com>
@ 2020-01-27 13:57 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2020-01-27 15:33 ` Re: Query optimization advice for beginners Kemal Ortanca <kemal.ortanca@outlook.com>
2 siblings, 1 reply; 5+ messages in thread
From: Andreas Kretschmer @ 2020-01-27 13:57 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Am 27.01.20 um 14:15 schrieb Kemal Ortanca:
>
> https://explain.depesz.com/s/G4vq
>
>
the estimates and the real values are very different, seems like
problems with autoanalyze.
which version?
Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query optimization advice for beginners
2020-01-27 13:15 Query optimization advice for beginners Kemal Ortanca <kemal.ortanca@outlook.com>
2020-01-27 13:57 ` Re: Query optimization advice for beginners Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2020-01-27 15:33 ` Kemal Ortanca <kemal.ortanca@outlook.com>
0 siblings, 0 replies; 5+ messages in thread
From: Kemal Ortanca @ 2020-01-27 15:33 UTC (permalink / raw)
To: Andreas Kretschmer <andreas@a-kretschmer.de>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Firstly, thank you for coming back.
PostgreSQL version = 11.5
Is there a resource or postgresql configuration you want me to check in addition?
________________________________
From: Andreas Kretschmer <andreas@a-kretschmer.de>
Sent: Monday, January 27, 2020 3:57 PM
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Query optimization advice for beginners
Am 27.01.20 um 14:15 schrieb Kemal Ortanca:
>
> https://explain.depesz.com/s/G4vq
>
>
the estimates and the real values are very different, seems like
problems with autoanalyze.
which version?
Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com<http://www.2ndQuadrant.com;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query optimization advice for beginners
2020-01-27 13:15 Query optimization advice for beginners Kemal Ortanca <kemal.ortanca@outlook.com>
@ 2020-01-27 16:46 ` Michael Lewis <mlewis@entrata.com>
2 siblings, 0 replies; 5+ messages in thread
From: Michael Lewis @ 2020-01-27 16:46 UTC (permalink / raw)
To: Kemal Ortanca <kemal.ortanca@outlook.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
You've got two references to order_basketitemdetail both aliased to bid and
ALSO a table called order_basketitembatch aliased to bib. I assume that
confuses the planner, but even if it doesn't it certainly confuses any new
developers trying to understand the query's intention.
The biggest thing that leaps out at me on the explain plan is the 822
thousand loops on index order_basketitembatch_detail_id_9268ccff. That
seems to be the subquery in the where clause of the subquery in the main
where clause. I never get great results when I nest sub-queries multiple
levels. Without knowing your data, we can only make guesses about
restructuring the query so it performs better.
select bi.id AS basketitem_id --coalesce(sum(bid.quantity), 0)
from order_basketitem bi
--, order_basketitemdetail bid
, order_order o
where o.type in (2,7,9) and o.id = bi.order_id
and o.is_cancelled = false
and bi.is_cancelled = false
and o.is_closed = false
and o.is_picked = false
and o.is_invoiced = false
and o.is_sent = false
--and bi.id = bid.basketitem_id
For a query like the above, how restrictive is it? That is, of ALL the
records in order_basketitem table, how many are returned by the above
condition? I would think that the number of orders that have been picked or
invoiced or sent or closed or cancelled would be LARGE and so this query
may eliminate most of the orders from being considered. Not to mention the
order type id restriction.
If I found that the above query resulted in 1% of the table being returned
perhaps, there are a number of ways to influence the planner to do this
work first such as-
1) put this in a sub-query as the FROM and include OFFSET 0 hack to prevent
in-lining
2) put in a CTE using the WITH keyword (note- need to use MATERIALIZED
option once on PG12 since default behavior changes)
3) if the number of records returned is large (10 thousand maybe?) and the
optimizer is making bad choices on the rest of the query that uses this
result set, put this query into a temp table, analyze it, and then use it.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query optimization advice for beginners
2020-01-27 13:15 Query optimization advice for beginners Kemal Ortanca <kemal.ortanca@outlook.com>
@ 2020-01-28 08:37 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2020-01-28 08:37 UTC (permalink / raw)
To: Kemal Ortanca <kemal.ortanca@outlook.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-01-28 08:37 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-27 13:15 Query optimization advice for beginners Kemal Ortanca <kemal.ortanca@outlook.com>
2020-01-27 13:57 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2020-01-27 15:33 ` Kemal Ortanca <kemal.ortanca@outlook.com>
2020-01-27 16:46 ` Michael Lewis <mlewis@entrata.com>
2020-01-28 08:37 ` Laurenz Albe <laurenz.albe@cybertec.at>
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