sql - Query performing poorly (nested joins, lateral join) (PostgreSQL) -

i'm working on query touching 4 tables. schema drew in following er model: http://i.stack.imgur.com/ftscj.jpg

i'm trying write query return sites sum of file sizes not exceed storage limit of associated plan. in other words, i'd know sites able create new files, once there storage available yet.

the trick contracted plan can have many sites can have many files, once 1 site exceed limit, sites share same contracted plan must disabled well.

after many tries, got following sql:

select sites plans p inner join (   select cp.plan_id plan_id, cp.id contracted_plan_id, array_agg(s.id) sites, sum(total_size) total   contracted_plans cp   inner join sites s on cp.id = s.contracted_plan_id   left join lateral(     select sum(size) total_size     files f     f.site_id = s.id   ) agg on true   group cp.id, cp.plan_id ) total_per_contracted_plan on p.id = total_per_contracted_plan.plan_id total < p.storage_limit; 

currently sql seems working, notice it's not performing well.

i've populated database 200k contracted plans, 200k sites (one per contracted plan) , 3kk files (15 files per site). there 5 plans, i've associated each contracted plan random plan.

all pk , fk have index. i'm using postgresql, latest version.

performing explain analyze got this: http://chunk.io/f/01c42c8aba7b414dbd8bff0299fbe84b. took 4s finish seems excessive.

how can improve performance of sql?

fiddle - http://sqlfiddle.com/#!15/ce190/6/0

select distinct site_id   (select s.id site_id,                p.id plan_id,                p.storage_limit,                sum(f.size) over(partition s.id) tot_site           sites s           join files f             on f.site_id = s.id           join contracted_plans cp             on cp.id = s.contracted_plan_id           join plans p             on p.id = cp.plan_id) x  tot_site < storage_limit