Changes between Version 10 and Version 11 of F3


Ignore:
Timestamp:
05/27/26 19:56:47 (9 hours ago)
Author:
231121
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • F3

    v10 v11  
    1111}}}
    1212
    13 [[Image(filter.png)]]
     13{{{
     14QUERY PLAN
     15Merge Right Join  (cost=249312.60..251333.27 rows=980 width=20) (actual time=487.591..492.755 rows=49 loops=1)
     16  Merge Cond: (ap.appointment_time = ts.slot_time)
     17  ->  Sort  (cost=249234.46..250242.10 rows=403054 width=12) (actual time=487.433..492.584 rows=0 loops=1)
     18        Sort Key: ap.appointment_time
     19        Sort Method: quicksort  Memory: 25kB
     20        ->  Gather  (cost=1000.00..204817.40 rows=403054 width=12) (actual time=487.420..492.570 rows=0 loops=1)
     21              Workers Planned: 2
     22              Workers Launched: 2
     23              ->  Parallel Seq Scan on appointment ap  (cost=0.00..163512.00 rows=167939 width=12) (actual time=458.175..458.175 rows=0 loops=3)
     24                    Filter: ((exam_room_id = 1) AND (status)::text = 'Scheduled'::text)
     25                    Rows Removed by Filter: 333333
     26  ->  Sort  (cost=78.13..80.63 rows=1000 width=20) (actual time=0.155..0.159 rows=49 loops=1)
     27        Sort Key: ts.slot_time
     28        Sort Method: quicksort  Memory: 26kB
     29        ->  Nested Loop  (cost=0.29..28.30 rows=1000 width=20) (actual time=0.052..0.060 rows=49 loops=1)
     30              ->  Index Scan using exam_room_pkey on exam_room er  (cost=0.28..8.29 rows=1 width=12) (actual time=0.019..0.020 rows=1 loops=1)
     31                    Index Cond: (exam_room_id = 1)
     32                    Filter: is_available
     33              ->  Function Scan on generate_series ts  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.026..0.029 rows=49 loops=1)
     34
     35Planning Time: 1.971 ms
     36Execution Time: 492.828 ms
     37}}}
    1438
    1539• Времето на извршување на insert изнесува 40.213ms. Тоа време е задоволително.
    1640     
    17 ```EXPLAIN ANALYZE```
    18 ```INSERT INTO Appointment (appointment_id, appointment-time, status, exam_room_id) VALUES (10000001, NOW() + INTERVAL '2 hours', 'Scheduled', 1);```
     41{{{EXPLAIN ANALYZE}}}
     42{{{INSERT INTO Appointment (appointment_id, appointment-time, status, exam_room_id) VALUES (10000001, NOW() + INTERVAL '2 hours', 'Scheduled', 1);}}}
    1943
    20 [[Image(insert.png)]]
     44{{{
     45QUERY PLAN
     46
     47Insert on appointment  (cost=0.00..0.02 rows=0 width=0) (actual time=28.065..28.066 rows=0 loops=1)
     48  ->  Result  (cost=0.00..0.02 rows=1 width=500) (actual time=0.007..0.007 rows=1 loops=1)
     49
     50Planning Time: 0.053 ms
     51
     52Trigger for constraint appointment_pet_id_fkey: time=11.548 calls=1
     53Trigger for constraint appointment_exam_room_id_fkey: time=0.487 calls=1
     54Trigger for constraint appointment_receptionist_id_fkey: time=0.080 calls=1
     55
     56Execution Time: 40.213 ms
     57}}}
    2158
    2259• Времето на извршување на update изнесува 2137.049ms. Тоа време можеме да го подобриме со индекс, бидејќи овој view би бил користен повеќе пати на ден при закажување нови прегледи.
    2360
    24 ```EXPLAIN ANALYZE```
    25 ```UPDATE Appointment SET appointment_time = appointment_time + INTERVAL '30 minutes' WHERE exam_room_id = 1 AND status = 'Scheduled';```
     61{{{EXPLAIN ANALYZE}}}
     62{{{UPDATE Appointment SET appointment_time = appointment_time + INTERVAL '30 minutes' WHERE exam_room_id = 1 AND status = 'Scheduled';}}}
    2663
    27 [[Image(update1.png)]]
     64{{{
     65QUERY PLAN
    2866
     67Update on appointment  (cost=0.00..252019.64 rows=0 width=0) (actual time=2136.984..2136.986 rows=0 loops=1)
     68  ->  Seq Scan on appointment  (cost=0.00..252019.64 rows=403054 width=14) (actual time=1938.264..1938.264 rows=0 loops=1)
     69        Filter: ((exam_room_id = 1) AND ((status)::text = 'Scheduled'::text))
     70        Rows Removed by Filter: 10000000
     71
     72Planning Time: 0.090 ms
     73
     74Execution Time: 2137.049 ms
     75}}}
    2976== Креирање на индексот ==
    3077
    31  CREATE INDEX idx_appointment_room_time ON Appointment (exam_room_id, status, appointment_time);
     78{{{CREATE INDEX idx_appointment_room_time ON Appointment (exam_room_id, status, appointment_time);}}}
    3279
    3380== Подобрување на операцијата update по креирање на индекс ==
    3481
    35 [[Image(update2.png)]]
     82{{{
     83QUERY PLAN
     84
     85Update on appointment  (cost=12127.90..217451.72 rows=0 width=0) (actual time=0.196..0.197 rows=0 loops=1)
     86  ->  Bitmap Heap Scan on appointment  (cost=12127.90..217451.72 rows=403058 width=14) (actual time=0.053..0.059 rows=2 loops=1)
     87        Recheck Cond: ((exam_room_id = 1) AND ((status)::text = 'Scheduled'::text))
     88        Heap Blocks: exact=2
     89        ->  Bitmap Index Scan on idx_appointment_room_time  (cost=0.00..12027.14 rows=403058 width=0) (actual time=0.039..0.039 rows=2 loops=1)
     90              Index Cond: ((exam_room_id = 1) AND ((status)::text = 'Scheduled'::text))
     91
     92Planning Time: 0.110 ms
     93
     94Execution Time: 0.244 ms
     95}}}
    3696
    3797== Останати погледи ==