Changes between Version 91 and Version 92 of QueryOptimization


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

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v91 v92  
    394394== Анализа и оптимизација на `Future_Events`
    395395
    396 Овој поглед служи за динамично генерирање на репертоарот, прикажувајќи ги исклучиво претстојните настани преку филтрирање на изминатите термини во однос на моменталното време на системот. Дополнително, тој ја прикажува комплетната географска адреса и локација на објектот каде ќе се одржи настанот.
     396Овој поглед служи како динамичен извор за репертоарот на платформата. Ги изолира само идните настани (`eh.event_time > CURRENT_TIMESTAMP`) кои се означени како активни, прикажувајќи ги со нивната целосна географска локација. Оптимизиран е за брзо пребарување од страна на крајните корисници при избор на термин.
    397397
    398398{{{
    399399
    400400CREATE OR REPLACE VIEW "Future_Events" AS
    401 SELECT
    402     e.event_id,
    403     e.name AS event_name,
    404     eh.event_happening_id,
    405     eh.event_time,
    406     v.venue_id,
    407     v.name AS venue_name,
    408     v.address_street AS street,
    409     v.address_city AS city,
    410     v.address_country AS country
     401SELECT e.event_id, e.name AS event_name, eh.event_happening_id, eh.event_time, v.venue_id, v.name AS venue_name,
     402       v.address_street AS street, v.address_city AS city, v.address_country AS country
    411403FROM "Event" e
    412 JOIN "Event_Happening" eh ON e.event_id = eh.event_id
    413 JOIN "Venue" v ON eh.venue_id = v.venue_id
    414 WHERE eh.event_time > CURRENT_TIMESTAMP;
    415 
    416 }}}
    417 
    418 Погледот е веќе најоптимално напишан и не треба да се преуредува.
     404    JOIN "Event_Happening" eh ON e.event_id = eh.event_id
     405    JOIN "Venue" v ON eh.venue_id = v.venue_id
     406WHERE eh.event_time > CURRENT_TIMESTAMP AND e.is_active = TRUE;
     407
     408}}}
     409
     410Погледот е веќе најоптимално напишан со користење на постоечките уникатни констреинти и не бара дополнителни модификации.
    419411
    420412==== 1. Примарен филтер:
    421413
    422 Примарниот филтер е колоната `event_time` во табелата `Event_Happening`. Погледот постојано го бара само подмножеството на записи кои се во иднина.
     414Примарен филтер е `eh.event_time > CURRENT_TIMESTAMP`. Овој услов го дефинира временскиот опсег на податоците што се прикажуваат, овозможувајќи системско филтрирање на сите завршени настани.
    423415
    424416==== 2. Случај на употреба:
    425417
    426 Овој поглед е „лицето“ на платформата. Се користи при вчитување на листата на претстојни настани за корисниците. Ефикасноста овде директно го дефинира времето на првично вчитување на апликацијата.
     418Погледот се користи на почетната страница на апликацијата („Coming Soon“ секцијата). Корисниците овде ги пребаруваат идните можности за забава, па брзината на овој поглед е критична за првичниот впечаток и брзото навигациско искуство.
    427419
    428420==== 3. Иницијално време:
    429421
    430  * '''SELECT:''' 100.080 ms (Бавно поради '''Seq Scan''' низ илјадници записи во `Event_Happening` за проверка на времето).
    431  * '''INSERT:''' 12.452 ms (Процесирањето на тригерите и запишувањето на податокот).
    432  * '''UPDATE:''' 0.104 ms (Инстантна брзина преку '''primary key''').
     422 * '''SELECT:''' 91.702 ms (Пред индексирање, со секвенцијално скенирање на целата табела `Event_Happening`)
     423 * '''INSERT:''' 3.157 ms
     424 * '''UPDATE:''' 0.102 ms
    433425
    434426==== 4. Анализа на планот на извршување (без индекси):
    435427
    436 Без индекс на времето, базата мораше да прави '''Sequential Scan''' низ целата табела `Event_Happening` (12,973 записи), што резултираше со бавно филтрирање во меморијата ('''Filter: `event_time > CURRENT_TIMESTAMP`'''). Дополнително, базата трошеше непотребни 466 читања од диск за да ја провери секоја редица.
     428Со воведувањето на индексот `idx_event_happening_time`, планерот го замени скапото секвенцијално скенирање со '''Bitmap Index Scan'''. Базата сега многу побрзо ги наоѓа само оние записи каде што датумот е во иднина, користејќи ја индексната структура наместо да ја чита секоја редица од табелата. Потоа се врши ефикасен '''Hash Join''' со табелата `Venue`.
    437429
    438430 * '''SELECT'''
    … …  
    441433
    442434EXPLAIN ANALYZE
    443 SELECT * FROM "Future_Events"
     435SELECT *
     436FROM "Future_Events"
    444437WHERE city = 'London';
    445438
    … …  
    447440
    448441||= QUERY PLAN =||
    449 ||Nested Loop  (cost\=263.49..1377.68 rows\=332 width\=121) (actual time\=3.243..100.080 rows\=340.00 loops\=1)||
    450 ||  Buffers: shared hit\=778 read\=844||
    451 ||  ->  Hash Join  (cost\=263.20..1234.31 rows\=332 width\=90) (actual time\=2.695..18.025 rows\=340.00 loops\=1)||
    452 ||        Hash Cond: (eh.venue_id \= v.venue_id)||
    453 ||        Buffers: shared hit\=2 read\=600||
    454 ||        ->  Seq Scan on ""Event_Happening"" eh  (cost\=0.00..937.09 rows\=12957 width\=32) (actual time\=0.154..12.105 rows\=12973.00 loops\=1)||
    455 ||              Filter: (event_time > CURRENT_TIMESTAMP)||
    456 ||              Rows Removed by Filter: 18365||
    457 ||              Buffers: shared hit\=1 read\=466||
    458 ||        ->  Hash  (cost\=260.00..260.00 rows\=256 width\=66) (actual time\=2.516..2.517 rows\=256.00 loops\=1)||
    459 ||              Buckets: 1024  Batches: 1  Memory Usage: 33kB||
    460 ||              Buffers: shared hit\=1 read\=134||
    461 ||              ->  Seq Scan on ""Venue"" v  (cost\=0.00..260.00 rows\=256 width\=66) (actual time\=0.222..2.393 rows\=256.00 loops\=1)||
    462 ||                    Filter: ((address_city)::text \= 'London'::text)||
    463 ||                    Rows Removed by Filter: 9744||
    464 ||                    Buffers: shared hit\=1 read\=134||
    465 ||  ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..0.43 rows\=1 width\=39) (actual time\=0.237..0.237 rows\=1.00 loops\=340)||
    466 ||        Index Cond: (event_id \= eh.event_id)||
    467 ||        Index Searches: 340||
    468 ||        Buffers: shared hit\=776 read\=244||
    469 ||Planning:||
    470 ||  Buffers: shared hit\=31 read\=8||
    471 ||Planning Time: 0.659 ms||
    472 ||Execution Time: 100.249 ms||
     442||Nested Loop  (cost\=263.49..1419.10 rows\=222 width\=121) (actual time\=3.997..91.702 rows\=230.00 loops\=1)||
     443||Buffers shared hit\=745 read\=877||
     444||->  Hash Join  (cost\=263.20..1234.27 rows\=331 width\=90) (actual time\=3.433..19.179 rows\=340.00 loops\=1)||
     445||Hash Cond (eh.venue_id \= v.venue_id)||
     446||Buffers shared hit\=2 read\=600||
     447||        ->  Seq Scan on ""Event_Happening"" eh  (cost\=0.00..937.09 rows\=12944 width\=32) (actual time\=1.890..14.843 rows\=12961.00 loops\=1)||
     448||Filter (event_time > CURRENT_TIMESTAMP)||
     449||Rows Removed by Filter 18377||
     450||Buffers shared hit\=1 read\=466||
     451||->  Hash  (cost\=260.00..260.00 rows\=256 width\=66) (actual time\=1.525..1.527 rows\=256.00 loops\=1)||
     452||Buckets 1024  Batches 1  Memory Usage 33kB||
     453||Buffers shared hit\=1 read\=134||
     454||              ->  Seq Scan on ""Venue"" v  (cost\=0.00..260.00 rows\=256 width\=66) (actual time\=0.160..1.467 rows\=256.00 loops\=1)||
     455||Filter ((address_city)||||text \= 'London'||||text)||
     456||Rows Removed by Filter 9744||
     457||Buffers shared hit\=1 read\=134||
     458||  ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..0.56 rows\=1 width\=39) (actual time\=0.211..0.211 rows\=0.68 loops\=340)||
     459||Index Cond (event_id \= eh.event_id)||
     460||Filter is_active||
     461||Rows Removed by Filter 0||
     462||Index Searches 340||
     463||Buffers shared hit\=743 read\=277||
     464||Planning||
     465||Buffers shared hit\=31 read\=8||
     466||Planning Time 0.685 ms||
     467||Execution Time 91.820 ms||
    473468
    474469 * '''INSERT'''
    … …  
    483478
    484479||= QUERY PLAN =||
    485 ||Insert on ""Event_Happening""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=12.004..12.005 rows\=0.00 loops\=1)||
    486 ||  Buffers: shared hit\=4 read\=5 dirtied\=1||
    487 ||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=100) (actual time\=0.002..0.003 rows\=1.00 loops\=1)||
    488 ||Planning Time: 0.047 ms||
    489 ||Trigger for constraint fk_happening_event: time\=0.276 calls\=1||
    490 ||Trigger for constraint fk_happening_venue: time\=0.134 calls\=1||
    491 ||Execution Time: 12.452 ms||
     480||Insert on ""Event_Happening""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=2.317..2.317 rows\=0.00 loops\=1)||
     481||Buffers shared hit\=4 read\=5 dirtied\=1||
     482||->  Result  (cost\=0.00..0.01 rows\=1 width\=100) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||
     483||Planning Time 0.029 ms||
     484||Trigger for constraint fk_happening_event time\=0.669 calls\=1||
     485||Trigger for constraint fk_happening_venue time\=0.152 calls\=1||
     486||Execution Time 3.157 ms||
    492487
    493488 * '''UPDATE'''
    … …  
    496491
    497492EXPLAIN ANALYZE
    498 UPDATE "Event_Happening"
    499 SET event_time = '2027-01-01 21:00:00'
     493UPDATE "Event_Happening" SET event_time = '2027-01-01 21:00:00'
    500494WHERE event_happening_id = 99999999;
    501495
    … …  
    503497
    504498||= QUERY PLAN =||
    505 ||Update on ""Event_Happening""  (cost\=0.29..8.30 rows\=0 width\=0) (actual time\=0.072..0.072 rows\=0.00 loops\=1)||
    506 ||  Buffers: shared hit\=12||
    507 ||  ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening""  (cost\=0.29..8.30 rows\=1 width\=14) (actual time\=0.023..0.024 rows\=1.00 loops\=1)||
    508 ||        Index Cond: (event_happening_id \= 99999999)||
    509 ||        Index Searches: 1||
    510 ||        Buffers: shared hit\=3||
    511 ||Planning Time: 0.089 ms||
    512 ||Execution Time: 0.104 ms||
     499||Update on ""Event_Happening""  (cost\=0.29..8.30 rows\=0 width\=0) (actual time\=0.075..0.076 rows\=0.00 loops\=1)||
     500||Buffers shared hit\=12||
     501||  ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening""  (cost\=0.29..8.30 rows\=1 width\=14) (actual time\=0.027..0.028 rows\=1.00 loops\=1)||
     502||Index Cond (event_happening_id \= 99999999)||
     503||Index Searches 1||
     504||Buffers shared hit\=3||
     505||Planning Time 0.096 ms||
     506||Execution Time 0.102 ms||
    513507
    514508==== 5. Оптимизација и индексирање:
    515509
    516 За да се елиминира потребата од пребарување на целата табела, креиравме '''B-tree''' индекс врз колоната `event_time`. Ова му овозможува на планерот да го лоцира временскиот „праг“ моментално.
     510Имплементиран е `idx_event_happening_time` ('''B-tree''' индекс) на колоната `event_time`. Ова е идеално за „range queries“ (пребарување на опсег), бидејќи овозможува базата да ги „прескокне“ сите стари настани и да започне со читање директно од сегашниот момент па натаму.
    517511
    518512{{{
    … …  
    528522==== 6. Резултат по оптимизација:
    529523
    530 Со воведувањето на индексот, базата премина на '''Bitmap Index Scan''', што значи дека ја чита само релевантната мапа на записи наместо целиот диск.
    531 
    532  * Времето за '''SELECT''' падна на 8.451 ms, што е приближно 12 пати побрзо.
    533  * Времето за '''INSERT''' падна на 0.458 ms (околу 27 пати побрзо), бидејќи индексната структура се ажурира ефикасно.
     524Времето за '''SELECT''' е намалено на ~13.24 ms, што претставува значително подобрување (~7 пати побрзо). Системот сега инстантно ги прикажува претстојните настани без притоа да го оптоварува хард дискот со непотребно читање на историски податоци.
    534525
    535526 * '''SELECT'''
    … …  
    538529
    539530EXPLAIN ANALYZE
    540 SELECT * FROM "Future_Events"
     531SELECT *
     532FROM "Future_Events"
    541533WHERE city = 'Berlin';
    542534
    … …  
    544536
    545537||= QUERY PLAN =||
    546 ||Nested Loop  (cost\=509.90..1246.42 rows\=96 width\=121) (actual time\=2.527..8.451 rows\=114.00 loops\=1)||
    547 ||  Buffers: shared hit\=981||
    548 ||  ->  Hash Join  (cost\=509.62..1204.96 rows\=96 width\=90) (actual time\=2.504..7.880 rows\=114.00 loops\=1)||
    549 ||        Hash Cond: (eh.venue_id \= v.venue_id)||
    550 ||        Buffers: shared hit\=639||
    551 ||        ->  Bitmap Heap Scan on ""Event_Happening"" eh  (cost\=248.69..910.02 rows\=12955 width\=32) (actual time\=0.722..3.795 rows\=12973.00 loops\=1)||
    552 ||              Recheck Cond: (event_time > CURRENT_TIMESTAMP)||
    553 ||              Heap Blocks: exact\=467||
    554 ||              Buffers: shared hit\=504||
    555 ||              ->  Bitmap Index Scan on idx_event_happening_time  (cost\=0.00..245.45 rows\=12955 width\=0) (actual time\=0.656..0.656 rows\=12976.00 loops\=1)||
    556 ||                    Index Cond: (event_time > CURRENT_TIMESTAMP)||
    557 ||                    Index Searches: 1||
    558 ||                    Buffers: shared hit\=37||
    559 ||        ->  Hash  (cost\=260.00..260.00 rows\=74 width\=66) (actual time\=1.770..1.771 rows\=74.00 loops\=1)||
    560 ||              Buckets: 1024  Batches: 1  Memory Usage: 15kB||
    561 ||              Buffers: shared hit\=135||
    562 ||              ->  Seq Scan on ""Venue"" v  (cost\=0.00..260.00 rows\=74 width\=66) (actual time\=0.011..1.739 rows\=74.00 loops\=1)||
    563 ||                    Filter: ((address_city)::text \= 'Berlin'::text)||
    564 ||                    Rows Removed by Filter: 9926||
    565 ||                    Buffers: shared hit\=135||
    566 ||  ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..0.43 rows\=1 width\=39) (actual time\=0.004..0.004 rows\=1.00 loops\=114)||
    567 ||        Index Cond: (event_id \= eh.event_id)||
    568 ||        Index Searches: 114||
    569 ||        Buffers: shared hit\=342||
    570 ||Planning:||
    571 ||  Buffers: shared hit\=40||
    572 ||Planning Time: 0.547 ms||
    573 ||Execution Time: 8.502 ms||
     538||Nested Loop  (cost\=509.92..1258.59 rows\=64 width\=121) (actual time\=3.296..13.248 rows\=73.00 loops\=1)||
     539||Buffers shared hit\=204 read\=780||
     540||->  Hash Join  (cost\=509.63..1205.01 rows\=96 width\=90) (actual time\=3.183..9.204 rows\=114.00 loops\=1)||
     541||Hash Cond (eh.venue_id \= v.venue_id)||
     542||Buffers shared hit\=4 read\=638||
     543||        ->  Bitmap Heap Scan on ""Event_Happening"" eh  (cost\=248.71..910.06 rows\=12957 width\=32) (actual time\=1.386..5.330 rows\=12961.00 loops\=1)||
     544||Recheck Cond (event_time > CURRENT_TIMESTAMP)||
     545||Heap Blocks exact\=467||
     546||Buffers shared hit\=4 read\=503||
     547||->  Bitmap Index Scan on idx_event_happening_time  (cost\=0.00..245.47 rows\=12957 width\=0) (actual time\=1.236..1.236 rows\=12962.00 loops\=1)||
     548||Index Cond (event_time > CURRENT_TIMESTAMP)||
     549||Index Searches 1||
     550||Buffers shared hit\=3 read\=37||
     551||->  Hash  (cost\=260.00..260.00 rows\=74 width\=66) (actual time\=1.755..1.755 rows\=74.00 loops\=1)||
     552||Buckets 1024  Batches 1  Memory Usage 15kB||
     553||Buffers shared read\=135||
     554||              ->  Seq Scan on ""Venue"" v  (cost\=0.00..260.00 rows\=74 width\=66) (actual time\=0.118..1.700 rows\=74.00 loops\=1)||
     555||Filter ((address_city)||||text \= 'Berlin'||||text)||
     556||Rows Removed by Filter 9926||
     557||Buffers shared read\=135||
     558||  ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..0.56 rows\=1 width\=39) (actual time\=0.034..0.034 rows\=0.64 loops\=114)||
     559||Index Cond (event_id \= eh.event_id)||
     560||Filter is_active||
     561||Rows Removed by Filter 0||
     562||Index Searches 114||
     563||Buffers shared hit\=200 read\=142||
     564||Planning||
     565||Buffers shared hit\=68 read\=3||
     566||Planning Time 0.831 ms||
     567||Execution Time 13.311 ms||
    574568
    575569 * '''INSERT'''
    … …  
    584578
    585579||= QUERY PLAN =||
    586 ||Insert on ""Event_Happening""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.275..0.275 rows\=0.00 loops\=1)||
    587 ||  Buffers: shared hit\=12 dirtied\=2||
    588 ||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=100) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||
    589 ||Planning Time: 0.031 ms||
    590 ||Trigger for constraint fk_happening_event: time\=0.109 calls\=1||
    591 ||Trigger for constraint fk_happening_venue: time\=0.058 calls\=1||
    592 ||Execution Time: 0.458 ms||
     580||Insert on ""Event_Happening""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=3.942..3.943 rows\=0.00 loops\=1)||
     581||Buffers shared hit\=7 read\=5 dirtied\=3||
     582||->  Result  (cost\=0.00..0.01 rows\=1 width\=100) (actual time\=0.001..0.002 rows\=1.00 loops\=1)||
     583||Planning Time 0.032 ms||
     584||Trigger for constraint fk_happening_event time\=0.164 calls\=1||
     585||Trigger for constraint fk_happening_venue time\=0.061 calls\=1||
     586||Execution Time 4.192 ms||
    593587
    594588 * '''UPDATE'''
    … …  
    597591
    598592EXPLAIN ANALYZE
    599 UPDATE "Event_Happening"
    600 SET event_time = '2028-05-16 20:00:00'
    601 WHERE event_happening_id = 88888888;
    602 
    603 }}}
    604 
    605 ||= QUERY PLAN =||
    606 ||Update on ""Event_Happening""  (cost\=0.29..8.30 rows\=0 width\=0) (actual time\=0.073..0.073 rows\=0.00 loops\=1)||
    607 ||  Buffers: shared hit\=14||
    608 ||  ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening""  (cost\=0.29..8.30 rows\=1 width\=14) (actual time\=0.022..0.023 rows\=1.00 loops\=1)||
    609 ||        Index Cond: (event_happening_id \= 88888888)||
    610 ||        Index Searches: 1||
    611 ||        Buffers: shared hit\=3||
    612 ||Planning Time: 0.107 ms||
    613 ||Execution Time: 0.119 ms||
     593UPDATE "Event_Happening" SET event_time = '2028-05-16 20:00:00' WHERE event_happening_id = 88888888;
     594
     595}}}
     596
     597||= QUERY PLAN =||
     598||Update on ""Event_Happening""  (cost\=0.29..8.30 rows\=0 width\=0) (actual time\=0.072..0.072 rows\=0.00 loops\=1)||
     599||Buffers shared hit\=14||
     600||  ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening""  (cost\=0.29..8.30 rows\=1 width\=14) (actual time\=0.021..0.022 rows\=1.00 loops\=1)||
     601||Index Cond (event_happening_id \= 88888888)||
     602||Index Searches 1||
     603||Buffers shared hit\=3||
     604||Planning Time 0.104 ms||
     605||Execution Time 0.119 ms||
    614606
    615607