Postgresql - LEFT JOIN LATERAL is too slow than subquery
<p>I have some difficulty using 'LEFT JOIN LATERAL' function with postgresql 9.5.</p>

<p>In my table, there are three columns of 'ID', 'DATE', 'CODE'. One person (ID) have multiple rows as below. The number of ID is 362 and total row number is about 2500000.</p>

<pre><code>ID   /  DATE     / CODE
1    /  20020101 / drugA
1    /  20020102 / drugA
1    /  20020103 / drugB
1    /  20020104 / drugA
1    /  20020105 / drugA
1    /  20020106 / drugB
1    /  20020107 / drugA
2    /  ...      / ...
</code></pre>

<p>I need to summarize information of drug A used between the first day and last day of drugB.</p>

<p>In the above case, only two rows should be remained for ID (1) [between 20020103 ~ 20020106; the period of drugB].</p>

<pre><code>1    /  20020104 / drugA
1    /  20020105 / drugA
</code></pre>

<p>To take this job, I write SQL code using 'LEFT LATERAL JOIN' as below.</p>

<pre><code>SELECT * FROM (SELECT ID, min(DATE) as start_date, max(DATE) as end_date from MAIN_TABLE WHERE CODE = 'drugA' GROUP BY ID) AA
LEFT JOIN LATERAL (SELECT ID, COUNT(ID) as no_tx, min(DATE) as fday_tx, max(DATE) lday_tx from MAIN_TABLE WHERE CODE = 'drugB' AND DATE &gt; AA.start_date AND DATE &lt; AA.end_date GROUP BY ID) as BB USING(ID);
</code></pre>

<p>There are only 362 person ID, but this postgresql code take about 2 mins.</p>

<p>It's too slow. Therefore, I tried another SQL code using subquery.</p>

<pre><code>SELECT * FROM (SELECT ID, min(DATE) as start_date, max(DATE) as end_date from MAIN_TABLE WHERE CODE ='drugA' GROUP BY ID) AA
LEFT JOIN (
       SELECT ID, COUNT(ID) as no_tx, min(DATE) as fday_tx, max(DATE) lday_tx FROM (SELECT ID, DATE, CODE FROM MAIN_TABLE) BB
            LEFT JOIN (SELECT ID, min(DATE) as start_date, max(DATE) as end_date from MAIN_TABLE WHERE CODE ='drugA' GROUP BY ID) CC USING (ID)
            WHERE CODE = 'drugB' and DATE &gt; start_date and DATE &lt; end_date GROUP BY ID
            ) DD USING (ID);
</code></pre>

<p>This code is not simple but very fast (take only 1.6 sec).</p>

<p>When I compare the explain of two codes, the second code use hash join, but the first code do not.</p>

<p>Can I get some hint to improve the first code with 'LEFT LATERAL JOIN' function more efficiently?</p>