Changes between Version 1 and Version 2 of Security&Optimization

Timestamp:

09/22/25 11:36:25 (3 days ago)

Author:

221164

Comment:

--

Security&Optimization

@@ -19 +19 @@
 || reservations || reservations_people_pos || number_of_people > 0 || Ensures that a reservation is made for at least one person. ||
 || front_staff || front_staff_tip_pct || tip_percent BETWEEN 0 AND 100 (or NULL) || Confines the tip percentage to a valid range if specified. ||
+
+== Index Performance Analysis ==
+We conducted a thorough analysis of key indexes to validate their performance impact on critical query patterns. The following sections detail the results, comparing query plans using EXPLAIN (ANALYZE, BUFFERS) with and without each index.
+
+=== Index: frontstaff_managed_reservations(table_number) — fmr_table_idx ===
+This B-Tree index is designed to accelerate lookups on the frontstaff_managed_reservations table for a specific table_number. This is a frequent operation for front-of-house staff managing table assignments.
+
+'''Query Analyzed''': SELECT * FROM frontstaff_managed_reservations WHERE table_number = 1;
+
+'''Selectivity''': High (~0.1% of 200k rows)
+
+||= Condition =||= Plan Type =||= Execution Time =||= Buffers (Shared) =||
+|| Without index || Sequential Scan || 16.235 ms || hit=32, read=1439 ||
+|| '''With index''' || '''Bitmap Index Scan''' || '''0.297 ms''' || '''hit=204, read=3''' ||
+
+'''Performance Impact''':
+The index provides a ~54x speedup in execution time. More importantly, it reduces disk reads by over 450x (from 1439 to 3), transforming an inefficient full table scan into a highly targeted index lookup.
+
+'''Conclusion''':
+This index is essential and provides a massive performance gain for selective table lookups, a core workflow of the application. Retain.
+
+=== GiST Index: reservations_span_expr_gist ===
+This is a GiST expression index on a tsrange created from reservation start times and stay lengths. It is built to optimize time-based overlap queries, which are critical for preventing double bookings and checking table availability.
+
+'''Query Analyzed''': tsrange(...) && tsrange(...) (checking for overlap in the next 30 minutes)
+
+'''Selectivity''': Medium (~2.1% of 500k rows)
+
+||= Condition =||= Plan Type =||= Execution Time =||= Buffers (Shared) =||
+|| Without index || Parallel Seq Scan || 141.415 ms || hit=94, read=4641 ||
+|| '''With index''' || '''Bitmap Index Scan (GiST)''' || '''3.646 ms''' || '''hit=249, read=0''' ||
+
+'''Performance Impact''':
+The GiST index is purpose-built for this type of query and delivers a ~39x speedup. It completely eliminates disk reads for this query by efficiently handling the range overlap && operator.
+
+'''Conclusion''':
+This index is fundamental for enforcing time-overlap constraints efficiently. It is critical for availability checks, double-booking prevention, and time-window dashboards. Retain.
+
+=== Index: payments(order_id) — idx_payments_order ===
+A standard B-Tree index on the order_id foreign key column in the payments table. This is intended to speed up joins and direct lookups from an order to its associated payments.
+
+'''Queries Analyzed''': COUNT(*) and JOIN on payments for a specific order_id.
+
+'''Selectivity''': Extremely high (finding a few payments among ~700k rows)
+
+||= Query Type =||= Condition =||= Plan Type =||= Execution Time =||
+|| COUNT(*) || Without index || Parallel Seq Scan || 34.445 ms ||
+|| COUNT(*) || '''With index''' || '''Index Only Scan''' || '''0.120 ms''' ||
+|| JOIN || Without index || Parallel Seq Scan || 33.313 ms ||
+|| JOIN || '''With index''' || '''Bitmap Index Scan''' || '''0.075 ms''' ||
+
+'''Performance Impact''':
+For highly selective equality lookups, the index offers a dramatic ~280-440x speedup. It allows the database to avoid scanning hundreds of thousands of payment rows by jumping directly to the relevant entries. The ability to perform an "Index Only Scan" for the count is especially efficient.
+
+'''Conclusion''':
+This index is indispensable for fast foreign key lookups and joins, a foundational operation for retrieving order and payment details. Retain.
+
+=== Index: payments(created_at) — idx_payments_created_at ===
+A time-series B-Tree index on the created_at timestamp in the payments table. Its value depends heavily on the selectivity of the time window being queried.
+
+'''Queries Analyzed''': COUNT(*) on payments over a broad (90-day) and a narrow (1-minute) time window.
+
+||= Time Window =||= Condition =||= Plan Type =||= Execution Time =||
+|| 90 Days (Low Selectivity) || Without index || Parallel Seq Scan || 234.384 ms ||
+|| 90 Days (Low Selectivity) || With index || Parallel Seq Scan || 239.074 ms ||
+|| 1 Minute (High Selectivity) || Without index || Parallel Seq Scan || 119.739 ms ||
+|| 1 Minute (High Selectivity) || '''With index''' || '''Index Only Scan''' || '''0.069 ms''' ||
+
+'''Performance Impact''':
+The analysis shows two distinct behaviors:
+
+Broad Window (Low Selectivity): When the query selects a large fraction of the table (e.g., 90 days of data), the planner correctly determines that a full Parallel Seq Scan is more efficient. The index is ignored and offers no benefit.
+
+Narrow Window (High Selectivity): When querying a tiny, recent time slice (e.g., the last minute), the index is overwhelmingly effective, providing a ~1,700x speedup and using an extremely fast Index Only Scan.
+
+'''Conclusion''':
+This index is highly valuable for "recent data" use cases like real-time dashboards or alerts. It is neutral for broad analytical queries where sequential scans are appropriate. The planner correctly chooses the best path in both scenarios. Retain.
 
 == Triggered Business Rules ==