title: Optimize the query (may be avoid nested loop)
body: <p>How can we optimize the following query:</p>
  
  <pre><code>select * 
  from program_infos pi
  join endeavour_organizations seller_organization on seller_organization.id = pi.supplier_id
  join endeavour_organizations obligor_organization on obligor_organization.id = pi.buyer_id
  join invoices i on pi.program_id = i.program_id
  join assets fa on fa.invoice_id = i.id and fa.owner_id=pi.fi_id
  join assets sa on sa.invoice_id = i.id and sa.owner_id=pi.supplier_id;
  </code></pre>
  
  <p>The corresponding Explain Analyze is :</p>
  
  <pre><code>Nested Loop  (cost=36.94..70919.65 rows=505 width=793) (actual time=0.263..1729.519 rows=267238 loops=1)
     -&gt;  Nested Loop  (cost=36.79..70806.58 rows=505 width=718) (actual time=0.261..1405.417 rows=267238 loops=1)
           Join Filter: ((i.id = fa.invoice_id) AND (pi.fi_id = fa.owner_id))
           Rows Removed by Join Filter: 400287
           -&gt;  Hash Join  (cost=36.37..69201.99 rows=2567 width=626) (actual time=0.255..772.895 rows=248735 loops=1)
                 Hash Cond: (pi.supplier_id = seller_organization.id)
                 -&gt;  Hash Join  (cost=27.52..68973.45 rows=15977 width=551) (actual time=0.202..672.442 rows=248735 loops=1)
                       Hash Cond: ((sa.owner_id = pi.supplier_id) AND (i.program_id = pi.program_id))
                       -&gt;  Merge Join  (cost=1.29..63781.02 rows=667525 width=288) (actual time=0.021..496.274 rows=667525 loops=1)
                             Merge Cond: (i.id = sa.invoice_id)
                             -&gt;  Index Scan using invoices_pkey on invoices i  (cost=0.42..27363.52 rows=249447 width=196) (actual time=0.004..60.598 rows=249440 loops=1)
                             -&gt;  Index Scan using index_assets_invoice on assets sa  (cost=0.42..27450.72 rows=667525 width=92) (actual time=0.014..147.276 rows=667525 loops=1)
                       -&gt;  Hash  (cost=20.09..20.09 rows=409 width=263) (actual time=0.176..0.176 rows=409 loops=1)
                             Buckets: 1024  Batches: 1  Memory Usage: 133kB
                             -&gt;  Seq Scan on program_infos pi  (cost=0.00..20.09 rows=409 width=263) (actual time=0.001..0.064 rows=409 loops=1)
                 -&gt;  Hash  (cost=5.60..5.60 rows=260 width=75) (actual time=0.049..0.049 rows=260 loops=1)
                       Buckets: 1024  Batches: 1  Memory Usage: 27kB
                       -&gt;  Seq Scan on endeavour_organizations seller_organization  (cost=0.00..5.60 rows=260 width=75) (actual time=0.006..0.019 rows=260 loops=1)
           -&gt;  Index Scan using index_assets_owner_invoice on assets fa  (cost=0.42..0.57 rows=4 width=92) (actual time=0.001..0.002 rows=3 loops=248735)
                 Index Cond: (invoice_id = sa.invoice_id)
     -&gt;  Index Scan using endeavour_organizations_pkey on endeavour_organizations obligor_organization  (cost=0.15..0.21 rows=1 width=75) (actual time=0.001..0.001 rows=1 loops=267238)
           Index Cond: (id = pi.buyer_id)
   Planning time: 3.194 ms
   Execution time: 1740.875 ms
  (24 rows)
  </code></pre>
  
  <p>Indexes are on <code>pi.program_id</code>, <code>pi.fi_id</code>, <code>pi.supplier_id</code>, <code>asset.invoice_id</code>, <code>i.program_id</code></p>
  
  <p>I am not able to understand why its doing nested loop. Please let me know if something else is needed.</p>