Phase P3: QueryOptimization

Views

• View 1: daily_clinic_schedule - indexed
• View 2: pet_medical_history - indexed
• View 3: active_prescriptions_for_pet - indexed
• View 4: employee_performance_summary - materialized
• View 5: invoice_billing_details
• 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_attribute_value_treatment on treatment_attribute_value(treatment_id)
• idx_prescription_active_dates on prescription(date_end, date_start)
• idx_treatment_examination on treatment(examination_id)
• idx_treatment_date on treatment(date_treatment)
• idx_invoice_item_treatment on invoice_item(treatment_id)
• idx_invoice_owner on invoice(owner_id)
• idx_invoice_item_invoice_id on invoice_item(invoice_id, num_item)
• idx_payment_invoice on payment(invoice_id)
• idx_shop_item_stock on shop_item(stock)
• idx_shop_item_category_name on shop_item_category(name)

To improve performance even further, the view 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