lundi 9 février 2015

Postgres - Optimizing a View Query


I have a view which gets populated by approximately 150,000 records per day. The data from the view is consumed by another application. While reading the view, it takes almost 10 minutes, which is a long time. Can someone help me in optimizing my query?



CREATE OR REPLACE VIEW ccdb.ea_billing_data AS
SELECT bmr.bill_id,
b.mbc_bill_id,
b.consumer_number,
bmr.present_reading_date,
bmr.previous_reading_date,
b.bill_amt AS billed_amount,
b.paid_amt AS collected_amount,
b.total_consumption AS billed_unit,
b.bill_amt - COALESCE(b.arrear_collected, 0::numeric) AS actual_paid_amt,
ci.cin,
b.org_unit_id,
b.bill_type_code,
bmr.status,
ci.dtr_id,
b.source_system_id,
bmr.creation_dt
FROM ccdb.consumer_index_details ci JOIN ccdb.bills b ON b.cin = ci.cin
JOIN ccdb.bills_meter_reading bmr ON bmr.mbc_bill_id = b.mbc_bill_id
WHERE ci.r_apdrp_town_flag = 1
AND (b.bill_type_code::text = ANY (ARRAY['RgCC'::character varying::text, 'RqCC'::character varying::text, 'IB'::character varying::text, 'Normal'::character varying::text]))
AND (bmr.status = ANY (ARRAY[0, 1]))
AND (bmr.present_reading_date IS NOT NULL OR bmr.previous_reading_date IS NOT NULL)
AND bmr.present_reading_date IS NOT NULL
UNION
SELECT b1.bill_id,
b1.mbc_bill_id,
b.consumer_number,
bmr.present_reading_date,
bmr.previous_reading_date,
b1.bill_amt AS billed_amount,
b1.paid_amt AS collected_amount,
b1.total_consumption AS billed_unit,
b1.bill_amt - COALESCE(b1.arrear_collected, 0::numeric) AS actual_paid_amt,
ci.cin,
b1.org_unit_id,
b1.bill_type_code,
bmr.status,
ci.dtr_id,
b1.source_system_id,
bmr.creation_dt
FROM ccdb.consumer_index_details ci
JOIN ccdb.bills b ON b.cin = ci.cin
JOIN ccdb.bills b1 ON b.mbc_bill_id = b1.parent_bill_id
JOIN ccdb.bills_meter_reading bmr ON bmr.mbc_bill_id = b.mbc_bill_id
WHERE ci.r_apdrp_town_flag = 1
AND (b.bill_type_code::text = ANY (ARRAY['RgCC'::character varying::text, 'RqCC'::character varying::text, 'IB'::character varying::text, 'Normal'::character varying::text]))
AND (bmr.status = ANY (ARRAY[0, 1]))
AND (bmr.present_reading_date IS NOT NULL OR bmr.previous_reading_date IS NOT NULL)
AND bmr.present_reading_date IS NOT NULL
AND b.mbc_bill_id <> b1.mbc_bill_id;


I would like this view to be optimized for a better performance.





Aucun commentaire:

Enregistrer un commentaire