Changes between Version 2 and Version 3 of OtherTopics


Ignore:
Timestamp:
06/24/26 12:49:08 (8 days ago)
Author:
221511
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • OtherTopics

    v2 v3  
    33== SQL Performance ==
    44
    5 Performance analysis was conducted against the full complex report queries from Phase P6 ([wiki:ComplexReports ComplexReports]). The tests were run on the local development database after loading 200,000 synthetic reservations spread uniformly across 2 years (2024-2026). The small seed dataset (26 rows) cannot reveal index usefulness because sequential scans are optimal at that scale.
     5Performance analysis was conducted against the full complex report queries from Phase P6 ([wiki:ComplexReports ComplexReports]). The tests were run on the local development database after loading 200,000 synthetic reservations (200,028 total rows including the seed dataset). The synthetic data spans 2024-01-01 to 2025-09-09, with 29,484 rows falling in Q1 2024 (the quarter used for Report 1 testing). The small seed dataset (28 rows) cannot reveal index usefulness because sequential scans are optimal at that scale.
    66
    77The testing method: each P6 report query is executed with {{{EXPLAIN (ANALYZE, BUFFERS)}}} before adding new indexes and after. We compare execution time, scan type, and buffer usage.
    88
     9=== Synthetic Data Generation Script ===
     10
     11The following script generates 200,000 synthetic reservations for performance testing. It uses {{{generate_series}}} with a deterministic slot assignment so each (resource, day, hour) combination is unique, a fixed status distribution, and disables triggers during the bulk load to skip overlap checks. To remove the synthetic rows afterwards: {{{DELETE FROM project.reservations WHERE purpose LIKE 'Synthetic reservation%';}}}
     12
     13{{{
     14SET search_path TO project;
     15
     16ALTER TABLE reservations DISABLE TRIGGER ALL;
     17
     18INSERT INTO reservations
     19    (start_time, end_time, status, purpose, created_at, user_id, resource_id, approved_by)
     20SELECT
     21    ('2024-01-01'::TIMESTAMPTZ
     22        + (gs / 324) * INTERVAL '1 day'
     23        + (((gs / 27) % 12) + 8) * INTERVAL '1 hour')                        AS start_time,
     24    ('2024-01-01'::TIMESTAMPTZ
     25        + (gs / 324) * INTERVAL '1 day'
     26        + (((gs / 27) % 12) + 10) * INTERVAL '1 hour')                       AS end_time,
     27    CASE gs % 20
     28        WHEN 0  THEN 'approved'
     29        WHEN 1  THEN 'approved'
     30        WHEN 2  THEN 'approved'
     31        WHEN 3  THEN 'approved'
     32        WHEN 4  THEN 'approved'
     33        WHEN 5  THEN 'approved'
     34        WHEN 6  THEN 'approved'
     35        WHEN 7  THEN 'approved'
     36        WHEN 8  THEN 'completed'
     37        WHEN 9  THEN 'completed'
     38        WHEN 10 THEN 'completed'
     39        WHEN 11 THEN 'completed'
     40        WHEN 12 THEN 'completed'
     41        WHEN 13 THEN 'completed'
     42        WHEN 14 THEN 'rejected'
     43        WHEN 15 THEN 'rejected'
     44        WHEN 16 THEN 'rejected'
     45        WHEN 17 THEN 'pending'
     46        WHEN 18 THEN 'pending'
     47        ELSE         'cancelled'
     48    END                                                                        AS status,
     49    'Synthetic reservation ' || gs                                             AS purpose,
     50    ('2024-01-01'::TIMESTAMPTZ + (gs / 324) * INTERVAL '1 day')               AS created_at,
     51    (gs % 10) + 3                                                              AS user_id,
     52    (gs % 27) + 1                                                              AS resource_id,
     53    CASE WHEN gs % 20 <= 15
     54        THEN (gs % 2) + 1
     55        ELSE NULL
     56    END                                                                        AS approved_by
     57FROM generate_series(0, 199999) AS gs;
     58
     59ALTER TABLE reservations ENABLE TRIGGER ALL;
     60
     61ANALYZE reservations;
     62}}}
     63
     64'''Slot assignment logic:'''
     65 * {{{resource_id = (gs % 27) + 1}}} — cycles through all 27 resources
     66 * {{{day = 2024-01-01 + gs/324}}} — advances one day every 324 rows (27 resources x 12 slots)
     67 * {{{hour = 8 + (gs/27 % 12)}}} — 12 x 2-hour slots covering 08:00-20:00
     68
     69'''Status distribution:''' 40% approved, 30% completed, 15% rejected, 10% pending, 5% cancelled.
     70
    971=== Scenario 1: Phase P6 Report 1 — Resource Utilization (quarterly) ===
    1072
    11 '''Query source:''' the full "Report 1: Resource Utilization and Demand Analysis" from [wiki:ComplexReports ComplexReports]. Uses 5 CTEs: quarter_bounds, quarter_days, resource_availability, reservation_stats, popular_day, peak_hour. Window function {{{RANK()}}} for demand ranking. Filters by a quarterly date range.
     73A quarterly report showing each resource's utilization rate, peak usage hours, busiest day, approval rate, and demand ranking. The query uses 5 CTEs (quarter_bounds, quarter_days, resource_availability, reservation_stats, popular_day, peak_hour) and a {{{RANK()}}} window function. The most expensive CTEs are popular_day and peak_hour, which filter reservations by date range and status to find the most-used day and hour per resource.
    1274
    1375'''Proposed index:'''
    … …  
    1779}}}
    1880
    19 '''Before index (excerpt of plan):'''
    20 {{{
    21 HashAggregate  (actual rows=189 loops=1)
    22  ->  Nested Loop (actual rows=10383 loops=1)
    23        ->  Bitmap Heap Scan on reservations rv_1 (actual rows=10383 loops=1)
    24              Recheck Cond: start_time >= q_start AND start_time < q_end
    25              Filter: status IN ('approved','completed')
    26              Rows Removed by Filter: 15537
    27              Heap Blocks: exact=364
    28              ->  Bitmap Index Scan on idx_reservations_times
    29 Execution Time: 181.324 ms
    30 }}}
    31 
    32 '''After index:'''
    33 {{{
    34  ->  Index Only Scan using idx_reservations_start_resource_status
    35        on reservations rv_1
    36        Index Cond: start_time >= q_start AND start_time < q_end
    37        Heap Fetches: 0
    38 Execution Time: 82.307 ms
    39 }}}
    40 
    41 '''Analysis:''' The new composite index includes {{{status}}}, so the planner can filter by status inside the index without touching the heap. The {{{popular_day}}} and {{{peak_hour}}} CTEs both switched from Bitmap Heap Scan with "Rows Removed by Filter: 15537" per iteration to a clean Index Only Scan. Heap Fetches is 0.
    42 
    43 '''Result: 181 ms -> 82 ms (55% faster). Index Only Scan confirmed in the plan.'''
     81'''Before index — full execution plan:'''
     82{{{
     83Sort  (cost=211793.37..212201.69 rows=163328 width=588) (actual time=43.493..43.502 rows=27 loops=1)
     84  Sort Key: utilization_pct DESC
     85  Sort Method: quicksort  Memory: 29kB
     86  Buffers: shared hit=1476
     87  CTE quarter_bounds
     88    ->  Result  (actual time=0.000..0.001 rows=1 loops=1)
     89  ->  WindowAgg  (actual time=43.416..43.468 rows=27 loops=1)
     90        Buffers: shared hit=1473
     91        ->  Sort  (actual time=43.404..43.412 rows=27 loops=1)
     92              Sort Method: quicksort  Memory: 28kB
     93              ->  Hash Left Join  (actual time=42.034..43.393 rows=27 loops=1)
     94                    Hash Cond: (r.resource_id = ph.resource_id)
     95                    ->  Merge Left Join  (actual time=29.501..30.849 rows=27 loops=1)
     96                          Merge Cond: (r.resource_id = rv_1.resource_id)
     97                          ->  Merge Join  (actual time=15.513..16.833 rows=27 loops=1)
     98                                ->  Nested Loop Left Join  (actual time=15.427..15.468 rows=27 loops=1)
     99                                      ->  Merge Left Join  (actual time=15.408..15.423 rows=27 loops=1)
     100                                            ->  [resources + resource_types join] (actual time=0.035..0.038 rows=27 loops=1)
     101                                            ->  reservation_stats CTE
     102                                                  ->  HashAggregate  Group Key: rv.resource_id  (actual rows=27 loops=1)
     103                                                        ->  Nested Loop  (actual time=0.467..6.167 rows=29484 loops=1)
     104                                                              ->  Bitmap Heap Scan on reservations rv
     105                                                                    Recheck Cond: start_time >= q_start AND start_time < q_end
     106                                                                    Heap Blocks: exact=448
     107                                                                    Buffers: shared hit=482
     108                                                                    ->  Bitmap Index Scan on idx_reservations_times
     109                                                                          Index Cond: start_time range
     110                          ->  popular_day CTE
     111                                ->  HashAggregate  (actual rows=189 loops=1)
     112                                      ->  Nested Loop
     113                                            ->  Bitmap Heap Scan on reservations rv_1
     114                                                  Recheck Cond: start_time >= q_start AND start_time < q_end
     115                                                  Filter: status IN ('approved','completed')
     116                                                  Rows Removed by Filter: 8844
     117                                                  Heap Blocks: exact=448
     118                                                  Buffers: shared hit=479
     119                                                  ->  Bitmap Index Scan on idx_reservations_times
     120                    ->  peak_hour CTE
     121                          ->  HashAggregate  (actual rows=324 loops=1)
     122                                ->  Nested Loop
     123                                      ->  Bitmap Heap Scan on reservations rv_2
     124                                            Recheck Cond: start_time >= q_start AND start_time < q_end
     125                                            Filter: status IN ('approved','completed')
     126                                            Rows Removed by Filter: 8844
     127                                            Heap Blocks: exact=448
     128                                            Buffers: shared hit=479
     129                                            ->  Bitmap Index Scan on idx_reservations_times
     130Planning Time: 3.454 ms
     131Execution Time: 44.114 ms
     132}}}
     133
     134'''After index — full execution plan:'''
     135{{{
     136Sort  (cost=206320.34..206729.09 rows=163500 width=588) (actual time=44.819..44.828 rows=27 loops=1)
     137  Sort Key: utilization_pct DESC
     138  Sort Method: quicksort  Memory: 29kB
     139  Buffers: shared hit=670 read=148
     140  CTE quarter_bounds
     141    ->  Result  (actual time=0.000..0.001 rows=1 loops=1)
     142  ->  WindowAgg  (actual time=44.755..44.798 rows=27 loops=1)
     143        ->  Sort  (actual time=44.742..44.750 rows=27 loops=1)
     144              Sort Method: quicksort  Memory: 28kB
     145              ->  Hash Left Join  (actual time=43.284..44.733 rows=27 loops=1)
     146                    Hash Cond: (r.resource_id = ph.resource_id)
     147                    ->  Merge Left Join  (actual time=32.860..34.299 rows=27 loops=1)
     148                          Merge Cond: (r.resource_id = rv_1.resource_id)
     149                          ->  [resource/type/location join] (actual time=17.376..18.786 rows=27 loops=1)
     150                                ->  reservation_stats CTE
     151                                      ->  HashAggregate  Group Key: rv.resource_id  (actual rows=27 loops=1)
     152                                            ->  Bitmap Heap Scan on reservations rv
     153                                                  Recheck Cond: start_time range
     154                                                  Heap Blocks: exact=448
     155                                                  Buffers: shared hit=482
     156                                                  ->  Bitmap Index Scan on idx_reservations_times
     157                          ->  popular_day CTE
     158                                ->  HashAggregate  (actual rows=189 loops=1)
     159                                      ->  Nested Loop
     160                                            ->  Index Only Scan using idx_reservations_start_resource_status
     161                                                  Index Cond: start_time >= q_start AND start_time < q_end
     162                                                  Filter: status IN ('approved','completed')
     163                                                  Rows Removed by Filter: 8844
     164                                                  Heap Fetches: 35
     165                                                  Buffers: shared hit=2 read=148
     166                    ->  peak_hour CTE
     167                          ->  HashAggregate  (actual rows=324 loops=1)
     168                                ->  Nested Loop
     169                                      ->  Index Only Scan using idx_reservations_start_resource_status
     170                                            Index Cond: start_time >= q_start AND start_time < q_end
     171                                            Filter: status IN ('approved','completed')
     172                                            Rows Removed by Filter: 8844
     173                                            Heap Fetches: 35
     174                                            Buffers: shared hit=150
     175Planning Time: 4.308 ms
     176Execution Time: 45.570 ms
     177}}}
     178
     179'''Key differences:'''
     180
     181||'''CTE'''||'''Before'''||'''After'''||
     182||popular_day||Bitmap Heap Scan on reservations + Bitmap Index Scan on idx_reservations_times||'''Index Only Scan using idx_reservations_start_resource_status'''||
     183||peak_hour||Bitmap Heap Scan on reservations + Bitmap Index Scan on idx_reservations_times||'''Index Only Scan using idx_reservations_start_resource_status'''||
     184||Heap access (popular_day)||Heap Blocks: exact=448||'''Heap Fetches: 35'''||
     185||Heap access (peak_hour)||Heap Blocks: exact=448||'''Heap Fetches: 35'''||
     186
     187'''Analysis:''' The popular_day and peak_hour CTEs switched from Bitmap Heap Scan (reading 448 heap blocks each) to Index Only Scan with near-zero heap access (35 fetches). The new composite index includes {{{status}}}, so the planner reads date range and status in one index pass without touching the heap. At 29,484 rows in the quarter the absolute time difference is small; the benefit scales with dataset size and becomes significant when multiple quarters or larger tables are queried.
     188
     189'''Result: 44.1 ms -> 45.6 ms (scan type improvement; time equivalent at this scale).''' '''Index Only Scan confirmed in popular_day and peak_hour CTEs. Heap access reduced from 448 blocks to 35 fetches per CTE.'''
    44190
    45191=== Scenario 2: Phase P6 Report 3 — User Activity and Behavior Analysis ===
    46192
    47 '''Query source:''' the full "Report 3: User Activity and Behavior Analysis" from [wiki:ComplexReports ComplexReports]. Uses 2 CTEs (user_stats, favorite_resource), {{{DISTINCT ON}}}, {{{RANK()}}}, {{{COUNT(*) FILTER (WHERE ...)}}} for per-status counts, and aggregates over the entire reservations table grouped by user_id.
     193A per-user report aggregating all reservations in the system: total counts, approval/cancellation rates, average duration, total hours, most-used resource, and activity ranking. The query scans the entire reservations table with no date filter (200,028 rows), grouped by user_id. The user_stats CTE requires a sort on user_id to group, and the DISTINCT ON in favorite_resource also requires an ordered scan.
    48194
    49195'''Proposed index:'''
    … …  
    54200}}}
    55201
    56 The {{{INCLUDE}}} clause adds start_time and end_time as index payload (not used for ordering) so the query can compute {{{AVG(end_time - start_time)}}} and {{{SUM(...) FILTER (...)}}} without heap access.
    57 
    58 '''Before index:'''
    59 {{{
    60 Sort Method: external merge  Disk: 9032kB
    61  ->  Seq Scan on reservations rv  (actual rows=200026 loops=1)
    62 Execution Time: 279.957 ms
    63 }}}
    64 
    65 '''After index:'''
    66 {{{
    67 Incremental Sort
    68   Presorted Key: user_id
    69   Full-sort Groups: 12  Sort Method: quicksort  Average Memory: 29kB
    70   Pre-sorted Groups: 12  Sort Method: quicksort  Average Memory: 1821kB
    71   ->  Index Only Scan using idx_reservations_user_status_resource
    72         Heap Fetches: 0
    73 Execution Time: 175.602 ms
    74 }}}
    75 
    76 '''Analysis:''' The sequential scan is replaced by an Index Only Scan. Disk-based external merge sort (9032kB to disk) is eliminated; because the index is sorted by user_id, PostgreSQL does an Incremental Sort entirely in memory. Heap Fetches: 0 confirms the INCLUDE columns are doing their job.
    77 
    78 '''Result: 280 ms -> 176 ms (37% faster). Disk sort eliminated; Index Only Scan used.'''
     202The {{{INCLUDE}}} clause adds start_time and end_time as non-key payload so the query can compute {{{AVG(end_time - start_time)}}} and {{{SUM(...) FILTER (...)}}} without heap access.
     203
     204'''Before index — full execution plan:'''
     205{{{
     206Sort  (cost=44161.35..44162.72 rows=550 width=490) (actual time=314.472..314.589 rows=12 loops=1)
     207  Sort Key: activity_rank
     208  Sort Method: quicksort  Memory: 26kB
     209  Buffers: shared hit=6089 read=2, temp read=1129 written=1133
     210  ->  WindowAgg  (actual time=314.439..314.562 rows=12 loops=1)
     211        ->  Sort  (actual time=314.431..314.548 rows=12 loops=1)
     212              Sort Key: total_reservations DESC
     213              ->  Hash Join  (actual time=213.597..314.521 rows=12 loops=1)
     214                    Hash Cond: u.type_id = ut.type_id
     215                    ->  Merge Left Join  (actual time=213.391..314.301 rows=12 loops=1)
     216                          Merge Cond: u.user_id = rv_1.user_id
     217                          ->  Merge Left Join  (actual time=177.462..278.025 rows=12 loops=1)
     218                                Merge Cond: u.user_id = rv.user_id
     219                                ->  Index Scan using users_pkey on users u  (actual rows=12 loops=1)
     220                                ->  user_stats CTE (GroupAggregate)
     221                                      Group Key: rv.user_id
     222                                      ->  Sort  (actual time=165.115..190.881 rows=200028 loops=1)
     223                                            Sort Key: rv.user_id, rv.resource_id
     224                                            Sort Method: external merge  Disk: 9032kB
     225                                            ->  Seq Scan on reservations rv  (actual rows=200028 loops=1)
     226                          ->  favorite_resource CTE
     227                                ->  Finalize GroupAggregate  Group Key: rv_1.user_id, r.name
     228                                      ->  Gather Merge (Workers Launched: 1)
     229                                            ->  Partial HashAggregate
     230                                                  ->  Hash Join  (Hash Cond: rv_1.resource_id = r.resource_id)
     231                                                        ->  Parallel Seq Scan on reservations rv_1
     232                                                              Filter: status IN ('approved','completed')
     233                                                              Rows Removed by Filter: 30004
     234Planning Time: 3.511 ms
     235Execution Time: 316.082 ms
     236}}}
     237
     238'''After index — full execution plan:'''
     239{{{
     240Sort  (cost=41026.98..41028.35 rows=550 width=490) (actual time=199.209..199.270 rows=12 loops=1)
     241  Sort Key: activity_rank
     242  Sort Method: quicksort  Memory: 26kB
     243  Buffers: shared hit=3062 read=1409
     244  ->  WindowAgg  (actual time=199.191..199.258 rows=12 loops=1)
     245        ->  Sort  (actual time=199.182..199.242 rows=12 loops=1)
     246              Sort Key: total_reservations DESC
     247              ->  Hash Join  (actual time=68.425..199.221 rows=12 loops=1)
     248                    Hash Cond: u.type_id = ut.type_id
     249                    ->  Merge Left Join  (actual time=68.393..199.165 rows=12 loops=1)
     250                          Merge Cond: u.user_id = rv_1.user_id
     251                          ->  Merge Left Join  (actual time=24.435..154.881 rows=12 loops=1)
     252                                Merge Cond: u.user_id = rv.user_id
     253                                ->  Index Scan using users_pkey on users u  (actual rows=12 loops=1)
     254                                ->  user_stats CTE (GroupAggregate)
     255                                      Group Key: rv.user_id
     256                                      ->  Incremental Sort  (actual time=6.463..67.437 rows=200028 loops=1)
     257                                            Sort Key: rv.user_id, rv.resource_id
     258                                            Presorted Key: rv.user_id
     259                                            Full-sort Groups: 10  Sort Method: quicksort  Memory: 29kB
     260                                            Pre-sorted Groups: 10  Sort Method: quicksort  Memory: 2019kB
     261                                            ->  Index Only Scan using idx_reservations_user_status_resource
     262                                                  Heap Fetches: 63
     263                                                  Buffers: shared hit=2 read=1409
     264                          ->  favorite_resource CTE
     265                                ->  Finalize GroupAggregate  Group Key: rv_1.user_id, r.name
     266                                      ->  Gather Merge (Workers Launched: 1)
     267                                            ->  Partial HashAggregate
     268                                                  ->  Hash Join  (Hash Cond: rv_1.resource_id = r.resource_id)
     269                                                        ->  Parallel Seq Scan on reservations rv_1
     270                                                              Filter: status IN ('approved','completed')
     271                                                              Rows Removed by Filter: 30004
     272Planning Time: 3.179 ms
     273Execution Time: 199.542 ms
     274}}}
     275
     276'''Key differences:'''
     277
     278||'''Node'''||'''Before'''||'''After'''||
     279||user_stats sort||'''Seq Scan on reservations''' (200,028 rows)||'''Index Only Scan using idx_reservations_user_status_resource'''||
     280||Sort method||'''external merge  Disk: 9032kB'''||'''Incremental Sort, in memory (2019kB peak)'''||
     281||Heap access||Full table read||'''Heap Fetches: 63'''||
     282||Buffer reads||shared hit=6089 read=2||shared hit=3062 read=1409 (index pages)||
     283
     284'''Analysis:''' The sequential scan on the full reservations table is replaced by an Index Only Scan. Because the index is ordered by user_id, the 9032kB disk-based external merge sort becomes an Incremental Sort entirely in memory. The INCLUDE columns (start_time, end_time) allow duration calculations without heap access. The favorite_resource CTE still uses a parallel sequential scan because that CTE joins resources and filters by status in a way that benefits more from parallelism than from the index at this data size.
     285
     286'''Result: 316 ms -> 199 ms (37% faster). Disk sort eliminated (9032kB -> 0kB). Index Only Scan confirmed. Heap Fetches: 63.'''
    79287
    80288=== Scenario 3: Phase P6 Report 4 — Administrator Approval Workload ===
    81289
    82 '''Query source:''' the full "Report 4: Administrator Approval Workload and Bottleneck Analysis" from [wiki:ComplexReports ComplexReports]. Uses 3 CTEs (admin_stats, pending_stats, workload_share), {{{CROSS JOIN}}} to attach the single-row pending_stats to every admin, and {{{SUM(...) OVER ()}}} to compute workload share percentage.
     290A per-administrator workload report: how many reservations each admin reviewed, approval/rejection rates, workload share percentage, and system-wide pending wait times. The admin_stats CTE scans only rows where {{{approved_by IS NOT NULL}}} (approximately 80% of the 200,028 rows), groups them by approved_by, and computes COUNT(DISTINCT resource_id) and COUNT(DISTINCT user_id), which requires sorting before aggregation.
    83291
    84292'''Proposed index:'''
    … …  
    89297}}}
    90298
    91 This is a partial covering index: the {{{WHERE}}} clause excludes rows with no approver (roughly 30% of the data), and the listed columns are everything the {{{admin_stats}}} CTE needs to aggregate.
    92 
    93 '''Before index:'''
    94 {{{
    95 Sort Method: external merge  Disk: 4568kB
    96  ->  Seq Scan on reservations rv_1
    97      Filter: approved_by IS NOT NULL
    98 Execution Time: 166.804 ms
    99 }}}
    100 
    101 '''After index:'''
    102 {{{
    103  ->  Index Only Scan using idx_reservations_approver_status
    104        Heap Fetches: 0
    105 Execution Time: 129.167 ms
    106 }}}
    107 
    108 '''Analysis:''' Sequential scan replaced by Index Only Scan. Disk-based external merge sort (4568kB) eliminated. The partial index is smaller than a full one because it excludes the 60,000 rows with {{{approved_by IS NULL}}}.
    109 
    110 '''Result: 167 ms -> 129 ms (23% faster). Index Only Scan used; partial index avoids indexing irrelevant rows.'''
     299This is a partial covering index: the {{{WHERE}}} clause excludes rows where approved_by is NULL (approximately 20% of rows), and the listed columns are everything the admin_stats CTE needs to aggregate.
     300
     301'''Before index — full execution plan:'''
     302{{{
     303Sort  (cost=29916.58..29916.60 rows=6 width=180) (actual time=155.563..155.566 rows=2 loops=1)
     304  Sort Key: total_reviewed DESC
     305  Sort Method: quicksort  Memory: 25kB
     306  Buffers: shared hit=6084 read=2, temp read=667 written=669
     307  CTE admin_stats
     308    ->  GroupAggregate  (actual time=120.747..141.321 rows=2 loops=1)
     309          Group Key: rv_1.approved_by
     310          Buffers: shared hit=3031, temp read=667 written=669
     311          ->  Sort  (actual time=99.698..116.116 rows=160023 loops=1)
     312                Sort Key: rv_1.approved_by, rv_1.resource_id
     313                Sort Method: external merge  Disk: 5336kB
     314                ->  Seq Scan on reservations rv_1  (actual rows=200028 loops=1)
     315                      Filter: (approved_by IS NOT NULL)
     316                      Rows Removed by Filter: 40005
     317  ->  Nested Loop  (actual time=155.552..155.556 rows=2 loops=1)
     318        ->  Aggregate (pending_stats)  (actual time=13.544..13.545 rows=1 loops=1)
     319              ->  Bitmap Heap Scan on reservations rv
     320                    Recheck Cond: status = 'pending'
     321                    Heap Blocks: exact=3031
     322                    ->  Bitmap Index Scan on idx_reservations_status
     323                          Index Cond: status = 'pending'
     324        ->  Hash Left Join  (actual time=142.000..142.003 rows=2 loops=1)
     325              ->  [users + workload_share join]
     326              ->  Hash on admin_stats
     327Planning Time: 1.899 ms
     328Execution Time: 157.080 ms
     329}}}
     330
     331'''After index — full execution plan:'''
     332{{{
     333Sort  (cost=25377.73..25377.75 rows=6 width=180) (actual time=107.891..107.895 rows=2 loops=1)
     334  Sort Key: total_reviewed DESC
     335  Sort Method: quicksort  Memory: 25kB
     336  Buffers: shared hit=3054 read=145, temp read=668 written=670
     337  CTE admin_stats
     338    ->  GroupAggregate  (actual time=79.159..96.144 rows=2 loops=1)
     339          Group Key: rv_1.approved_by
     340          Buffers: shared hit=2 read=145, temp read=668 written=670
     341          ->  Incremental Sort  (actual time=29.685..71.752 rows=160023 loops=1)
     342                Sort Key: rv_1.approved_by, rv_1.resource_id
     343                Presorted Key: rv_1.approved_by
     344                Full-sort Groups: 2  Sort Method: quicksort  Memory: 28kB
     345                Pre-sorted Groups: 2  Sort Method: external merge  Avg Disk: 2672kB
     346                ->  Index Only Scan using idx_reservations_approver_status
     347                      Heap Fetches: 54
     348                      Buffers: shared hit=2 read=145
     349  ->  Nested Loop  (actual time=107.883..107.888 rows=2 loops=1)
     350        ->  Aggregate (pending_stats)  (actual time=11.656..11.656 rows=1 loops=1)
     351              ->  Bitmap Heap Scan on reservations rv
     352                    Recheck Cond: status = 'pending'
     353                    Heap Blocks: exact=3031
     354                    ->  Bitmap Index Scan on idx_reservations_status
     355                          Index Cond: status = 'pending'
     356        ->  Hash Left Join  (actual time=96.223..96.227 rows=2 loops=1)
     357              ->  [users + workload_share join]
     358              ->  Hash on admin_stats
     359Planning Time: 0.374 ms
     360Execution Time: 109.060 ms
     361}}}
     362
     363'''Key differences:'''
     364
     365||'''Node'''||'''Before'''||'''After'''||
     366||admin_stats scan||'''Seq Scan on reservations''' (200,028 rows read, 40,005 filtered)||'''Index Only Scan using idx_reservations_approver_status''' (160,023 rows, no filter step)||
     367||Sort method||'''external merge  Disk: 5336kB'''||'''Incremental Sort; Per-sorted groups external merge Avg Disk: 2672kB (halved)'''||
     368||Heap access||Full table read||'''Heap Fetches: 54'''||
     369||Rows filtered||Rows Removed by Filter: 40005||'''0 (partial index excludes NULL rows by definition)'''||
     370
     371'''Analysis:''' The sequential scan that read all 200,028 rows and discarded 40,005 NULL-approved_by rows is replaced by an Index Only Scan that only reads the 160,023 indexed rows. The partial index physically excludes NULL rows, so no filter step is needed. The pre-sorted nature of the index reduces the external merge sort from 5336kB to 2672kB. Heap Fetches drop to 54 because status, resource_id, and user_id are in the index and do not require heap lookup.
     372
     373'''Result: 157 ms -> 109 ms (31% faster). Index Only Scan used. Rows-removed-by-filter eliminated. Disk sort halved (5336kB -> 2672kB). Heap Fetches: 54.'''
    111374
    112375=== Performance Summary ===
    113376
    114377||'''Phase P6 Report'''||'''Before'''||'''After'''||'''Gain'''||'''Key change'''||
    115 ||Report 1 — Resource Utilization (quarterly)||181 ms||82 ms||55%||Composite index enables Index Only Scan for CTE aggregation||
    116 ||Report 3 — User Activity Analysis||280 ms||176 ms||37%||INCLUDE covering index removes 9 MB disk sort and heap fetches||
    117 ||Report 4 — Admin Workload Analysis||167 ms||129 ms||23%||Partial covering index replaces seq scan, removes 4.5 MB disk sort||
     378||Report 1 — Resource Utilization (quarterly)||'''44.1 ms'''||'''45.6 ms'''||Scan type improved||'''Bitmap Heap Scan -> Index Only Scan''' in popular_day and peak_hour CTEs; heap blocks reduced from 448 to 35 fetches per CTE||
     379||Report 3 — User Activity Analysis||'''316 ms'''||'''199 ms'''||'''37% faster'''||'''Seq Scan + 9032kB disk sort -> Index Only Scan + in-memory Incremental Sort'''||
     380||Report 4 — Admin Workload Analysis||'''157 ms'''||'''109 ms'''||'''31% faster'''||'''Seq Scan (40k rows filtered) + 5336kB disk sort -> Index Only Scan + 2672kB disk sort'''||
    118381
    119382=== Note on Reports That Do Not Benefit from Indexes ===