title: How to optimize join and sub-query in pgSQL
body: <p>I've 2 tables in PostgreSQL, Schema ==> <b>sts</b></p>
  
  <ol>
  <li><code>product_price_log</code> contains columns <code>product_name</code> (character varying), K (numeric), <code>sale_date</code> (date)</li>
  <li><code>product_transaction</code> contains columns <code>product_name</code> (character varying), <code>price</code> (numeric), <code>sale_date</code> (date)</li>
  </ol>
  
  <p>I want to get json data for <code>sale_date = '2018-01-15' and '2018-01-01'</code>.</p>
  
  <p>Product <b>price</b> should be from <code>product_price_log</code> table and sale data from <code>product_transaction</code> table for summarized report.</p>
  
  <p>this my query:</p>
  
  <pre><code>(SELECT 
      json_agg(j.*)
  from(
      select 
          a.sale_date, a.product_name, sum(a.quantity) as quantity, sum( a.quantity * b.price) as total_sale
      from(
          select
              sale_date, product_name, quantity
          from 
              sts.product_transaction p
          where 
          sale_date = '2018-01-15' or sale_date = '2018-01-01' and quantity &gt; 0
      )a
      inner join (
  
          select * from(
            select product_name, price, sale_date from sts.product_price_log where sale_date &lt;= '2018-01-15' order by sale_date desc limit 1
              union all
            select product_name, price, sale_date from sts.product_price_log where sale_date &lt;= '2018-01-01' order by sale_date desc limit 1
          )a order by sale_date
  
      )r on (a.product_name = r.product_name and a.sale_date = r.sale_date)
      group by 
          sale_date,
          product_name
  )j)
  ;
  </code></pre>
  
  <pre class="lang-none prettyprint-override"><code>QUERY PLAN
  Aggregate  (cost=230444.51..230444.52 rows=1 width=28)
    -&gt;  Subquery Scan on j  (cost=230290.98..230444.50 rows=1 width=28)
    -&gt;  GroupAggregate  (cost=230290.98..230444.49 rows=1 width=20)
  </code></pre>
  
  <p>How to I reduce time cost or optimize this query?</p>