Changes between Version 4 and Version 5 of OtherTopics

Timestamp:

09/11/26 10:17:03 (7 hours ago)

Author:

232012

Comment:

--

OtherTopics

@@ -69 +69 @@
 
 }}}
+
+== Scenario 2 - Slow Moving Products
+
+==== Without indexes
+
+The query was tested on approximately 25,017 orders and 75,021 order-product records. Before indexing, PostgreSQL used a sequential scan on {{{orders}}} to find orders from the last 6 months with a completed status.
+
+{{{
+Seq Scan on orders
+rows=2549
+Rows Removed by Filter: 22468
+}}}
+
+The existing primary-key index on {{{order_products}}} was already used:
+
+{{{
+Index Only Scan using order_products_pk on order_products
+}}}
+
+The query was executed 10 times and the average execution time without indexes was: **12.125 ms**
+
+==== Indexes
+
+{{{
+CREATE INDEX idx_orders_status_purchase_date
+ON project.orders (status, purchase_date, order_id);
+
+ANALYZE project.orders;
+}}}
+
+The index targets the {{{status}}} and {{{purchase_date}}} filters and also includes {{{order_id}}} for the join with {{{order_products}}}.
+
+==== With indexes
+
+After indexing, PostgreSQL used:
+
+{{{
+Index Only Scan using idx_orders_status_purchase_date on orders
+Heap Fetches: 0
+}}}
+
+This replaced the sequential scan on {{{orders}}} and reduced the number of pages that had to be read.
+
+The existing {{{order_products_pk}}} index continued to be used for the join with {{{order_products}}}.
+
+The query was again executed 10 times and the average execution time with indexes was: **8.894 ms**
+
+==== Performance comparison and conclusion
+
+{{{
+Without indexes: 12.125 ms
+With indexes: 8.894 ms
+Improvement: 26.65%
+}}}
+
+{{{idx_orders_status_purchase_date}}} was successfully used as an Index Only Scan and improved the filtering of orders by status and purchase date.
+The query improved by approximately 26.65%, while the existing {{{order_products_pk}}} index continued to support the join efficiently.
 
 == Security