Changes between Version 2 and Version 3 of Security&Optimization


Ignore:
Timestamp:
09/22/25 11:59:09 (3 days ago)
Author:
221164
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Security&Optimization

    v2 v3  
    3030'''Selectivity''': High (~0.1% of 200k rows)
    3131
    32 ||= Condition =||= Plan Type =||= Execution Time =||= Buffers (Shared) =||
    33 || Without index || Sequential Scan || 16.235 ms || hit=32, read=1439 ||
    34 || '''With index''' || '''Bitmap Index Scan''' || '''0.297 ms''' || '''hit=204, read=3''' ||
     32'''WITHOUT INDEX:'''
     33{{{
     34Seq Scan on frontstaff_managed_reservations  (cost=0.00..3971.01 rows=260 width=32) (actual time=0.061..16.195 rows=201 loops=1)
     35Filter: (table_number = 1)
     36Rows Removed by Filter: 199800
     37Buffers: shared hit=32 read=1439
     38Planning:
     39Buffers: shared hit=14 read=1
     40Planning Time: 0.524 ms
     41Execution Time: 16.235 ms
     42}}}
     43
     44'''WITH INDEX:'''
     45{{{
     46Bitmap Heap Scan on frontstaff_managed_reservations  (cost=5.87..529.57 rows=187 width=32) (actual time=0.068..0.270 rows=201 loops=1)
     47Recheck Cond: (table_number = 1)
     48Heap Blocks: exact=201
     49Buffers: shared hit=204 read=3
     50->  Bitmap Index Scan on fmr_table_idx  (cost=0.00..5.82 rows=187 width=0) (actual time=0.044..0.044 rows=201 loops=1)
     51Index Cond: (table_number = 1)
     52Buffers: shared hit=3 read=3
     53Planning:
     54Buffers: shared hit=16 read=1
     55Planning Time: 0.176 ms
     56Execution Time: 0.297 ms
     57}}}
    3558
    3659'''Performance Impact''':
    37 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.
     60The index provides a ~54x speedup in execution time (16.235 ms → 0.297 ms). 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.
    3861
    3962'''Conclusion''':
    … …  
    4770'''Selectivity''': Medium (~2.1% of 500k rows)
    4871
    49 ||= Condition =||= Plan Type =||= Execution Time =||= Buffers (Shared) =||
    50 || Without index || Parallel Seq Scan || 141.415 ms || hit=94, read=4641 ||
    51 || '''With index''' || '''Bitmap Index Scan (GiST)''' || '''3.646 ms''' || '''hit=249, read=0''' ||
     72'''WITHOUT INDEX:'''
     73{{{
     74Gather  (cost=1000.00..13985.52 rows=5000 width=8) (actual time=59.230..141.002 rows=10627 loops=1)
     75Workers Planned: 2
     76Workers Launched: 2
     77Buffers: shared hit=94 read=4641
     78->  Parallel Seq Scan on reservations  (cost=0.00..12485.52 rows=2083 width=8) (actual time=55.539..135.006 rows=3542 loops=3)
     79"        Filter: (tsrange(datetime, (datetime + ((COALESCE(stay_length, '2'::numeric))::double precision * '01:00:00'::interval)), '[)'::text) && tsrange((now())::timestamp without time zone, ((now())::timestamp without time zone + '00:30:00'::interval), '[)'::text))"
     80Rows Removed by Filter: 163125
     81Buffers: shared hit=94 read=4641
     82Planning:
     83Buffers: shared hit=5
     84Planning Time: 0.111 ms
     85Execution Time: 141.415 ms
     86}}}
     87
     88'''WITH INDEX:'''
     89{{{
     90Bitmap Heap Scan on reservations  (cost=480.45..5507.06 rows=9310 width=8) (actual time=2.161..3.291 rows=10515 loops=1)
     91"  Recheck Cond: (tsrange(datetime, (datetime + ((COALESCE(stay_length, '2'::numeric))::double precision * '01:00:00'::interval)), '[)'::text) && tsrange((now())::timestamp without time zone, ((now())::timestamp without time zone + '00:30:00'::interval), '[)'::text))"
     92Heap Blocks: exact=167
     93Buffers: shared hit=249
     94->  Bitmap Index Scan on reservations_span_expr_gist  (cost=0.00..478.12 rows=9310 width=0) (actual time=2.117..2.117 rows=10515 loops=1)
     95"        Index Cond: (tsrange(datetime, (datetime + ((COALESCE(stay_length, '2'::numeric))::double precision * '01:00:00'::interval)), '[)'::text) && tsrange((now())::timestamp without time zone, ((now())::timestamp without time zone + '00:30:00'::interval), '[)'::text))"
     96Buffers: shared hit=82
     97Planning Time: 0.101 ms
     98Execution Time: 3.646 ms
     99}}}
    52100
    53101'''Performance Impact''':
    54 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.
     102The GiST index is purpose-built for this type of query and delivers a ~39x speedup (141.415 ms → 3.646 ms). It completely eliminates disk reads for this query by efficiently handling the range overlap && operator.
    55103
    56104'''Conclusion''':
    … …  
    64112'''Selectivity''': Extremely high (finding a few payments among ~700k rows)
    65113
    66 ||= Query Type =||= Condition =||= Plan Type =||= Execution Time =||
    67 || COUNT(*) || Without index || Parallel Seq Scan || 34.445 ms ||
    68 || COUNT(*) || '''With index''' || '''Index Only Scan''' || '''0.120 ms''' ||
    69 || JOIN || Without index || Parallel Seq Scan || 33.313 ms ||
    70 || JOIN || '''With index''' || '''Bitmap Index Scan''' || '''0.075 ms''' ||
     114'''WITHOUT INDEX (COUNT Query):'''
     115{{{
     116Finalize Aggregate  (cost=14543.27..14543.28 rows=1 width=8) (actual time=32.037..34.410 rows=1 loops=1)
     117Buffers: shared hit=3849 read=4489
     118InitPlan 2 (returns $1)
     119->  Result  (cost=0.65..0.66 rows=1 width=8) (actual time=0.016..0.018 rows=1 loops=1)
     120Buffers: shared hit=4
     121InitPlan 1 (returns $0)
     122->  Limit  (cost=0.41..0.65 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1)
     123Buffers: shared hit=4
     124->  Index Only Scan using orders_pkey on orders  (cost=0.41..4750.47 rows=20003 width=8) (actual time=0.012..0.013 rows=1 loops=1)
     125Index Cond: (id IS NOT NULL)
     126Heap Fetches: 0
     127Buffers: shared hit=4
     128->  Gather  (cost=14542.39..14542.60 rows=2 width=8) (actual time=31.865..34.400 rows=3 loops=1)
     129Workers Planned: 2
     130Params Evaluated: $1
     131Workers Launched: 2
     132Buffers: shared hit=3849 read=4489
     133->  Partial Aggregate  (cost=13542.39..13542.40 rows=1 width=8) (actual time=27.452..27.453 rows=1 loops=3)
     134Buffers: shared hit=3845 read=4489
     135->  Parallel Seq Scan on payments  (cost=0.00..13542.34 rows=21 width=0) (actual time=27.448..27.448 rows=0 loops=3)
     136Filter: (order_id = $1)
     137Rows Removed by Filter: 333334
     138Buffers: shared hit=3845 read=4489
     139Planning:
     140Buffers: shared hit=5
     141Planning Time: 0.166 ms
     142Execution Time: 34.445 ms
     143}}}
     144
     145'''WITH INDEX (COUNT Query):'''
     146{{{
     147Aggregate  (cost=6.08..6.09 rows=1 width=8) (actual time=0.095..0.096 rows=1 loops=1)
     148Buffers: shared hit=4 read=3
     149InitPlan 2 (returns $1)
     150->  Result  (cost=0.65..0.66 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)
     151Buffers: shared hit=4
     152InitPlan 1 (returns $0)
     153->  Limit  (cost=0.41..0.65 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=1)
     154Buffers: shared hit=4
     155->  Index Only Scan using orders_pkey on orders  (cost=0.41..4750.47 rows=20003 width=8) (actual time=0.009..0.009 rows=1 loops=1)
     156Index Cond: (id IS NOT NULL)
     157Heap Fetches: 0
     158Buffers: shared hit=4
     159->  Index Only Scan using idx_payments_order on payments  (cost=0.42..5.30 rows=50 width=0) (actual time=0.092..0.092 rows=0 loops=1)
     160Index Cond: (order_id = $1)
     161Heap Fetches: 0
     162Buffers: shared hit=4 read=3
     163Planning:
     164Buffers: shared hit=5 read=1
     165Planning Time: 0.258 ms
     166Execution Time: 0.120 ms
     167}}}
     168
     169'''WITHOUT INDEX (JOIN Query):'''
     170{{{
     171Nested Loop  (cost=1001.07..14552.93 rows=50 width=21) (actual time=31.125..33.280 rows=0 loops=1)
     172Buffers: shared hit=3949 read=4393
     173InitPlan 2 (returns $1)
     174->  Result  (cost=0.65..0.66 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=1)
     175Buffers: shared hit=4
     176InitPlan 1 (returns $0)
     177->  Limit  (cost=0.41..0.65 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)
     178Buffers: shared hit=4
     179->  Index Only Scan using orders_pkey on orders  (cost=0.41..4750.47 rows=20003 width=8) (actual time=0.009..0.010 rows=1 loops=1)
     180Index Cond: (id IS NOT NULL)
     181Heap Fetches: 0
     182Buffers: shared hit=4
     183->  Index Only Scan using orders_pkey on orders o  (cost=0.41..4.43 rows=1 width=8) (actual time=0.020..0.025 rows=1 loops=1)
     184Index Cond: (id = $1)
     185Heap Fetches: 0
     186Buffers: shared hit=8
     187->  Gather  (cost=1000.00..14547.34 rows=50 width=29) (actual time=31.097..33.248 rows=0 loops=1)
     188Workers Planned: 2
     189Params Evaluated: $1
     190Workers Launched: 2
     191Buffers: shared hit=3941 read=4393
     192->  Parallel Seq Scan on payments p  (cost=0.00..13542.34 rows=21 width=29) (actual time=25.975..25.976 rows=0 loops=3)
     193Filter: (order_id = $1)
     194Rows Removed by Filter: 333334
     195Buffers: shared hit=3941 read=4393
     196Planning:
     197Buffers: shared hit=14
     198Planning Time: 0.225 ms
     199Execution Time: 33.313 ms
     200}}}
     201
     202'''WITH INDEX (JOIN Query):'''
     203{{{
     204Nested Loop  (cost=5.88..199.41 rows=50 width=21) (actual time=0.033..0.034 rows=0 loops=1)
     205Buffers: shared hit=11
     206InitPlan 2 (returns $1)
     207->  Result  (cost=0.65..0.66 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)
     208Buffers: shared hit=4
     209InitPlan 1 (returns $0)
     210->  Limit  (cost=0.41..0.65 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)
     211Buffers: shared hit=4
     212->  Index Only Scan using orders_pkey on orders  (cost=0.41..4750.47 rows=20003 width=8) (actual time=0.011..0.012 rows=1 loops=1)
     213Index Cond: (id IS NOT NULL)
     214Heap Fetches: 0
     215Buffers: shared hit=4
     216->  Index Only Scan using orders_pkey on orders o  (cost=0.41..4.43 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=1)
     217Index Cond: (id = $1)
     218Heap Fetches: 0
     219Buffers: shared hit=8
     220->  Bitmap Heap Scan on payments p  (cost=4.81..193.82 rows=50 width=29) (actual time=0.008..0.008 rows=0 loops=1)
     221Recheck Cond: (order_id = $1)
     222Buffers: shared hit=3
     223->  Bitmap Index Scan on idx_payments_order  (cost=0.00..4.80 rows=50 width=0) (actual time=0.006..0.006 rows=0 loops=1)
     224Index Cond: (order_id = $1)
     225Buffers: shared hit=3
     226Planning:
     227Buffers: shared hit=16 dirtied=5
     228Planning Time: 0.258 ms
     229Execution Time: 0.075 ms
     230}}}
    71231
    72232'''Performance Impact''':
    … …  
    81241'''Queries Analyzed''': COUNT(*) on payments over a broad (90-day) and a narrow (1-minute) time window.
    82242
    83 ||= Time Window =||= Condition =||= Plan Type =||= Execution Time =||
    84 || 90 Days (Low Selectivity) || Without index || Parallel Seq Scan || 234.384 ms ||
    85 || 90 Days (Low Selectivity) || With index || Parallel Seq Scan || 239.074 ms ||
    86 || 1 Minute (High Selectivity) || Without index || Parallel Seq Scan || 119.739 ms ||
    87 || 1 Minute (High Selectivity) || '''With index''' || '''Index Only Scan''' || '''0.069 ms''' ||
     243'''WITHOUT INDEX (90-day window):'''
     244{{{
     245Finalize Aggregate  (cost=34333.68..34333.69 rows=1 width=8) (actual time=231.944..234.358 rows=1 loops=1)
     246Buffers: shared hit=7491 read=9176
     247->  Gather  (cost=34333.47..34333.68 rows=2 width=8) (actual time=231.831..234.350 rows=3 loops=1)
     248Workers Planned: 2
     249Workers Launched: 2
     250Buffers: shared hit=7491 read=9176
     251->  Partial Aggregate  (cost=33333.47..33333.48 rows=1 width=8) (actual time=221.112..221.113 rows=1 loops=3)
     252Buffers: shared hit=7491 read=9176
     253->  Parallel Seq Scan on payments  (cost=0.00..31250.34 rows=833250 width=0) (actual time=0.033..185.312 rows=666667 loops=3)
     254Filter: (created_at >= (now() - '90 days'::interval))
     255Buffers: shared hit=7491 read=9176
     256Planning:
     257Buffers: shared hit=7
     258Planning Time: 0.117 ms
     259Execution Time: 234.384 ms
     260}}}
     261
     262'''WITH INDEX (90-day window):'''
     263{{{
     264Finalize Aggregate  (cost=34333.89..34333.90 rows=1 width=8) (actual time=236.606..239.043 rows=1 loops=1)
     265Buffers: shared hit=7907 read=8760
     266->  Gather  (cost=34333.68..34333.89 rows=2 width=8) (actual time=236.487..239.034 rows=3 loops=1)
     267Workers Planned: 2
     268Workers Launched: 2
     269Buffers: shared hit=7907 read=8760
     270->  Partial Aggregate  (cost=33333.68..33333.69 rows=1 width=8) (actual time=232.049..232.050 rows=1 loops=3)
     271Buffers: shared hit=7907 read=8760
     272->  Parallel Seq Scan on payments  (cost=0.00..31250.34 rows=833334 width=0) (actual time=0.038..194.022 rows=666667 loops=3)
     273Filter: (created_at >= (now() - '90 days'::interval))
     274Buffers: shared hit=7907 read=8760
     275Planning:
     276Buffers: shared hit=7
     277Planning Time: 0.194 ms
     278Execution Time: 239.074 ms
     279}}}
     280
     281'''WITHOUT INDEX (1-minute window):'''
     282{{{
     283Finalize Aggregate  (cost=32250.76..32250.77 rows=1 width=8) (actual time=116.163..119.672 rows=1 loops=1)
     284Buffers: shared hit=7587 read=9080
     285->  Gather  (cost=32250.55..32250.76 rows=2 width=8) (actual time=115.972..119.662 rows=3 loops=1)
     286Workers Planned: 2
     287Workers Launched: 2
     288Buffers: shared hit=7587 read=9080
     289->  Partial Aggregate  (cost=31250.55..31250.56 rows=1 width=8) (actual time=111.887..111.889 rows=1 loops=3)
     290Buffers: shared hit=7587 read=9080
     291->  Parallel Seq Scan on payments  (cost=0.00..31250.34 rows=83 width=0) (actual time=111.883..111.884 rows=0 loops=3)
     292Filter: (created_at >= (now() - '00:01:00'::interval))
     293Rows Removed by Filter: 666667
     294Buffers: shared hit=7587 read=9080
     295Planning Time: 0.086 ms
     296Execution Time: 119.739 ms
     297}}}
     298
     299'''WITH INDEX (1-minute window):'''
     300{{{
     301Aggregate  (cost=4.45..4.46 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=1)
     302Buffers: shared hit=3
     303->  Index Only Scan using idx_payments_created_at on payments  (cost=0.43..4.45 rows=1 width=0) (actual time=0.021..0.021 rows=0 loops=1)
     304Index Cond: (created_at >= (now() - '00:01:00'::interval))
     305Heap Fetches: 0
     306Buffers: shared hit=3
     307Planning:
     308Buffers: shared hit=2 read=2
     309Planning Time: 0.288 ms
     310Execution Time: 0.069 ms
     311}}}
    88312
    89313'''Performance Impact''':
    … …  
    92316Broad 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.
    93317
    94 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.
     318Narrow 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 (119.739 ms → 0.069 ms) and using an extremely fast Index Only Scan.
    95319
    96320'''Conclusion''':
    97321This 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.
     322
    98323
    99324== Triggered Business Rules ==