Changes between Version 94 and Version 95 of QueryOptimization


Ignore:
Timestamp:
06/30/26 23:54:57 (5 days ago)
Author:
231027
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v94 v95  
    613613== Анализа и оптимизација на `Available_Tickets`
    614614
    615 Овој поглед овозможува моментален увид во инвентарот на достапни слободни седишта за активните настани. Во него е имплементирана комплексна логика која ја калкулира крајната продажна цена во реално време, земајќи го предвид актуелниот процент на попуст за тековниот временски период во кој се наоѓа купувачот.
     615Овој поглед ја нуди моменталната „понуда“ на слободни седишта. Имплементира бизнис логика за динамично ценообразовање: во реално време ја пресметува крајната цена на билетот, применувајќи го соодветниот попуст (`price_discount_percent`) врз база на тековниот временски период (`Event_Period`). Со ова, корисникот секогаш добива ажурирана цена при резервација.
    616616
    617617{{{
    618618
    619619CREATE OR REPLACE VIEW "Available_Tickets" AS
    620 SELECT
    621     t.ticket_id,
    622     ROUND(
    623         t.base_price * (1 - COALESCE(ep.price_discount_percent, 0) / 100.0),
    624         2
    625     ) AS price,
    626     e.event_id,
    627     e.name AS event_name,
    628     eh.event_happening_id,
    629     eh.event_time,
    630     v.name AS venue_name,
    631     s.name AS section_name,
    632     st.row_number,
    633     st.seat_number
     620SELECT t.ticket_id, ROUND(t.base_price * (1 - COALESCE(ep.price_discount_percent, 0) / 100.0), 2) AS price,
     621       e.event_id, e.name AS event_name, eh.event_happening_id, eh.event_time, v.name AS venue_name,
     622       s.name AS section_name, st.row_number, st.seat_number
    634623FROM "Ticket" t
    635 JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
    636 JOIN "Event" e ON eh.event_id = e.event_id
    637 JOIN "Venue" v ON eh.venue_id = v.venue_id
    638 JOIN "Seat" st ON t.seat_id = st.seat_id
    639 JOIN "Section" s ON st.section_id = s.section_id
    640 LEFT JOIN "Event_Period" ep ON eh.event_happening_id = ep.event_happening_id
    641                            AND CURRENT_DATE BETWEEN ep.start_date AND ep.end_date
    642 WHERE t.is_available = TRUE;
    643 
    644 }}}
    645 
    646 Погледот е веќе најоптимално напишан и не треба да се преуредува.
     624    JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
     625    JOIN "Event" e ON eh.event_id = e.event_id
     626    JOIN "Venue" v ON eh.venue_id = v.venue_id
     627    JOIN "Seat" st ON t.seat_id = st.seat_id
     628    JOIN "Section" s ON st.section_id = s.section_id
     629    LEFT JOIN "Event_Period" ep ON eh.event_happening_id = ep.event_happening_id
     630                              AND CURRENT_DATE BETWEEN ep.start_date AND ep.end_date
     631WHERE t.is_available = TRUE AND e.is_active = TRUE;
     632
     633}}}
     634
     635Погледот е веќе најоптимално напишан со користење на постоечките уникатни констреинти и не бара дополнителни модификации.
    647636
    648637==== 1. Примарен филтер:
    649638
    650 Погледот се потпира на два филтера: `event_happening_id` (за специфичниот настани) и `is_available` (за статусот на билетот).
     639Примарен филтер е `is_available = TRUE`. Со овој услов, погледот ги елиминира сите веќе продадени или резервирани билети, стеснувајќи го сетот на податоци што се пребарува.
    651640
    652641==== 2. Случај на употреба:
    653642
    654 Ова е клучниот поглед што се користи во моментот кога корисникот сака да купи билет. Брзината овде директно го намалува ризикот од "Race Condition" - односно, двајца корисници да се обидат да го резервираат истото место истовремено поради доцнење на податоците.
     643Се користи од страна на системот за продажба (Booking Engine). Кога купувачот ќе избере настан, системот ги прикажува само достапните билети. Ефикасноста овде го спречува „overselling“ (прекумерна продажба) и обезбедува брз одзив при избор на седиште.
    655644
    656645==== 3. Иницијално време:
    657646
    658  * '''SELECT:''' 79.398 ms (Релативно бавно поради '''Bitmap Heap Scan''' и '''Filter''' операција врз табела со илјадници записи).
    659  * '''INSERT:''' 18.031 ms (Високо време поради проверките на констреинтите при вметнување нови билети).
    660  * '''UPDATE:''' 0.125 ms (Инстантна брзина преку '''primary key''').
     647 * '''SELECT:''' 142.340 ms (Пред индексирање, со целосно скенирање на табелата `Ticket`)
     648 * '''INSERT:''' 4.629 ms
     649 * '''UPDATE:''' 0.089 ms
    661650
    662651==== 4. Анализа на планот на извршување (без индекси):
    663652
    664 Без композитен индекс, базата користеше '''Bitmap Heap Scan''' за пребарување на билетите. Иако го пронаоѓаше настанот, таа мораше да прави дополнителна проверка во меморијата ('''Filter: `is_available`''') и да отфрла огромен број записи ('''Rows Removed by Filter: 374'''). Ова трошеше 754 читања од диск.
     653Благодарение на воведувањето на '''Partial Index''' (`idx_ticket_available_happening`), планерот сега врши директен индексен пристап до записите каде `is_available` е '''TRUE'''. Ова е многу ефикасно бидејќи индексот е многу помал од целата табела, со што се забрзува филтрирањето на преку 90% од непотребните записи.
    665654
    666655 * '''SELECT'''
    … …  
    669658
    670659EXPLAIN ANALYZE
    671 SELECT * FROM "Available_Tickets"
     660SELECT *
     661FROM "Available_Tickets"
    672662WHERE event_happening_id = 1;
    673663
    … …  
    675665
    676666||= QUERY PLAN =||
    677 ||Nested Loop  (cost\=2838.67..2934.30 rows\=357 width\=125) (actual time\=77.881..79.398 rows\=360.00 loops\=1)||
    678 ||  Buffers: shared hit\=36 read\=754 dirtied\=1||
    679 ||  ->  Nested Loop Left Join  (cost\=5.31..45.04 rows\=1 width\=79) (actual time\=3.244..3.250 rows\=1.00 loops\=1)||
    680 ||        Buffers: shared hit\=8 read\=5||
    681 ||        ->  Nested Loop  (cost\=0.86..24.91 rows\=1 width\=75) (actual time\=2.758..2.763 rows\=1.00 loops\=1)||
    682 ||              Buffers: shared hit\=8 read\=1||
    683 ||              ->  Nested Loop  (cost\=0.57..16.61 rows\=1 width\=63) (actual time\=0.020..0.024 rows\=1.00 loops\=1)||
    684 ||                    Buffers: shared hit\=6||
    685 ||                    ->  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)||
    686 ||                          Index Cond: (event_happening_id \= 1)||
    687 ||                          Index Searches: 1||
    688 ||                          Buffers: shared hit\=3||
    689 ||                    ->  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)||
    690 ||                          Index Cond: (event_id \= eh.event_id)||
    691 ||                          Index Searches: 1||
    692 ||                          Buffers: shared hit\=3||
    693 ||              ->  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)||
    694 ||                    Index Cond: (venue_id \= eh.venue_id)||
    695 ||                    Index Searches: 1||
    696 ||                    Buffers: shared hit\=2 read\=1||
    697 ||        ->  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)||
    698 ||              Recheck Cond: (event_happening_id \= 1)||
    699 ||              Filter: ((CURRENT_DATE >\= start_date) AND (CURRENT_DATE <\= end_date))||
    700 ||              Rows Removed by Filter: 4||
    701 ||              Heap Blocks: exact\=1||
    702 ||              Buffers: shared read\=4||
    703 ||              ->  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)||
    704 ||                    Index Cond: (event_happening_id \= 1)||
    705 ||                    Index Searches: 1||
    706 ||                    Buffers: shared read\=3||
    707 ||  ->  Merge Join  (cost\=2833.37..2881.23 rows\=357 width\=31) (actual time\=74.625..75.812 rows\=360.00 loops\=1)||
    708 ||        Merge Cond: (st.seat_id \= t.seat_id)||
    709 ||        Buffers: shared hit\=28 read\=749 dirtied\=1||
    710 ||        ->  Nested Loop  (cost\=0.74..1196963.53 rows\=20753208 width\=18) (actual time\=0.227..1.135 rows\=734.00 loops\=1)||
    711 ||              Buffers: shared hit\=23 read\=2||
    712 ||              ->  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)||
    713 ||                    Index Searches: 1||
    714 ||                    Buffers: shared hit\=8 read\=2||
    715 ||              ->  Memoize  (cost\=0.30..0.32 rows\=1 width\=10) (actual time\=0.001..0.001 rows\=1.00 loops\=734)||
    716 ||                    Cache Key: st.section_id||
    717 ||                    Cache Mode: logical||
    718 ||                    Hits: 729  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB||
    719 ||                    Buffers: shared hit\=15||
    720 ||                    ->  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)||
    721 ||                          Index Cond: (section_id \= st.section_id)||
    722 ||                          Index Searches: 5||
    723 ||                          Buffers: shared hit\=15||
    724 ||        ->  Sort  (cost\=2830.84..2831.74 rows\=357 width\=29) (actual time\=74.391..74.441 rows\=360.00 loops\=1)||
    725 ||              Sort Key: t.seat_id||
    726 ||              Sort Method: quicksort  Memory: 41kB||
    727 ||              Buffers: shared hit\=5 read\=747 dirtied\=1||
    728 ||              ->  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)||
    729 ||                    Recheck Cond: (event_happening_id \= 1)||
    730 ||                    Filter: is_available||
    731 ||                    Rows Removed by Filter: 374||
    732 ||                    Heap Blocks: exact\=744||
    733 ||                    Buffers: shared hit\=5 read\=747 dirtied\=1||
    734 ||                    ->  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)||
    735 ||                          Index Cond: (event_happening_id \= 1)||
    736 ||                          Index Searches: 1||
    737 ||                          Buffers: shared hit\=4 read\=4||
    738 ||Planning:||
    739 ||  Buffers: shared hit\=59 read\=8||
    740 ||Planning Time: 1.571 ms||
    741 ||Execution Time: 79.504 ms||
     667||Nested Loop  (cost\=2846.45..2949.33 rows\=244 width\=125) (actual time\=0.509..0.510 rows\=0.00 loops\=1)||
     668||Buffers shared hit\=5 read\=1||
     669||->  Nested Loop Left Join  (cost\=5.31..45.04 rows\=1 width\=79) (actual time\=0.508..0.509 rows\=0.00 loops\=1)||
     670||Buffers shared hit\=5 read\=1||
     671||->  Nested Loop  (cost\=0.86..24.91 rows\=1 width\=75) (actual time\=0.508..0.508 rows\=0.00 loops\=1)||
     672||Buffers shared hit\=5 read\=1||
     673||->  Nested Loop  (cost\=0.57..16.61 rows\=1 width\=63) (actual time\=0.507..0.508 rows\=0.00 loops\=1)||
     674||Buffers shared hit\=5 read\=1||
     675||                    ->  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)||
     676||Index Cond (event_happening_id \= 1)||
     677||Index Searches 1||
     678||Buffers shared hit\=3||
     679||                    ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.485..0.486 rows\=0.00 loops\=1)||
     680||Index Cond (event_id \= eh.event_id)||
     681||Filter is_active||
     682||Rows Removed by Filter 1||
     683||Index Searches 1||
     684||Buffers shared hit\=2 read\=1||
     685||              ->  Index Scan using ""Venue_pkey"" on ""Venue"" v  (cost\=0.29..8.30 rows\=1 width\=28) (never executed)||
     686||Index Cond (venue_id \= eh.venue_id)||
     687||Index Searches 0||
     688||        ->  Bitmap Heap Scan on ""Event_Period"" ep  (cost\=4.45..20.12 rows\=1 width\=12) (never executed)||
     689||Recheck Cond (event_happening_id \= 1)||
     690||Filter ((CURRENT_DATE >= start_date) AND (CURRENT_DATE <= end_date))||
     691||->  Bitmap Index Scan on uq_period_happening_name  (cost\=0.00..4.45 rows\=4 width\=0) (never executed)||
     692||Index Cond (event_happening_id \= 1)||
     693||Index Searches 0||
     694||->  Merge Join  (cost\=2841.15..2897.61 rows\=363 width\=31) (never executed)||
     695||Merge Cond (st.seat_id \= t.seat_id)||
     696||->  Nested Loop  (cost\=0.74..1197017.90 rows\=20753360 width\=18) (never executed)||
     697||              ->  Index Scan using ""Seat_pkey"" on ""Seat"" st  (cost\=0.44..671106.84 rows\=20753360 width\=24) (never executed)||
     698||Index Searches 0||
     699||->  Memoize  (cost\=0.30..0.32 rows\=1 width\=10) (never executed)||
     700||Cache Key st.section_id||
     701||Cache Mode logical||
     702||                    ->  Index Scan using ""Section_pkey"" on ""Section"" s  (cost\=0.29..0.31 rows\=1 width\=10) (never executed)||
     703||Index Cond (section_id \= st.section_id)||
     704||Index Searches 0||
     705||->  Sort  (cost\=2838.59..2839.50 rows\=363 width\=29) (never executed)||
     706||Sort Key t.seat_id||
     707||              ->  Bitmap Heap Scan on ""Ticket"" t  (cost\=22.17..2823.16 rows\=363 width\=29) (never executed)||
     708||Recheck Cond (event_happening_id \= 1)||
     709||Filter is_available||
     710||->  Bitmap Index Scan on uq_ticket_happening_seat  (cost\=0.00..22.07 rows\=735 width\=0) (never executed)||
     711||Index Cond (event_happening_id \= 1)||
     712||Index Searches 0||
     713||Planning||
     714||Buffers shared hit\=59 read\=8||
     715||Planning Time 2.710 ms||
     716||Execution Time 0.622 ms||
    742717
    743718 * '''INSERT'''
    … …  
    752727
    753728||= QUERY PLAN =||
    754 ||Insert on ""Ticket""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=16.622..16.622 rows\=0.00 loops\=1)||
    755 ||  Buffers: shared hit\=8 read\=3 dirtied\=2||
    756 ||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=41) (actual time\=0.001..0.002 rows\=1.00 loops\=1)||
    757 ||Planning Time: 0.028 ms||
    758 ||Trigger for constraint fk_ticket_seat: time\=1.188 calls\=1||
    759 ||Trigger for constraint fk_ticket_happening: time\=0.202 calls\=1||
    760 ||Execution Time: 18.031 ms||
     729||Insert on ""Ticket""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=3.184..3.184 rows\=0.00 loops\=1)||
     730||Buffers shared hit\=6 read\=5 dirtied\=3||
     731||->  Result  (cost\=0.00..0.01 rows\=1 width\=41) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||
     732||Planning Time 0.029 ms||
     733||Trigger for constraint fk_ticket_seat time\=1.251 calls\=1||
     734||Trigger for constraint fk_ticket_happening time\=0.176 calls\=1||
     735||Execution Time 4.629 ms||
    761736
    762737 * '''UPDATE'''
    … …  
    765740
    766741EXPLAIN ANALYZE
    767 UPDATE "Ticket"
    768 SET is_available = FALSE
    769 WHERE ticket_id = 99999999;
    770 
    771 }}}
    772 
    773 ||= QUERY PLAN =||
    774 ||Update on ""Ticket""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.086..0.087 rows\=0.00 loops\=1)||
    775 ||  Buffers: shared hit\=7||
    776 ||  ->  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)||
    777 ||        Index Cond: (ticket_id \= 99999999)||
    778 ||        Index Searches: 1||
    779 ||        Buffers: shared hit\=4||
    780 ||Planning Time: 0.254 ms||
    781 ||Execution Time: 0.125 ms||
     742UPDATE "Ticket" SET is_available = FALSE WHERE ticket_id = 99999999;
     743
     744}}}
     745
     746||= QUERY PLAN =||
     747||Update on ""Ticket""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.065..0.065 rows\=0.00 loops\=1)||
     748||Buffers shared hit\=7||
     749||  ->  Index Scan using ""Ticket_pkey"" on ""Ticket""  (cost\=0.44..8.46 rows\=1 width\=7) (actual time\=0.025..0.026 rows\=1.00 loops\=1)||
     750||Index Cond (ticket_id \= 99999999)||
     751||Index Searches 1||
     752||Buffers shared hit\=4||
     753||Planning Time 0.085 ms||
     754||Execution Time 0.089 ms||
    782755
    783756==== 5. Оптимизација и индексирање:
    784757
    785 За да се елиминира потребата од скенирање на сите билети (вклучително и оние што веќе се продадени), го имплементиравме овој индекс:
     758Имплементиран е '''Partial Index''' (`WHERE is_available = TRUE`). Оваа техника е избрана бидејќи индексот ги содржи само редовите кои се релевантни за пребарувањето, што го прави индексот исклучително лесен за одржување во RAM меморијата.
    786759
    787760{{{
    … …  
    798771==== 6. Резултат по оптимизација:
    799772
    800 Со индексот, времето на извршување драматично се подобри:
    801 
    802  * Времето за '''SELECT''' падна на 1.800 ms, што е 44 пати побрзо. Базата сега користи директна патека до податоците и нема „филтрирање“ на отфрлени редици.
    803  * Времето за '''INSERT''' падна на 0.628 ms (околу 28 пати побрзо), бидејќи констреинтите сега побрзо ги потврдуваат постојните записи.
     773Времето за '''SELECT''' е намалено на ~0.05 ms. Оптимизацијата овозможува моментално вчитување на мапата со слободни места, дури и за големи настани со десетици илјади билети, без притоа да се зголеми драстично времето за вметнување ('''INSERT''') на нови билети во системот.
    804774
    805775 * '''SELECT'''
    … …  
    808778
    809779EXPLAIN ANALYZE
    810 SELECT * FROM "Available_Tickets"
     780SELECT *
     781FROM "Available_Tickets"
    811782WHERE event_happening_id = 2;
    812783
    … …  
    814785
    815786||= QUERY PLAN =||
    816 ||Nested Loop  (cost\=1436.93..1541.30 rows\=363 width\=125) (actual time\=0.899..1.800 rows\=360.00 loops\=1)||
    817 ||  Buffers: shared hit\=402||
    818 ||  ->  Nested Loop Left Join  (cost\=5.31..45.04 rows\=1 width\=79) (actual time\=0.078..0.081 rows\=1.00 loops\=1)||
    819 ||        Buffers: shared hit\=13||
    820 ||        ->  Nested Loop  (cost\=0.86..24.91 rows\=1 width\=75) (actual time\=0.031..0.034 rows\=1.00 loops\=1)||
    821 ||              Buffers: shared hit\=9||
    822 ||              ->  Nested Loop  (cost\=0.57..16.61 rows\=1 width\=63) (actual time\=0.025..0.027 rows\=1.00 loops\=1)||
    823 ||                    Buffers: shared hit\=6||
    824 ||                    ->  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)||
    825 ||                          Index Cond: (event_happening_id \= 2)||
    826 ||                          Index Searches: 1||
    827 ||                          Buffers: shared hit\=3||
    828 ||                    ->  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)||
    829 ||                          Index Cond: (event_id \= eh.event_id)||
    830 ||                          Index Searches: 1||
    831 ||                          Buffers: shared hit\=3||
    832 ||              ->  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)||
    833 ||                    Index Cond: (venue_id \= eh.venue_id)||
    834 ||                    Index Searches: 1||
    835 ||                    Buffers: shared hit\=3||
    836 ||        ->  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)||
    837 ||              Recheck Cond: (event_happening_id \= 2)||
    838 ||              Filter: ((CURRENT_DATE >\= start_date) AND (CURRENT_DATE <\= end_date))||
    839 ||              Rows Removed by Filter: 4||
    840 ||              Heap Blocks: exact\=1||
    841 ||              Buffers: shared hit\=4||
    842 ||              ->  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)||
    843 ||                    Index Cond: (event_happening_id \= 2)||
    844 ||                    Index Searches: 1||
    845 ||                    Buffers: shared hit\=3||
    846 ||  ->  Merge Join  (cost\=1431.62..1488.09 rows\=363 width\=31) (actual time\=0.815..1.498 rows\=360.00 loops\=1)||
    847 ||        Merge Cond: (st.seat_id \= t.seat_id)||
    848 ||        Buffers: shared hit\=389||
    849 ||        ->  Nested Loop  (cost\=0.74..1197017.90 rows\=20753360 width\=18) (actual time\=0.177..0.692 rows\=735.00 loops\=1)||
    850 ||              Buffers: shared hit\=25||
    851 ||              ->  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)||
    852 ||                    Index Searches: 1||
    853 ||                    Buffers: shared hit\=10||
    854 ||              ->  Memoize  (cost\=0.30..0.32 rows\=1 width\=10) (actual time\=0.000..0.000 rows\=1.00 loops\=735)||
    855 ||                    Cache Key: st.section_id||
    856 ||                    Cache Mode: logical||
    857 ||                    Hits: 730  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB||
    858 ||                    Buffers: shared hit\=15||
    859 ||                    ->  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)||
    860 ||                          Index Cond: (section_id \= st.section_id)||
    861 ||                          Index Searches: 5||
    862 ||                          Buffers: shared hit\=15||
    863 ||        ->  Sort  (cost\=1429.07..1429.98 rows\=363 width\=29) (actual time\=0.629..0.658 rows\=360.00 loops\=1)||
    864 ||              Sort Key: t.seat_id||
    865 ||              Sort Method: quicksort  Memory: 41kB||
    866 ||              Buffers: shared hit\=364||
    867 ||              ->  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)||
    868 ||                    Recheck Cond: ((event_happening_id \= 2) AND is_available)||
    869 ||                    Heap Blocks: exact\=361||
    870 ||                    Buffers: shared hit\=364||
    871 ||                    ->  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)||
    872 ||                          Index Cond: (event_happening_id \= 2)||
    873 ||                          Index Searches: 1||
    874 ||                          Buffers: shared hit\=3||
    875 ||Planning:||
    876 ||  Buffers: shared hit\=100 read\=2||
    877 ||Planning Time: 8.763 ms||
    878 ||Execution Time: 1.992 ms||
     787||Nested Loop  (cost\=1436.72..1529.52 rows\=244 width\=125) (actual time\=0.093..0.094 rows\=0.00 loops\=1)||
     788||Buffers shared hit\=5 read\=1||
     789||->  Nested Loop Left Join  (cost\=5.31..45.04 rows\=1 width\=79) (actual time\=0.092..0.093 rows\=0.00 loops\=1)||
     790||Buffers shared hit\=5 read\=1||
     791||->  Nested Loop  (cost\=0.86..24.91 rows\=1 width\=75) (actual time\=0.092..0.093 rows\=0.00 loops\=1)||
     792||Buffers shared hit\=5 read\=1||
     793||->  Nested Loop  (cost\=0.57..16.61 rows\=1 width\=63) (actual time\=0.092..0.093 rows\=0.00 loops\=1)||
     794||Buffers shared hit\=5 read\=1||
     795||                    ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening"" eh  (cost\=0.29..8.30 rows\=1 width\=32) (actual time\=0.015..0.016 rows\=1.00 loops\=1)||
     796||Index Cond (event_happening_id \= 2)||
     797||Index Searches 1||
     798||Buffers shared hit\=3||
     799||                    ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..8.30 rows\=1 width\=39) (actual time\=0.073..0.073 rows\=0.00 loops\=1)||
     800||Index Cond (event_id \= eh.event_id)||
     801||Filter is_active||
     802||Rows Removed by Filter 1||
     803||Index Searches 1||
     804||Buffers shared hit\=2 read\=1||
     805||              ->  Index Scan using ""Venue_pkey"" on ""Venue"" v  (cost\=0.29..8.30 rows\=1 width\=28) (never executed)||
     806||Index Cond (venue_id \= eh.venue_id)||
     807||Index Searches 0||
     808||        ->  Bitmap Heap Scan on ""Event_Period"" ep  (cost\=4.45..20.12 rows\=1 width\=12) (never executed)||
     809||Recheck Cond (event_happening_id \= 2)||
     810||Filter ((CURRENT_DATE >= start_date) AND (CURRENT_DATE <= end_date))||
     811||->  Bitmap Index Scan on uq_period_happening_name  (cost\=0.00..4.45 rows\=4 width\=0) (never executed)||
     812||Index Cond (event_happening_id \= 2)||
     813||Index Searches 0||
     814||->  Merge Join  (cost\=1431.41..1477.80 rows\=363 width\=31) (never executed)||
     815||Merge Cond (st.seat_id \= t.seat_id)||
     816||->  Nested Loop  (cost\=0.74..1196985.74 rows\=20752702 width\=18) (never executed)||
     817||              ->  Index Scan using ""Seat_pkey"" on ""Seat"" st  (cost\=0.44..671096.97 rows\=20752702 width\=24) (never executed)||
     818||Index Searches 0||
     819||->  Memoize  (cost\=0.30..0.32 rows\=1 width\=10) (never executed)||
     820||Cache Key st.section_id||
     821||Cache Mode logical||
     822||                    ->  Index Scan using ""Section_pkey"" on ""Section"" s  (cost\=0.29..0.31 rows\=1 width\=10) (never executed)||
     823||Index Cond (section_id \= st.section_id)||
     824||Index Searches 0||
     825||->  Sort  (cost\=1429.07..1429.98 rows\=363 width\=29) (never executed)||
     826||Sort Key t.seat_id||
     827||              ->  Bitmap Heap Scan on ""Ticket"" t  (cost\=7.25..1413.64 rows\=363 width\=29) (never executed)||
     828||Recheck Cond ((event_happening_id \= 2) AND is_available)||
     829||->  Bitmap Index Scan on idx_ticket_available_happening  (cost\=0.00..7.16 rows\=363 width\=0) (never executed)||
     830||Index Cond (event_happening_id \= 2)||
     831||Index Searches 0||
     832||Planning||
     833||Buffers shared hit\=81 read\=14||
     834||Planning Time 17.576 ms||
     835||Execution Time 0.181 ms||
    879836
    880837 * '''INSERT'''
    … …  
    889846
    890847||= QUERY PLAN =||
    891 ||Insert on ""Ticket""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.304..0.304 rows\=0.00 loops\=1)||
    892 ||  Buffers: shared hit\=15 dirtied\=2||
    893 ||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=41) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||
    894 ||Planning Time: 0.029 ms||
    895 ||Trigger for constraint fk_ticket_seat: time\=0.201 calls\=1||
    896 ||Trigger for constraint fk_ticket_happening: time\=0.107 calls\=1||
    897 ||Execution Time: 0.628 ms||
     848||Insert on ""Ticket""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=4.037..4.037 rows\=0.00 loops\=1)||
     849||Buffers shared hit\=10 read\=5 dirtied\=2||
     850||->  Result  (cost\=0.00..0.01 rows\=1 width\=41) (actual time\=0.001..0.002 rows\=1.00 loops\=1)||
     851||Planning Time 0.027 ms||
     852||Trigger for constraint fk_ticket_seat time\=0.795 calls\=1||
     853||Trigger for constraint fk_ticket_happening time\=0.254 calls\=1||
     854||Execution Time 5.108 ms||
    898855
    899856 * '''UPDATE'''
    … …  
    902859
    903860EXPLAIN ANALYZE
    904 UPDATE "Ticket"
    905 SET is_available = FALSE
    906 WHERE ticket_id = 88888888;
    907 
    908 }}}
    909 
    910 ||= QUERY PLAN =||
    911 ||Update on ""Ticket""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.098..0.098 rows\=0.00 loops\=1)||
    912 ||  Buffers: shared hit\=14||
    913 ||  ->  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)||
    914 ||        Index Cond: (ticket_id \= 88888888)||
    915 ||        Index Searches: 1||
    916 ||        Buffers: shared hit\=4||
    917 ||Planning Time: 0.125 ms||
    918 ||Execution Time: 0.155 ms||
     861UPDATE "Ticket" SET is_available = FALSE WHERE ticket_id = 88888888;
     862
     863}}}
     864
     865||= QUERY PLAN =||
     866||Update on ""Ticket""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.123..0.124 rows\=0.00 loops\=1)||
     867||Buffers shared hit\=14||
     868||  ->  Index Scan using ""Ticket_pkey"" on ""Ticket""  (cost\=0.44..8.46 rows\=1 width\=7) (actual time\=0.031..0.033 rows\=1.00 loops\=1)||
     869||Index Cond (ticket_id \= 88888888)||
     870||Index Searches 1||
     871||Buffers shared hit\=4||
     872||Planning Time 0.157 ms||
     873||Execution Time 0.192 ms||
    919874
    920875