Case Study - 6 | Problem - 1 | Optimizing Query Execution Time through Database Indexing for FoodWagon
Learning Objectives
Overview
FoodWagon, a flourishing food ordering, and delivery platform, has witnessed remarkable growth, particularly among the bustling millennial population. This surge in popularity has translated to a tenfold increase in order volumes and restaurant partnerships.
Their current data is being gathered in an SQL Database that includes customers, partners, restaurants, food, orders, and orders_details tables, frequently queried to generate insights into delivery performance, customer behavior, and vendor efficiency.
However, due to poor query optimization, operations like joins, aggregations, and filtering lead to high execution costs and slow response times. This also leads to inefficient use of database resources
This Scenario aims to overcome these bottlenecks by implementing advanced query optimization techniques. You will work on improving the performance of analytical queries by restructuring queries, leveraging indexes, and applying best practices in query optimization.
The goal is to ensure that key business reports run efficiently, even for large datasets, enabling real-time insights and decision-making.
Prerequisites
- Ability to write intermediate to advanced SQL queries, including complex joins, aggregations, CTE & Sub Query
- Understanding of execution plans and query performance metrics.
- Basic knowledge of indexing and how it effects query performance
- Familiarity with relational database design & Modelling
- Familiarity with Catalog and System views of Postgres