Changes between Version 2 and Version 3 of OtherTopics

Timestamp:

09/11/26 09:23:21 (7 hours ago)

Author:

232012

Comment:

--

OtherTopics

@@ -1 +1 @@
 = Other topics (Performance, Security, …)
 
-== Performance
+== Scenario 1 - Top Selling Products and Restock Plan
+
+==== Without indexes
+
+The query was tested on approximately 25,017 orders and 75,021 order-product records. Before indexing, PostgreSQL used sequential scans on both {{{orders}}} and {{{order_products}}}.
+
+{{{
+Seq Scan on order_products
+rows=75021
+
+Seq Scan on orders
+rows=5009
+Rows Removed by Filter: 20008
+}}}
+
+The query was executed 10 times and the average execution time without indexes was: **25.646 ms**
+
+==== Indexes
+{{{
+CREATE INDEX idx_orders_status_purchase_date
+ON project.orders (status, purchase_date, order_id);
+
+CREATE INDEX idx_order_products_order_product_quantity
+ON project.order_products (order_id, product_id, quantity);
+
+ANALYZE project.orders;
+ANALYZE project.order_products;
+}}}
+
+* The first index targets the filters on {{{status}}} and {{{purchase_date}}}, while also including {{{order_id}}} for the join.
+
+* The second index was tested to support the join and aggregation on {{{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 previous sequential scan on orders.
+
+However, PostgreSQL did not use {{{idx_order_products_order_product_quantity}}}. It continued using:
+
+{{{
+Seq Scan on order_products
+}}}
+
+because scanning the table and performing a hash join was estimated to be cheaper.
+
+The query was again executed 10 times and the average execution time with indexes was: **22.299 ms**
+
+==== Performance comparison and conclusion
+{{{
+Without indexes: 25.646 ms
+With indexes:    22.299 ms
+Improvement:     13.05%
+}}}
+
+* {{{idx_orders_status_purchase_date}}} was successfully used as an Index Only Scan and reduced the cost of filtering orders by status and purchase date.
+
+* {{{idx_order_products_order_product_quantity}}} was not used by the optimizer, because a sequential scan of order_products was still considered cheaper for the current dataset.
 
 == Security