# Optimizing Query Performance in YugabyteDB!

**URL:** <https://forum.yugabyte.com/t/optimizing-query-performance-in-yugabytedb/4167>\
**Category:** General\
**Created:** [January 29, 2025, 12:42pm UTC](https://forum.yugabyte.com/t/optimizing-query-performance-in-yugabytedb/4167 "2025-01-29T12:42:53Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![marcelosalas00](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/marcelosalas00/32/895_2.png) [@marcelosalas00](https://forum.yugabyte.com/u/marcelosalas00)\
**Post date:** [January 29, 2025, 12:42pm UTC](https://forum.yugabyte.com/t/optimizing-query-performance-in-yugabytedb/4167/1 "2025-01-29T12:42:53Z")

</div>

Hey everyone,

I am working on optimizing queries in YugabyteDB and I would love to get some insights from the community. I have a dataset with millions of rows…, and I have noticed that some queries take longer than expected…, even with proper indexing.

Query Execution Plans – What’s the best way to analyze and optimize execution plans in YugabyteDB: ?? Are there any tools or strategies you recommend: ??  
Sharding Impact – How does the default sharding strategy impact query performance: ?? Would manually defining table partitions help in my case: ??  
Joins vs. Denormalization – For high-performance reads, is it better to normalize and use joins, or should I consider some level of denormalization: ??  
Caching Mechanisms – Does YugabyteDB offer any built-in caching mechanisms, or should I rely on external caching solutions like Redis: ??

Any best practices or real-world experiences would be really helpful !! Looking forward to your expert advice. I have also read this thread [https://docs.yugabyte.com/preview/explore/query-1-performance/pg-hint-plan-](https://docs.yugabyte.com/preview/explore/query-1-performance/pg-hint-plan/)[servicenow](https://www.igmguru.com/project-management/servicenow-training) but couldn’t get enough solution.

Thanks in advance !!

With Regards,  
Marcelo Salas

---

<div class="post-metadata">

**Author:** ![dorian\_yugabyte](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/dorian_yugabyte/32/206_2.png) [@dorian\_yugabyte](https://forum.yugabyte.com/u/dorian_yugabyte)\
**Post date:** [January 29, 2025, 1:05pm UTC](https://forum.yugabyte.com/t/optimizing-query-performance-in-yugabytedb/4167/2 "2025-01-29T13:05:39Z")

</div>

Hi @marcelosalas00

As a general answer see best practices: [Best practices for YSQL applications | YugabyteDB Docs](https://docs.yugabyte.com/preview/develop/best-practices-ysql/)

> [@marcelosalas00](#):
>
> Query Execution Plans – What’s the best way to analyze and optimize execution plans in YugabyteDB: ?? Are there any tools or strategies you recommend: ??

Simple explain analyze, then looking at [Query Tuning | YugabyteDB Docs](https://docs.yugabyte.com/preview/explore/query-1-performance/)

> [@marcelosalas00](#):
>
> Sharding Impact – How does the default sharding strategy impact query performance: ??

It has a big impact.

> [@marcelosalas00](#):
>
> Would manually defining table partitions help in my case: ??

Partitions usually help in time-series data where you want to drop old partitions.

> [@marcelosalas00](#):
>
> Joins vs. Denormalization – For high-performance reads, is it better to normalize and use joins, or should I consider some level of denormalization: ??

Depends on the exact case.

> [@marcelosalas00](#):
>
> Caching Mechanisms – Does YugabyteDB offer any built-in caching mechanisms, or should I rely on external caching solutions like Redis: ??

Depends on the exact case.

> [@marcelosalas00](#):
>
> Any best practices or real-world experiences would be really helpful !!

Please describe one or each workload in detail:

1. table/indexes schema
2. queries + explain analyze
3. What the workload is like? How many reads/writes? How big/small are the batches etc.
4. node/cluster hardware (vcpu,memory,network,disk)

Then I’ll make recommendations and explaining why.

---

<div class="post-metadata">

**Author:** ![FranckPachot](https://yyz1.discourse-cdn.com/flex027/user_avatar/forum.yugabyte.com/franckpachot/32/353_2.png) [@FranckPachot](https://forum.yugabyte.com/u/FranckPachot)\
**Post date:** [January 29, 2025, 2:16pm UTC](https://forum.yugabyte.com/t/optimizing-query-performance-in-yugabytedb/4167/3 "2025-01-29T14:16:44Z")

</div>

Hi @marcelosalas00 please share the execution plan taken with `explain (analyze, dist)` as it will give all info. The sharding impact will be visible in the number of read requests.  
Here is an example:

> **[Batched Nested Loop for Join With Large Pagination](https://dev.to/yugabyte/batched-nested-loop-for-join-with-pagination-621)**
>
> TL;DR: You can skip to the last execution plan to see the best solution (Order-Preserving Batched...

Note that to get the query planner find the best plan you need to use the cost based optimizer, which means:

- ANALYZE the tables (there’s no auto-analyze yet)
- have `yb_enable_base_scans_cost_model` set to `on` (it’s the default only when you start with postgres parity enabled)