Below query is getting slower as I have used the case statement in having clause and the data is very huge, so below query is taking time for fetching the such huge data for overcoming this I need some alternatiive solution instead of the Having clause with case statement.
Explanation about the query,
I need to fetch the data by some types with condtions like I have three types Flat Amount(1), Flat amount of rent(2) and percentage of rent(3) on that basis I need to do the SUM of the charges with condtions like if type is 2 then fetch the record with charge type is 2 and charge code is 3 like that. Please look into the below query and suggest me alternative solution is there,
SELECT
n.id,
ro.id,
n.notice_amount,
n.notice_type_id,
SUM ( c.charge_amount ) AS charge_amount
FROM
notification n
JOIN notification_doc_groups ndg ON ( ndg.notice_id = n.id AND ndg.is_published = TRUE )
JOIN properties p ON ( p.id = ndg.property_id )
JOIN customer_details cd ON ( cd.notification_id = n.id )
JOIN rent_out ro ON ( ro.property_id = p.id )
JOIN charges c ON ( c.rent_out_id = ro.id )
WHERE
( ( n.notice_type_id = 1
AND c.charge_amount > 0
AND c.charge_type_id = 2 )
OR ( n.notice_type_id = 2
AND c.charge_type_id = 2
AND c.charge_code = 3 )
OR ( n.notice_type_id = 3
AND c.scheduled_id IS NOT NULL ) )
GROUP BY
n.id,
ro.id,
n.notice_type_id,
n.notice_amount
HAVING
CASE
WHEN n.notice_type_id = 1 THEN ( ( sum ( c.charge_amount ) >= n.notice_amount ) )
WHEN n.notice_type_id = 2 THEN sum ( c.charge_amount ) >= sum ( c.charge_amount ) * ( n.notice_amount / 100 ) -- or( )
WHEN n.notice_type_id = 3 THEN sum ( c.charge_amount ) >= sum ( c.charge_amount ) * ( n.notice_amount / 100 )
END;
Thanks
Can you try this:
HAVING (n.notice_type_id = 1 AND sum ( c.charge_amount ) >= n.notice_amount)
OR ( n.notice_type_id IN (2,3) AND n.notice_amount / 100 >=1)
My guess it that the planner can't optimize for the case so try an array:
having (array[
sum ( c.charge_amount ) >= n.notice_amount,
sum ( c.charge_amount ) >= sum ( c.charge_amount ) * ( n.notice_amount / 100 ),
sum ( c.charge_amount ) >= sum ( c.charge_amount ) * ( n.notice_amount / 100 )
]::bool[])[n.notice_type_id]
You can separate out the three different cases and use a union to join them. It shouldn't make a difference in the results but might make it easier for the optimizer. If nothing else you'll be able to run each separate notice type and see if one is causing the slowdown.
I am a bit confused about the condition on notice types 2 and 3 though:
sum ( c.charge_amount ) >= sum ( c.charge_amount ) * ( n.notice_amount / 100 )
It looks like you don't need to check the charge amount at all, you can just find entries where the notice_amount is 100 or less.
SELECT
n.id,
ro.id,
n.notice_amount,
n.notice_type_id,
SUM ( c.charge_amount ) AS charge_amount
FROM
notification n
JOIN notification_doc_groups ndg ON ( ndg.notice_id = n.id AND ndg.is_published = TRUE )
JOIN properties p ON ( p.id = ndg.property_id )
JOIN rent_out ro ON ( ro.property_id = p.id )
JOIN charges c ON ( c.rent_out_id = ro.id )
WHERE
n.notice_type_id = 3 AND c.scheduled_id IS NOT NULL
GROUP BY
n.id,
ro.id,
n.notice_type_id,
n.notice_amount
HAVING
sum ( c.charge_amount ) >= sum ( c.charge_amount ) * ( n.notice_amount / 100 )
UNION ALL
SELECT
n.id,
ro.id,
n.notice_amount,
n.notice_type_id,
SUM ( c.charge_amount ) AS charge_amount
FROM
notification n
JOIN notification_doc_groups ndg ON ( ndg.notice_id = n.id AND ndg.is_published = TRUE )
JOIN properties p ON ( p.id = ndg.property_id )
JOIN rent_out ro ON ( ro.property_id = p.id )
JOIN charges c ON ( c.rent_out_id = ro.id )
WHERE
n.notice_type_id = 2 AND c.charge_type_id = 2
GROUP BY
n.id,
ro.id,
n.notice_type_id,
n.notice_amount
HAVING
sum ( c.charge_amount ) >= sum ( c.charge_amount ) * ( n.notice_amount / 100 )
UNION ALL
SELECT
n.id,
ro.id,
n.notice_amount,
n.notice_type_id,
SUM ( c.charge_amount ) AS charge_amount
FROM
notification n
JOIN notification_doc_groups ndg ON ( ndg.notice_id = n.id AND ndg.is_published = TRUE )
JOIN properties p ON ( p.id = ndg.property_id )
JOIN rent_out ro ON ( ro.property_id = p.id )
JOIN charges c ON ( c.rent_out_id = ro.id )
WHERE
n.notice_type_id = 1 AND c.charge_amount > 0 AND c.charge_type_id = 2
GROUP BY
n.id,
ro.id,
n.notice_type_id,
n.notice_amount
HAVING
sum ( c.charge_amount ) >= n.notice_amount