Phase P3: QueryOptimization

Views

• View 1: daily_clinic_schedule - indexed
• View 2: pet_medical_history - indexed
• View 3: active_prescriptions_for_pet
• View 4: employee_performance_summary - materialized
• View 5: invoice_billing_details - indexed
• View 6: low_stock_shop_items

Indexes

To improve view performance, the following indexes were implemented:

• idx_appointment_date on appointment(date_appointment)
• idx_appointment_pet on appointment(pet_id)
• idx_examination_appointment on examination(appointment_id)
• idx_treatment_examination on treatment(examination_id)
• idx_invoice_owner on invoice(owner_id)
• idx_payment_invoice on payment(invoice_id)

To improve performance even further, view4: employee_performance_summary is materialized.

Detailed documentation

​https://develop.finki.ukim.mk/projects/pawcare/attachment/wiki/QueryOptimization/PawCare_Views_Indexes_Documentation.docx

Script

​https://develop.finki.ukim.mk/projects/pawcare/attachment/wiki/QueryOptimization/views.sql