Индекси и оптимизација на прашалници

Индекс idx_appointment_room_time

• Погледот View_Free_Upcoming_Slots се филтрира според exam_room_id и се користи за прикажување на слободни термини во една соба за преглед. Погледот овозможува увид на временски интервали во наредните 24 часа почнувајќи од моменталното време, и изведениот интервал е 30 минути - проценка на времетраење на еден преглед.

• Иницијалното време на извршување е 492.828ms. Ова е прифатливо време.

EXPLAIN ANALYZE SELECT * FROM View_Free_Upcoming_Slots WHERE exam_room_id = 1;

QUERY PLAN
Merge Right Join  (cost=249312.60..251333.27 rows=980 width=20) (actual time=487.591..492.755 rows=49 loops=1)
  Merge Cond: (ap.appointment_time = ts.slot_time)
  ->  Sort  (cost=249234.46..250242.10 rows=403054 width=12) (actual time=487.433..492.584 rows=0 loops=1)
        Sort Key: ap.appointment_time
        Sort Method: quicksort  Memory: 25kB
        ->  Gather  (cost=1000.00..204817.40 rows=403054 width=12) (actual time=487.420..492.570 rows=0 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  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)
                    Filter: ((exam_room_id = 1) AND (status)::text = 'Scheduled'::text)
                    Rows Removed by Filter: 333333
  ->  Sort  (cost=78.13..80.63 rows=1000 width=20) (actual time=0.155..0.159 rows=49 loops=1)
        Sort Key: ts.slot_time
        Sort Method: quicksort  Memory: 26kB
        ->  Nested Loop  (cost=0.29..28.30 rows=1000 width=20) (actual time=0.052..0.060 rows=49 loops=1)
              ->  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)
                    Index Cond: (exam_room_id = 1)
                    Filter: is_available
              ->  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)

Planning Time: 1.971 ms
Execution Time: 492.828 ms

• Времето на извршување на insert изнесува 40.213ms. Тоа време е задоволително.

EXPLAIN ANALYZE
INSERT INTO Appointment (appointment_id, appointment-time, status, exam_room_id) 
VALUES (10000001, NOW() + INTERVAL '2 hours', 'Scheduled', 1);

QUERY PLAN

Insert on appointment  (cost=0.00..0.02 rows=0 width=0) (actual time=28.065..28.066 rows=0 loops=1)
  ->  Result  (cost=0.00..0.02 rows=1 width=500) (actual time=0.007..0.007 rows=1 loops=1)

Planning Time: 0.053 ms

Trigger for constraint appointment_pet_id_fkey: time=11.548 calls=1
Trigger for constraint appointment_exam_room_id_fkey: time=0.487 calls=1
Trigger for constraint appointment_receptionist_id_fkey: time=0.080 calls=1

Execution Time: 40.213 ms

• Времето на извршување на update изнесува 2137.049ms. Тоа време можеме да го подобриме со индекс, бидејќи овој view би бил користен повеќе пати на ден при закажување нови прегледи.

EXPLAIN ANALYZE
UPDATE Appointment SET appointment_time = appointment_time + INTERVAL '30 minutes' 
WHERE exam_room_id = 1 AND status = 'Scheduled';

QUERY PLAN

Update on appointment  (cost=0.00..252019.64 rows=0 width=0) (actual time=2136.984..2136.986 rows=0 loops=1)
  ->  Seq Scan on appointment  (cost=0.00..252019.64 rows=403054 width=14) (actual time=1938.264..1938.264 rows=0 loops=1)
        Filter: ((exam_room_id = 1) AND ((status)::text = 'Scheduled'::text))
        Rows Removed by Filter: 10000000

Planning Time: 0.090 ms

Execution Time: 2137.049 ms

Креирање на индексот

CREATE INDEX idx_appointment_room_time ON Appointment (exam_room_id, status, appointment_time);

Подобрување на операцијата update по креирање на индекс

QUERY PLAN

Update on appointment  (cost=12127.90..217451.72 rows=0 width=0) (actual time=0.196..0.197 rows=0 loops=1)
  ->  Bitmap Heap Scan on appointment  (cost=12127.90..217451.72 rows=403058 width=14) (actual time=0.053..0.059 rows=2 loops=1)
        Recheck Cond: ((exam_room_id = 1) AND ((status)::text = 'Scheduled'::text))
        Heap Blocks: exact=2
        ->  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)
              Index Cond: ((exam_room_id = 1) AND ((status)::text = 'Scheduled'::text))

Planning Time: 0.110 ms

Execution Time: 0.244 ms

Останати погледи

• Останатите погледи во базата се извршуваат за време побрзо од 1 секунда така што не им е потребно индексирање и оптимизација. Дополнителни информации за анализа на погледите и нивната брзина може да погледнете во документацијата на следниот линк.

Линк до документација за оптимизација на прашалници​