Changes between Version 77 and Version 78 of QueryOptimization


Ignore:
Timestamp:
06/30/26 00:43:13 (6 days ago)
Author:
231027
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v77 v78  
    644644||Planning Time: 0.107 ms||
    645645||Execution Time: 0.119 ms||
     646
     647
     648
     649
     650
     651
     652
     653== Анализа и оптимизација на `Available_Tickets`
     654
     655Овој поглед овозможува моментален увид во инвентарот на достапни слободни седишта за активните настани. Во него е имплементирана комплексна логика која ја калкулира крајната продажна цена во реално време, земајќи го предвид актуелниот процент на попуст за тековниот временски период во кој се наоѓа купувачот.
     656
     657{{{
     658
     659CREATE OR REPLACE VIEW "Available_Tickets" AS
     660SELECT
     661    t.ticket_id,
     662    ROUND(
     663        t.base_price * (1 - COALESCE(ep.price_discount_percent, 0) / 100.0),
     664        2
     665    ) AS price,
     666    e.event_id,
     667    e.name AS event_name,
     668    eh.event_happening_id,
     669    eh.event_time,
     670    v.name AS venue_name,
     671    s.name AS section_name,
     672    st.row_number,
     673    st.seat_number
     674FROM "Ticket" t
     675JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
     676JOIN "Event" e ON eh.event_id = e.event_id
     677JOIN "Venue" v ON eh.venue_id = v.venue_id
     678JOIN "Seat" st ON t.seat_id = st.seat_id
     679JOIN "Section" s ON st.section_id = s.section_id
     680LEFT JOIN "Event_Period" ep ON eh.event_happening_id = ep.event_happening_id
     681                           AND CURRENT_DATE BETWEEN ep.start_date AND ep.end_date
     682WHERE t.is_available = TRUE;
     683
     684}}}
     685
     686==== 1. Примарен филтер:
     687
     688Погледот се потпира на два филтера: `event_happening_id` (за специфичниот настани) и `is_available` (за статусот на билетот).
     689
     690==== 2. Случај на употреба:
     691
     692Ова е клучниот поглед што се користи во моментот кога корисникот сака да купи билет. Брзината овде директно го намалува ризикот од "Race Condition" - односно, двајца корисници да се обидат да го резервираат истото место истовремено поради доцнење на податоците.
     693
     694==== 3. Иницијално време:
     695
     696 * '''SELECT:''' 79.398 ms (Релативно бавно поради '''Bitmap Heap Scan''' и '''Filter''' операција врз табела со илјадници записи).
     697 * '''INSERT:''' 18.031 ms (Високо време поради проверките на констреинтите при вметнување нови билети).
     698 * '''UPDATE:''' 0.125 ms (Инстантна брзина преку '''primary key''').
     699
     700==== 4. Анализа на планот на извршување (без индекси):
     701
     702Без композитен индекс, базата користеше '''Bitmap Heap Scan''' за пребарување на билетите. Иако го пронаоѓаше настанот, таа мораше да прави дополнителна проверка во меморијата ('''Filter: `is_available`''') и да отфрла огромен број записи ('''Rows Removed by Filter: 374'''). Ова трошеше 754 читања од диск.
     703
     704 * '''SELECT'''
     705
     706{{{
     707
     708EXPLAIN ANALYZE
     709SELECT * FROM "Available_Tickets"
     710WHERE event_happening_id = 1;
     711
     712}}}
     713
     714||= QUERY PLAN =||
     715||Nested Loop  (cost\=2838.67..2934.30 rows\=357 width\=125) (actual time\=77.881..79.398 rows\=360.00 loops\=1)||
     716||  Buffers: shared hit\=36 read\=754 dirtied\=1||
     717||  ->  Nested Loop Left Join  (cost\=5.31..45.04 rows\=1 width\=79) (actual time\=3.244..3.250 rows\=1.00 loops\=1)||
     718||        Buffers: shared hit\=8 read\=5||
     719||        ->  Nested Loop  (cost\=0.86..24.91 rows\=1 width\=75) (actual time\=2.758..2.763 rows\=1.00 loops\=1)||
     720||              Buffers: shared hit\=8 read\=1||
     721||              ->  Nested Loop  (cost\=0.57..16.61 rows\=1 width\=63) (actual time\=0.020..0.024 rows\=1.00 loops\=1)||
     722||                    Buffers: shared hit\=6||
     723||                    ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening"" eh  (cost\=0.29..8.30 rows\=1 width\=32) (actual time\=0.013..0.016 rows\=1.00 loops\=1)||
     724||                          Index Cond: (event_happening_id \= 1)||
     725||                          Index Searches: 1||
     726||                          Buffers: shared hit\=3||
     727||                    ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.005..0.005 rows\=1.00 loops\=1)||
     728||                          Index Cond: (event_id \= eh.event_id)||
     729||                          Index Searches: 1||
     730||                          Buffers: shared hit\=3||
     731||              ->  Index Scan using ""Venue_pkey"" on ""Venue"" v  (cost\=0.29..8.30 rows\=1 width\=28) (actual time\=2.734..2.734 rows\=1.00 loops\=1)||
     732||                    Index Cond: (venue_id \= eh.venue_id)||
     733||                    Index Searches: 1||
     734||                    Buffers: shared hit\=2 read\=1||
     735||        ->  Bitmap Heap Scan on ""Event_Period"" ep  (cost\=4.45..20.12 rows\=1 width\=12) (actual time\=0.481..0.481 rows\=0.00 loops\=1)||
     736||              Recheck Cond: (event_happening_id \= 1)||
     737||              Filter: ((CURRENT_DATE >\= start_date) AND (CURRENT_DATE <\= end_date))||
     738||              Rows Removed by Filter: 4||
     739||              Heap Blocks: exact\=1||
     740||              Buffers: shared read\=4||
     741||              ->  Bitmap Index Scan on uq_period_happening_name  (cost\=0.00..4.45 rows\=4 width\=0) (actual time\=0.088..0.088 rows\=4.00 loops\=1)||
     742||                    Index Cond: (event_happening_id \= 1)||
     743||                    Index Searches: 1||
     744||                    Buffers: shared read\=3||
     745||  ->  Merge Join  (cost\=2833.37..2881.23 rows\=357 width\=31) (actual time\=74.625..75.812 rows\=360.00 loops\=1)||
     746||        Merge Cond: (st.seat_id \= t.seat_id)||
     747||        Buffers: shared hit\=28 read\=749 dirtied\=1||
     748||        ->  Nested Loop  (cost\=0.74..1196963.53 rows\=20753208 width\=18) (actual time\=0.227..1.135 rows\=734.00 loops\=1)||
     749||              Buffers: shared hit\=23 read\=2||
     750||              ->  Index Scan using ""Seat_pkey"" on ""Seat"" st  (cost\=0.44..671104.56 rows\=20753208 width\=24) (actual time\=0.024..0.343 rows\=734.00 loops\=1)||
     751||                    Index Searches: 1||
     752||                    Buffers: shared hit\=8 read\=2||
     753||              ->  Memoize  (cost\=0.30..0.32 rows\=1 width\=10) (actual time\=0.001..0.001 rows\=1.00 loops\=734)||
     754||                    Cache Key: st.section_id||
     755||                    Cache Mode: logical||
     756||                    Hits: 729  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB||
     757||                    Buffers: shared hit\=15||
     758||                    ->  Index Scan using ""Section_pkey"" on ""Section"" s  (cost\=0.29..0.31 rows\=1 width\=10) (actual time\=0.009..0.009 rows\=1.00 loops\=5)||
     759||                          Index Cond: (section_id \= st.section_id)||
     760||                          Index Searches: 5||
     761||                          Buffers: shared hit\=15||
     762||        ->  Sort  (cost\=2830.84..2831.74 rows\=357 width\=29) (actual time\=74.391..74.441 rows\=360.00 loops\=1)||
     763||              Sort Key: t.seat_id||
     764||              Sort Method: quicksort  Memory: 41kB||
     765||              Buffers: shared hit\=5 read\=747 dirtied\=1||
     766||              ->  Bitmap Heap Scan on ""Ticket"" t  (cost\=22.15..2815.71 rows\=357 width\=29) (actual time\=1.866..73.806 rows\=360.00 loops\=1)||
     767||                    Recheck Cond: (event_happening_id \= 1)||
     768||                    Filter: is_available||
     769||                    Rows Removed by Filter: 374||
     770||                    Heap Blocks: exact\=744||
     771||                    Buffers: shared hit\=5 read\=747 dirtied\=1||
     772||                    ->  Bitmap Index Scan on uq_ticket_happening_seat  (cost\=0.00..22.06 rows\=733 width\=0) (actual time\=1.599..1.599 rows\=744.00 loops\=1)||
     773||                          Index Cond: (event_happening_id \= 1)||
     774||                          Index Searches: 1||
     775||                          Buffers: shared hit\=4 read\=4||
     776||Planning:||
     777||  Buffers: shared hit\=59 read\=8||
     778||Planning Time: 1.571 ms||
     779||Execution Time: 79.504 ms||
     780
     781 * '''INSERT'''
     782
     783{{{
     784
     785EXPLAIN ANALYZE
     786INSERT INTO "Ticket" (ticket_id, event_happening_id, seat_id, base_price, is_available)
     787VALUES (99999999, 1, 99999, 1500.00, TRUE);
     788
     789}}}
     790
     791||= QUERY PLAN =||
     792||Insert on ""Ticket""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=16.622..16.622 rows\=0.00 loops\=1)||
     793||  Buffers: shared hit\=8 read\=3 dirtied\=2||
     794||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=41) (actual time\=0.001..0.002 rows\=1.00 loops\=1)||
     795||Planning Time: 0.028 ms||
     796||Trigger for constraint fk_ticket_seat: time\=1.188 calls\=1||
     797||Trigger for constraint fk_ticket_happening: time\=0.202 calls\=1||
     798||Execution Time: 18.031 ms||
     799
     800 * '''UPDATE'''
     801
     802{{{
     803
     804EXPLAIN ANALYZE
     805UPDATE "Ticket"
     806SET is_available = FALSE
     807WHERE ticket_id = 99999999;
     808
     809}}}
     810
     811||= QUERY PLAN =||
     812||Update on ""Ticket""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.086..0.087 rows\=0.00 loops\=1)||
     813||  Buffers: shared hit\=7||
     814||  ->  Index Scan using ""Ticket_pkey"" on ""Ticket""  (cost\=0.44..8.46 rows\=1 width\=7) (actual time\=0.036..0.038 rows\=1.00 loops\=1)||
     815||        Index Cond: (ticket_id \= 99999999)||
     816||        Index Searches: 1||
     817||        Buffers: shared hit\=4||
     818||Planning Time: 0.254 ms||
     819||Execution Time: 0.125 ms||
     820
     821==== 5. Оптимизација и индексирање:
     822
     823За да се елиминира потребата од скенирање на сите билети (вклучително и оние што веќе се продадени), го имплементиравме овој индекс:
     824
     825{{{
     826
     827CREATE INDEX CONCURRENTLY idx_ticket_available_happening
     828ON "Ticket" (event_happening_id)
     829WHERE is_available = TRUE;
     830
     831
     832ANALYZE "Ticket";
     833
     834}}}
     835
     836==== 6. Резултат по оптимизација:
     837
     838Со индексот, времето на извршување драматично се подобри:
     839
     840 * Времето за '''SELECT''' падна на 1.800 ms, што е 44 пати побрзо. Базата сега користи директна патека до податоците и нема „филтрирање“ на отфрлени редици.
     841 * Времето за '''INSERT''' падна на 0.628 ms (околу 28 пати побрзо), бидејќи констреинтите сега побрзо ги потврдуваат постојните записи.
     842
     843 * '''SELECT'''
     844
     845{{{
     846
     847EXPLAIN ANALYZE
     848SELECT * FROM "Available_Tickets"
     849WHERE event_happening_id = 2;
     850
     851}}}
     852
     853||= QUERY PLAN =||
     854||Nested Loop  (cost\=1436.93..1541.30 rows\=363 width\=125) (actual time\=0.899..1.800 rows\=360.00 loops\=1)||
     855||  Buffers: shared hit\=402||
     856||  ->  Nested Loop Left Join  (cost\=5.31..45.04 rows\=1 width\=79) (actual time\=0.078..0.081 rows\=1.00 loops\=1)||
     857||        Buffers: shared hit\=13||
     858||        ->  Nested Loop  (cost\=0.86..24.91 rows\=1 width\=75) (actual time\=0.031..0.034 rows\=1.00 loops\=1)||
     859||              Buffers: shared hit\=9||
     860||              ->  Nested Loop  (cost\=0.57..16.61 rows\=1 width\=63) (actual time\=0.025..0.027 rows\=1.00 loops\=1)||
     861||                    Buffers: shared hit\=6||
     862||                    ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening"" eh  (cost\=0.29..8.30 rows\=1 width\=32) (actual time\=0.016..0.017 rows\=1.00 loops\=1)||
     863||                          Index Cond: (event_happening_id \= 2)||
     864||                          Index Searches: 1||
     865||                          Buffers: shared hit\=3||
     866||                    ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.005..0.005 rows\=1.00 loops\=1)||
     867||                          Index Cond: (event_id \= eh.event_id)||
     868||                          Index Searches: 1||
     869||                          Buffers: shared hit\=3||
     870||              ->  Index Scan using ""Venue_pkey"" on ""Venue"" v  (cost\=0.29..8.30 rows\=1 width\=28) (actual time\=0.005..0.005 rows\=1.00 loops\=1)||
     871||                    Index Cond: (venue_id \= eh.venue_id)||
     872||                    Index Searches: 1||
     873||                    Buffers: shared hit\=3||
     874||        ->  Bitmap Heap Scan on ""Event_Period"" ep  (cost\=4.45..20.12 rows\=1 width\=12) (actual time\=0.043..0.043 rows\=0.00 loops\=1)||
     875||              Recheck Cond: (event_happening_id \= 2)||
     876||              Filter: ((CURRENT_DATE >\= start_date) AND (CURRENT_DATE <\= end_date))||
     877||              Rows Removed by Filter: 4||
     878||              Heap Blocks: exact\=1||
     879||              Buffers: shared hit\=4||
     880||              ->  Bitmap Index Scan on uq_period_happening_name  (cost\=0.00..4.45 rows\=4 width\=0) (actual time\=0.008..0.008 rows\=4.00 loops\=1)||
     881||                    Index Cond: (event_happening_id \= 2)||
     882||                    Index Searches: 1||
     883||                    Buffers: shared hit\=3||
     884||  ->  Merge Join  (cost\=1431.62..1488.09 rows\=363 width\=31) (actual time\=0.815..1.498 rows\=360.00 loops\=1)||
     885||        Merge Cond: (st.seat_id \= t.seat_id)||
     886||        Buffers: shared hit\=389||
     887||        ->  Nested Loop  (cost\=0.74..1197017.90 rows\=20753360 width\=18) (actual time\=0.177..0.692 rows\=735.00 loops\=1)||
     888||              Buffers: shared hit\=25||
     889||              ->  Index Scan using ""Seat_pkey"" on ""Seat"" st  (cost\=0.44..671106.84 rows\=20753360 width\=24) (actual time\=0.009..0.188 rows\=735.00 loops\=1)||
     890||                    Index Searches: 1||
     891||                    Buffers: shared hit\=10||
     892||              ->  Memoize  (cost\=0.30..0.32 rows\=1 width\=10) (actual time\=0.000..0.000 rows\=1.00 loops\=735)||
     893||                    Cache Key: st.section_id||
     894||                    Cache Mode: logical||
     895||                    Hits: 730  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB||
     896||                    Buffers: shared hit\=15||
     897||                    ->  Index Scan using ""Section_pkey"" on ""Section"" s  (cost\=0.29..0.31 rows\=1 width\=10) (actual time\=0.003..0.004 rows\=1.00 loops\=5)||
     898||                          Index Cond: (section_id \= st.section_id)||
     899||                          Index Searches: 5||
     900||                          Buffers: shared hit\=15||
     901||        ->  Sort  (cost\=1429.07..1429.98 rows\=363 width\=29) (actual time\=0.629..0.658 rows\=360.00 loops\=1)||
     902||              Sort Key: t.seat_id||
     903||              Sort Method: quicksort  Memory: 41kB||
     904||              Buffers: shared hit\=364||
     905||              ->  Bitmap Heap Scan on ""Ticket"" t  (cost\=7.25..1413.64 rows\=363 width\=29) (actual time\=0.088..0.525 rows\=360.00 loops\=1)||
     906||                    Recheck Cond: ((event_happening_id \= 2) AND is_available)||
     907||                    Heap Blocks: exact\=361||
     908||                    Buffers: shared hit\=364||
     909||                    ->  Bitmap Index Scan on idx_ticket_available_happening  (cost\=0.00..7.16 rows\=363 width\=0) (actual time\=0.038..0.038 rows\=361.00 loops\=1)||
     910||                          Index Cond: (event_happening_id \= 2)||
     911||                          Index Searches: 1||
     912||                          Buffers: shared hit\=3||
     913||Planning:||
     914||  Buffers: shared hit\=100 read\=2||
     915||Planning Time: 8.763 ms||
     916||Execution Time: 1.992 ms||
     917
     918 * '''INSERT'''
     919
     920{{{
     921
     922EXPLAIN ANALYZE
     923INSERT INTO "Ticket" (ticket_id, event_happening_id, seat_id, base_price, is_available)
     924VALUES (88888888, 2, 77777, 1800.00, TRUE);
     925
     926}}}
     927
     928||= QUERY PLAN =||
     929||Insert on ""Ticket""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.304..0.304 rows\=0.00 loops\=1)||
     930||  Buffers: shared hit\=15 dirtied\=2||
     931||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=41) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||
     932||Planning Time: 0.029 ms||
     933||Trigger for constraint fk_ticket_seat: time\=0.201 calls\=1||
     934||Trigger for constraint fk_ticket_happening: time\=0.107 calls\=1||
     935||Execution Time: 0.628 ms||
     936
     937 * '''UPDATE'''
     938
     939{{{
     940
     941EXPLAIN ANALYZE
     942UPDATE "Ticket"
     943SET is_available = FALSE
     944WHERE ticket_id = 88888888;
     945
     946}}}
     947
     948||= QUERY PLAN =||
     949||Update on ""Ticket""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.098..0.098 rows\=0.00 loops\=1)||
     950||  Buffers: shared hit\=14||
     951||  ->  Index Scan using ""Ticket_pkey"" on ""Ticket""  (cost\=0.44..8.46 rows\=1 width\=7) (actual time\=0.026..0.028 rows\=1.00 loops\=1)||
     952||        Index Cond: (ticket_id \= 88888888)||
     953||        Index Searches: 1||
     954||        Buffers: shared hit\=4||
     955||Planning Time: 0.125 ms||
     956||Execution Time: 0.155 ms||