How to help OneClickDBA to achieve the desired solution for your ticket.
A common problem
Imagine that you have the following execution plan on a query that is causing issues on production:
Limit (cost=93579.52..93580.68 rows=10 width=769) (actual time=234.355..240.666 rows=10 loops=1)
" Output: scpi.sku, scpi.hidden_for_privacy..."
" Buffers: shared hit=69962, temp read=3866 written=3962"
-> Gather Merge (cost=87162.40..93767.60 rows=56612 width=769) (actual time=173.211..237.499 rows=55010 loops=1)
" Output: scpi.sku, scpi.hidden_for_privacy..."
Workers Planned: 2
Workers Launched: 2
" Buffers: shared hit=69962, temp read=3866 written=3962"
-> Sort (cost=86162.38..86233.14 rows=28306 width=769) (actual time=163.165..177.751 rows=18427 loops=3)
" Output: scpi.sku, scpi.hidden_for_privacy..."
Sort Key: scpi.sku
Sort Method: external merge Disk: 11520kB
" Buffers: shared hit=69962, temp read=3866 written=3962"
Worker 0: actual time=154.595..167.976 rows=16896 loops=1
Sort Method: external merge Disk: 9544kB
" Buffers: shared hit=22340, temp read=1178 written=1196"
Worker 1: actual time=162.071..176.828 rows=18265 loops=1
Sort Method: external merge Disk: 10560kB
" Buffers: shared hit=24062, temp read=1307 written=1323"
-> Parallel Seq Scan on ms_schema.sell_hidden scpi (cost=0.00..74391.82 rows=28306 width=769) (actual time=0.015..87.886 rows=23676 loops=3)
" Output: scpi.sku, scpi.hidden_for_privacy..."
Filter: (scpi.sell_hidden_id = 12)
Rows Removed by Filter: 267544
Buffers: shared hit=69842
Worker 0: actual time=0.009..85.074 rows=21420 loops=1
Buffers: shared hit=22280
Worker 1: actual time=0.011..88.314 rows=23690 loops=1
Buffers: shared hit=24002
Planning Time: 0.183 ms
Execution Time: 244.012 ms
You submit a ticket to OneClickDBA, and we assist you in diagnosing and resolving the issue.
In this scenario, the query caused memory issues due to pagination. However, for the purpose of this example, we’ll focus on optimizing the Execution Time as the milestone.
Milestone Examples
Good Milestone
- Use a percentage reduction for Execution Time:
We need the query causing problems to consume 50% less execution time than it currently does.
- Reason: This allows us to optimize the query to improve performance while working toward a realistic, measurable goal.
Bad Milestone(s)
- Use a fixed time for Execution Time:
We need the query causing problems to execute in 10,000ms or less.
- Reason: The
Execution Timeof a query depends on various factors. While we aim to find the best solution, we may not always be able to achieve a specific fixed execution time due to limitations in the query’s structure or the database environment.
Result of OneClickDBA work
After a lot of hard work we provide a solution that dramatically reduced the time and complexity of the query:
Limit (cost=0.42..44.19 rows=10 width=769) (actual time=6.721..6.745 rows=10 loops=1)
" Output: scpi.sku, scpi.hidden_for_privacy..."
Buffers: shared hit=13 read=3
I/O Timings: read=6.632
-> Index Scan using sell_hidden_8_pkey on ms_schema.seller_hidden_8 scpi (cost=0.42..17840.39 rows=4076 width=769) (actual time=6.720..6.743 rows=10 loops=1)
" Output: scpi.sku, scpi.hidden_for_privacy..."
Index Cond: (((scpi.sku)::text >= 'SAM01306'::text) AND (scpi.seller_contract_id = 12))
Buffers: shared hit=13 read=3
I/O Timings: read=6.632
Planning:
Buffers: shared hit=5
Planning Time: 0.196 ms
Execution Time: 6.773 ms
Improvements:
- The query execution time dropped significantly from 244.012 ms to just 6.773 ms, thanks to the optimized index usage and query refinement.
- Memory usage was reduced by eliminating unnecessary operations like external sorting and parallel scans.
- The new execution plan demonstrates improved efficiency with minimal buffer hits and reduced I/O timings.