Changes between Version 5 and Version 6 of OtherTopics

Timestamp:

09/11/26 10:39:13 (6 hours ago)

Author:

232012

Comment:

--

OtherTopics

@@ -72 +72 @@
 == Scenario 2 - Slow Moving Products
 
+{{{#!div style="text-align: justify; width: 100%;"
+
 ==== Without indexes
 
@@ -126 +128 @@
 {{{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.
+
+}}}
+
+== Scenario 3 - Impact of Admin Discounts on Sales Numbers
+
+{{{#!div style="text-align: justify; width: 100%;"
+
+==== 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}}} while calculating the 30-day periods before and after each discount.
+
+{{{
+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: **103.004 ms**
+
+==== Indexes
+
+{{{
+CREATE INDEX idx_order_products_product_order
+ON project.order_products (product_id, order_id)
+INCLUDE (quantity, price_at_purchase);
+
+CREATE INDEX idx_orders_status_purchase_date
+ON project.orders (status, purchase_date, order_id);
+
+ANALYZE project.order_products;
+ANALYZE project.orders;
+}}}
+
+* {{{idx_order_products_product_order}}} was tested to support lookups of order products by {{{product_id}}} and provide the quantity and purchase price required by the aggregation.
+
+* {{{idx_orders_status_purchase_date}}} targets the order status filter and includes the purchase date and {{{order_id}}} needed for the sales-period joins.
+
+==== With indexes
+
+PostgreSQL used:
+
+{{{
+Index Only Scan using idx_orders_status_purchase_date on orders
+Heap Fetches: 0
+}}}
+
+The index was used for both the pre-promotion and post-promotion order lookups.
+
+However, {{{idx_order_products_product_order}}} was not used. PostgreSQL continued using:
+
+{{{
+Seq Scan on order_products
+}}}
+
+because scanning the table and performing the hash joins was estimated to be cheaper for the current data distribution.
+
+The query was executed 10 times with both indexes present and the average execution time was: **97.710 ms**
+
+The unused {{{idx_order_products_product_order}}} index was removed after testing.
+
+==== Performance comparison and conclusion
+
+{{{
+Without indexes: 103.004 ms
+With indexes: 97.710 ms
+Improvement: 5.14%
+}}}
+
+* {{{idx_orders_status_purchase_date}}} was successfully used as an Index Only Scan in both the pre-promotion and post-promotion parts of the query.
+
+* {{{idx_order_products_product_order}}} was not used by the optimizer and was removed.
+
+}}}
+
+== Scenario 4 - Customer Habits and Points Spending
+
+==== 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}}} and scanned all {{{order_products}}} records to calculate order totals.
+
+{{{
+Seq Scan on orders
+rows=5009
+Rows Removed by Filter: 20008
+
+Seq Scan on order_products
+rows=75021
+}}}
+
+The {{{order_products}}} aggregation also required temporary disk usage:
+
+{{{
+HashAggregate
+Batches: 5
+Disk Usage: 760kB
+}}}
+
+The query was executed 10 times and the average execution time without indexes was: **93.895 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 on {{{orders}}} and includes {{{order_id}}} for the join with aggregated order totals.
+
+==== With indexes
+
+After indexing, PostgreSQL used:
+
+{{{
+Bitmap Index Scan on idx_orders_status_purchase_date
+
+Bitmap Heap Scan on orders
+}}}
+
+This replaced the sequential scan on {{{orders}}}.
+
+However, {{{order_products}}} was still processed using:
+
+{{{
+Seq Scan on order_products
+rows=75021
+}}}
+
+because the query needs to aggregate essentially the whole {{{order_products}}} table to calculate total spend per order.
+
+The query was executed 10 times and the average execution time with indexes was: **90.547 ms**
+
+==== Performance comparison and conclusion
+
+{{{
+Without indexes: 93.895 ms
+With indexes: 90.547 ms
+Improvement: 3.57%
+}}}
+
+* {{{idx_orders_status_purchase_date}}} was successfully used through a Bitmap Index Scan and reduced the cost of filtering orders.
+
+* The overall improvement was approximately 3.57% because the main remaining cost is the full aggregation of {{{order_products}}}, which still requires a sequential scan and temporary disk usage.
 
 == Security