pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow query to improve performace
3+ messages / 3 participants
[nested] [flat]
* slow query to improve performace
@ 2022-02-25 20:18 Ayub Khan <ayub.hp@gmail.com>
2022-02-25 22:52 ` Re: slow query to improve performace Justin Pryzby <pryzby@telsasoft.com>
2022-02-28 03:18 ` Re: slow query to improve performace Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Ayub Khan @ 2022-02-25 20:18 UTC (permalink / raw)
To: pgsql-performance
Hi,
Could some some verify the attached query to verify the performance and
suggest some steps to improve it, this query is created as a view. This
view is used to get the aggregates of orders based on its current status
Thanks
HashAggregate (cost=334063.59..334064.17 rows=58 width=213) (actual time=1180.016..1180.053 rows=49 loops=1)
Group Key: c.city_id, '2022-02-25 23:09:26.587835'::timestamp without time zone, (count(*))
-> Nested Loop Left Join (cost=154967.02..312398.93 rows=133321 width=92) (actual time=450.918..961.314 rows=156105 loops=1)
Join Filter: (c_1.city_id = c.city_id)
Rows Removed by Join Filter: 103951
-> Hash Join (cost=2343.27..154695.90 rows=133321 width=76) (actual time=12.345..456.445 rows=156105 loops=1)
Hash Cond: (b.city_id = c.city_id)
-> Hash Join (cost=2324.74..154301.83 rows=133321 width=55) (actual time=12.266..420.340 rows=156105 loops=1)
Hash Cond: (o.branch_id = b.branch_id)
-> Append (cost=0.57..151627.65 rows=133321 width=55) (actual time=0.022..347.441 rows=156105 loops=1)
Subplans Removed: 23
-> Index Scan using restaurant_order_p_202202_202203_date_time_idx on restaurant_order_p_202202_202203 o (cost=0.56..150904.37 rows=133298 width=55) (actual time=0.022..333.768 rows=156105 loops=1)
Index Cond: ((date_time >= '2022-02-25 05:00:00'::timestamp without time zone) AND (date_time <= '2022-02-25 23:09:26.587835'::timestamp without time zone))
-> Hash (cost=1942.41..1942.41 rows=30541 width=16) (actual time=12.172..12.172 rows=29242 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1627kB
-> Seq Scan on branch b (cost=0.00..1942.41 rows=30541 width=16) (actual time=0.005..7.358 rows=29242 loops=1)
-> Hash (cost=17.80..17.80 rows=58 width=29) (actual time=0.072..0.072 rows=58 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Index Only Scan using city_idx$$_274b0022 on city c (cost=0.27..17.80 rows=58 width=29) (actual time=0.011..0.056 rows=58 loops=1)
Index Cond: (city_id IS NOT NULL)
Heap Fetches: 40
-> Materialize (cost=152623.75..155703.21 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=156105)
-> GroupAggregate (cost=152623.75..155703.19 rows=1 width=16) (actual time=438.567..438.611 rows=2 loops=1)
Group Key: c_1.city_id
-> Nested Loop Left Join (cost=152623.75..155703.18 rows=1 width=8) (actual time=434.578..438.605 rows=6 loops=1)
-> Nested Loop (cost=152623.32..155701.62 rows=1 width=16) (actual time=434.569..438.581 rows=6 loops=1)
Join Filter: (b_1.city_id = c_1.city_id)
Rows Removed by Join Filter: 342
-> Index Only Scan using city_pk on city c_1 (cost=0.27..13.27 rows=58 width=8) (actual time=0.015..0.062 rows=58 loops=1)
Heap Fetches: 40
-> Materialize (cost=152623.06..155687.49 rows=1 width=16) (actual time=7.492..7.560 rows=6 loops=58)
-> Nested Loop (cost=152623.06..155687.48 rows=1 width=16) (actual time=434.549..438.450 rows=6 loops=1)
-> Nested Loop (cost=152622.77..155687.17 rows=1 width=32) (actual time=434.539..438.425 rows=6 loops=1)
Join Filter: (r_1.restaurant_id = b_1.restaurant_id)
-> Nested Loop (cost=152622.48..155685.14 rows=1 width=24) (actual time=434.529..438.396 rows=6 loops=1)
-> GroupAggregate (cost=152621.92..152628.01 rows=79 width=55) (actual time=434.465..438.214 rows=3 loops=1)
Group Key: (round(ro_1.total_amount, 2)), ro_1.phone, r_1.restaurant_id
Filter: (count(1) > 1)
Rows Removed by Filter: 9365
-> Sort (cost=152621.92..152622.51 rows=236 width=55) (actual time=434.273..435.044 rows=9371 loops=1)
Sort Key: (round(ro_1.total_amount, 2)), ro_1.phone, r_1.restaurant_id
Sort Method: quicksort Memory: 1117kB
-> Nested Loop Left Join (cost=1.83..152612.62 rows=236 width=55) (actual time=163.508..417.596 rows=9371 loops=1)
-> Nested Loop (cost=1.41..152245.05 rows=236 width=37) (actual time=163.493..396.303 rows=9371 loops=1)
-> Nested Loop (cost=1.14..152178.31 rows=236 width=45) (actual time=163.475..377.443 rows=9371 loops=1)
-> Nested Loop (cost=0.85..152105.61 rows=236 width=45) (actual time=163.459..353.730 rows=9371 loops=1)
-> Append (cost=0.57..151628.83 rows=236 width=37) (actual time=163.439..322.719 rows=9371 loops=1)
Subplans Removed: 23
-> Index Scan using restaurant_order_p_202202_202203_date_time_idx on restaurant_order_p_202202_202203 ro_1 (cost=0.56..151570.86 rows=213 width=34) (actual time=163.438..321.864 rows=9371 loops=1)
Index Cond: ((date_time >= '2022-02-25 05:00:00'::timestamp without time zone) AND (date_time <= '2022-02-25 23:09:26.587835'::timestamp without time zone))
Filter: ((schedule_datetime IS NULL) AND (order_status_code <> ALL ('{T,J,C,D}'::bpchar[])))
Rows Removed by Filter: 146734
-> Index Scan using branch_idx$$_274b0038 on branch b_2 (cost=0.29..2.02 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=9371)
Index Cond: (branch_id = ro_1.branch_id)
-> Index Only Scan using restaurant_idx$$_274b003d on restaurant r_1 (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=9371)
Index Cond: (restaurant_id = b_2.restaurant_id)
Heap Fetches: 6155
-> Index Only Scan using city_pk on city c_2 (cost=0.27..0.28 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=9371)
Index Cond: (city_id = b_2.city_id)
Heap Fetches: 9141
-> Index Only Scan using order_offer_map_order_id on order_offer_map oom_1 (cost=0.43..1.55 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=9371)
Index Cond: (order_id = ro_1.order_id)
Heap Fetches: 34
-> Append (cost=0.57..38.45 rows=24 width=37) (actual time=0.039..0.059 rows=2 loops=3)
Subplans Removed: 23
-> Index Scan using restaurant_order_p_202202_202203_phone_idx on restaurant_order_p_202202_202203 ro (cost=0.56..14.11 rows=1 width=34) (actual time=0.038..0.057 rows=2 loops=3)
Index Cond: ((phone)::text = (ro_1.phone)::text)
Filter: ((schedule_datetime IS NULL) AND (date_time >= '2022-02-25 05:00:00'::timestamp without time zone) AND (date_time <= '2022-02-25 23:09:26.587835'::timestamp without time zone) AND (order_status_code <> ALL ('{T,J,C,D}'::bpchar[])) AND ((round(ro_1.total_amount, 2)) = round(total_amount, 2)))
Rows Removed by Filter: 12
-> Index Scan using branch_idx$$_274b0038 on branch b_1 (cost=0.29..2.02 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=6)
Index Cond: (branch_id = ro.branch_id)
-> Index Only Scan using restaurant_idx$$_274b003d on restaurant r (cost=0.29..0.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=6)
Index Cond: (restaurant_id = b_1.restaurant_id)
Heap Fetches: 6
-> Index Only Scan using order_offer_map_order_id on order_offer_map oom (cost=0.43..1.55 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=6)
Index Cond: (order_id = ro.order_id)
Heap Fetches: 0
Planning Time: 27.392 ms
Execution Time: 1180.579 ms
Attachments:
[text/plain] explain.txt (10.2K, ../../CAHdeyEKbZgCY1C+LkAYoKKnJ-sajk5Ky8sfhQNP_+wKWRs9iVQ@mail.gmail.com/3-explain.txt)
download | inline:
HashAggregate (cost=334063.59..334064.17 rows=58 width=213) (actual time=1180.016..1180.053 rows=49 loops=1)
Group Key: c.city_id, '2022-02-25 23:09:26.587835'::timestamp without time zone, (count(*))
-> Nested Loop Left Join (cost=154967.02..312398.93 rows=133321 width=92) (actual time=450.918..961.314 rows=156105 loops=1)
Join Filter: (c_1.city_id = c.city_id)
Rows Removed by Join Filter: 103951
-> Hash Join (cost=2343.27..154695.90 rows=133321 width=76) (actual time=12.345..456.445 rows=156105 loops=1)
Hash Cond: (b.city_id = c.city_id)
-> Hash Join (cost=2324.74..154301.83 rows=133321 width=55) (actual time=12.266..420.340 rows=156105 loops=1)
Hash Cond: (o.branch_id = b.branch_id)
-> Append (cost=0.57..151627.65 rows=133321 width=55) (actual time=0.022..347.441 rows=156105 loops=1)
Subplans Removed: 23
-> Index Scan using restaurant_order_p_202202_202203_date_time_idx on restaurant_order_p_202202_202203 o (cost=0.56..150904.37 rows=133298 width=55) (actual time=0.022..333.768 rows=156105 loops=1)
Index Cond: ((date_time >= '2022-02-25 05:00:00'::timestamp without time zone) AND (date_time <= '2022-02-25 23:09:26.587835'::timestamp without time zone))
-> Hash (cost=1942.41..1942.41 rows=30541 width=16) (actual time=12.172..12.172 rows=29242 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 1627kB
-> Seq Scan on branch b (cost=0.00..1942.41 rows=30541 width=16) (actual time=0.005..7.358 rows=29242 loops=1)
-> Hash (cost=17.80..17.80 rows=58 width=29) (actual time=0.072..0.072 rows=58 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> Index Only Scan using city_idx$$_274b0022 on city c (cost=0.27..17.80 rows=58 width=29) (actual time=0.011..0.056 rows=58 loops=1)
Index Cond: (city_id IS NOT NULL)
Heap Fetches: 40
-> Materialize (cost=152623.75..155703.21 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=156105)
-> GroupAggregate (cost=152623.75..155703.19 rows=1 width=16) (actual time=438.567..438.611 rows=2 loops=1)
Group Key: c_1.city_id
-> Nested Loop Left Join (cost=152623.75..155703.18 rows=1 width=8) (actual time=434.578..438.605 rows=6 loops=1)
-> Nested Loop (cost=152623.32..155701.62 rows=1 width=16) (actual time=434.569..438.581 rows=6 loops=1)
Join Filter: (b_1.city_id = c_1.city_id)
Rows Removed by Join Filter: 342
-> Index Only Scan using city_pk on city c_1 (cost=0.27..13.27 rows=58 width=8) (actual time=0.015..0.062 rows=58 loops=1)
Heap Fetches: 40
-> Materialize (cost=152623.06..155687.49 rows=1 width=16) (actual time=7.492..7.560 rows=6 loops=58)
-> Nested Loop (cost=152623.06..155687.48 rows=1 width=16) (actual time=434.549..438.450 rows=6 loops=1)
-> Nested Loop (cost=152622.77..155687.17 rows=1 width=32) (actual time=434.539..438.425 rows=6 loops=1)
Join Filter: (r_1.restaurant_id = b_1.restaurant_id)
-> Nested Loop (cost=152622.48..155685.14 rows=1 width=24) (actual time=434.529..438.396 rows=6 loops=1)
-> GroupAggregate (cost=152621.92..152628.01 rows=79 width=55) (actual time=434.465..438.214 rows=3 loops=1)
Group Key: (round(ro_1.total_amount, 2)), ro_1.phone, r_1.restaurant_id
Filter: (count(1) > 1)
Rows Removed by Filter: 9365
-> Sort (cost=152621.92..152622.51 rows=236 width=55) (actual time=434.273..435.044 rows=9371 loops=1)
Sort Key: (round(ro_1.total_amount, 2)), ro_1.phone, r_1.restaurant_id
Sort Method: quicksort Memory: 1117kB
-> Nested Loop Left Join (cost=1.83..152612.62 rows=236 width=55) (actual time=163.508..417.596 rows=9371 loops=1)
-> Nested Loop (cost=1.41..152245.05 rows=236 width=37) (actual time=163.493..396.303 rows=9371 loops=1)
-> Nested Loop (cost=1.14..152178.31 rows=236 width=45) (actual time=163.475..377.443 rows=9371 loops=1)
-> Nested Loop (cost=0.85..152105.61 rows=236 width=45) (actual time=163.459..353.730 rows=9371 loops=1)
-> Append (cost=0.57..151628.83 rows=236 width=37) (actual time=163.439..322.719 rows=9371 loops=1)
Subplans Removed: 23
-> Index Scan using restaurant_order_p_202202_202203_date_time_idx on restaurant_order_p_202202_202203 ro_1 (cost=0.56..151570.86 rows=213 width=34) (actual time=163.438..321.864 rows=9371 loops=1)
Index Cond: ((date_time >= '2022-02-25 05:00:00'::timestamp without time zone) AND (date_time <= '2022-02-25 23:09:26.587835'::timestamp without time zone))
Filter: ((schedule_datetime IS NULL) AND (order_status_code <> ALL ('{T,J,C,D}'::bpchar[])))
Rows Removed by Filter: 146734
-> Index Scan using branch_idx$$_274b0038 on branch b_2 (cost=0.29..2.02 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=9371)
Index Cond: (branch_id = ro_1.branch_id)
-> Index Only Scan using restaurant_idx$$_274b003d on restaurant r_1 (cost=0.29..0.31 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=9371)
Index Cond: (restaurant_id = b_2.restaurant_id)
Heap Fetches: 6155
-> Index Only Scan using city_pk on city c_2 (cost=0.27..0.28 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=9371)
Index Cond: (city_id = b_2.city_id)
Heap Fetches: 9141
-> Index Only Scan using order_offer_map_order_id on order_offer_map oom_1 (cost=0.43..1.55 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=9371)
Index Cond: (order_id = ro_1.order_id)
Heap Fetches: 34
-> Append (cost=0.57..38.45 rows=24 width=37) (actual time=0.039..0.059 rows=2 loops=3)
Subplans Removed: 23
-> Index Scan using restaurant_order_p_202202_202203_phone_idx on restaurant_order_p_202202_202203 ro (cost=0.56..14.11 rows=1 width=34) (actual time=0.038..0.057 rows=2 loops=3)
Index Cond: ((phone)::text = (ro_1.phone)::text)
Filter: ((schedule_datetime IS NULL) AND (date_time >= '2022-02-25 05:00:00'::timestamp without time zone) AND (date_time <= '2022-02-25 23:09:26.587835'::timestamp without time zone) AND (order_status_code <> ALL ('{T,J,C,D}'::bpchar[])) AND ((round(ro_1.total_amount, 2)) = round(total_amount, 2)))
Rows Removed by Filter: 12
-> Index Scan using branch_idx$$_274b0038 on branch b_1 (cost=0.29..2.02 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=6)
Index Cond: (branch_id = ro.branch_id)
-> Index Only Scan using restaurant_idx$$_274b003d on restaurant r (cost=0.29..0.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=6)
Index Cond: (restaurant_id = b_1.restaurant_id)
Heap Fetches: 6
-> Index Only Scan using order_offer_map_order_id on order_offer_map oom (cost=0.43..1.55 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=6)
Index Cond: (order_id = ro.order_id)
Heap Fetches: 0
Planning Time: 27.392 ms
Execution Time: 1180.579 ms
[application/octet-stream] slow_query.sql (6.7K, ../../CAHdeyEKbZgCY1C+LkAYoKKnJ-sajk5Ky8sfhQNP_+wKWRs9iVQ@mail.gmail.com/4-slow_query.sql)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: slow query to improve performace
2022-02-25 20:18 slow query to improve performace Ayub Khan <ayub.hp@gmail.com>
@ 2022-02-25 22:52 ` Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 3+ messages in thread
From: Justin Pryzby @ 2022-02-25 22:52 UTC (permalink / raw)
To: Ayub Khan <ayub.hp@gmail.com>; +Cc: pgsql-performance
Please provide some more information, like your postgres version and settings.
Some relevant things are included here.
https://wiki.postgresql.org/wiki/Slow_Query_Questions
--
Justin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: slow query to improve performace
2022-02-25 20:18 slow query to improve performace Ayub Khan <ayub.hp@gmail.com>
@ 2022-02-28 03:18 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jeff Janes @ 2022-02-28 03:18 UTC (permalink / raw)
To: Ayub Khan <ayub.hp@gmail.com>; +Cc: pgsql-performance
On Fri, Feb 25, 2022 at 3:18 PM Ayub Khan <ayub.hp@gmail.com> wrote:
> Hi,
>
> Could some some verify the attached query to verify the performance and
> suggest some steps to improve it, this query is created as a view. This
> view is used to get the aggregates of orders based on its current status
>
I don't see how it is possible for that query to yield that plan. For
example, what part of that query could have been transformed into this part
of the plan "order_status_code <> ALL ('{T,J,C,D}'::bpchar[])"?
Well, I suppose some of the tables used in that query could themselves be
views over the same tables? In that case, we might need to know the
definitions of those views. As well as knowing the version, and seeing the
EXPLAIN (ANALYZE, BUFFERS) for the query, run after track_io_timing is
turned on.
Cheers,
Jeff
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-02-28 03:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-25 20:18 slow query to improve performace Ayub Khan <ayub.hp@gmail.com>
2022-02-25 22:52 ` Justin Pryzby <pryzby@telsasoft.com>
2022-02-28 03:18 ` Jeff Janes <jeff.janes@gmail.com>
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