Optimizing bulk update with two inner joins and calculation
<p>So i have three tables:
products, offers, and offer_lines</p>

<p>offer_lines is a join table to make an has and belongs to many relation:</p>

<ul>
<li>products has_many offer_lines</li>
<li>offers has_many offer_lines</li>
</ul>

<p>offer_lines has a column called <strong>calculated_price</strong>. This is based on the offers table's column <strong>discount_percentage</strong> and the products table's column <strong>price</strong>.</p>

<p>I want to create a sql statement that can calculate the discounted price of a product based on the discount percentage stored in the offers table.</p>

<p>This is what i got so far:</p>

<pre><code>UPDATE offer_lines
SET discounted_price = (products.price - (products.price * offers.discount_percentage / 100))
FROM offer_lines AS o
  INNER JOIN products ON o.product_id = products.id
  INNER JOIN offers ON o.offer_id = offers.id
WHERE offer_lines.offer_id = 2;
</code></pre>

<p>This seems to work fine except for the fact that it takes about a minute to run.</p>

<p>Here is the explain:</p>

<pre><code>Update on offer_lines  (cost=77.16..1131.37 rows=10670 width=87)
  -&gt;  Hash Join  (cost=77.16..1131.37 rows=10670 width=87)
    Hash Cond: (o.product_id = products.id)
    -&gt;  Hash Join  (cost=9.44..836.90 rows=10670 width=77)
          Hash Cond: (o.offer_id = offers.id)
          -&gt;  Seq Scan on offer_lines o  (cost=0.00..620.74 rows=26674 width=14)
          -&gt;  Hash  (cost=9.38..9.38 rows=4 width=71)
                -&gt;  Nested Loop  (cost=0.29..9.38 rows=4 width=71)
                      -&gt;  Index Scan using index_offer_lines_on_offer_id on offer_lines  (cost=0.29..8.30 rows=1 width=53)
                            Index Cond: (offer_id = 13)
                      -&gt;  Seq Scan on offers  (cost=0.00..1.04 rows=4 width=18)
    -&gt;  Hash  (cost=62.88..62.88 rows=388 width=18)
          -&gt;  Seq Scan on products  (cost=0.00..62.88 rows=388 width=18)
</code></pre>

<p>Any ideas how i can make this run faster?</p>