title: Postgresql avoid nested loop with join
body: <p>Please help me to improve query performance if possible.</p>
  
  <p>I have following query   </p>
  
  <pre><code>    select
          s."CustomerCode",
          s."MaterialCode",
          fw."Name",
          fw."ReverseName",
          s."Uc"
      from
          "Sales" s
      left join
          "FiscalWeeks" fw on s."SalesDate" between fw."StartedAt" and fw."EndedAt"
  </code></pre>
  
  <p>And execution plan is</p>
  
  <pre><code>"Nested Loop Left Join  (cost=0.00..1439970.46 rows=8954562 width=40) (actual time=0.129..114889.581 rows=1492427 loops=1)"
  "  Join Filter: ((s."SalesDate" &gt;= fw."StartedAt") AND (s."SalesDate" &lt;= fw."EndedAt"))"
  "  Rows Removed by Join Filter: 79098631"
  "  Buffers: shared hit=3818 read=10884"
  "  -&gt;  Seq Scan on "Sales" s  (cost=0.00..29625.27 rows=1492427 width=26) (actual time=0.098..1216.287 rows=1492427 loops=1)"
  "        Buffers: shared hit=3817 read=10884"
  "  -&gt;  Materialize  (cost=0.00..1.81 rows=54 width=26) (actual time=0.001..0.034 rows=54 loops=1492427)"
  "        Buffers: shared hit=1"
  "        -&gt;  Seq Scan on "FiscalWeeks" fw  (cost=0.00..1.54 rows=54 width=26) (actual time=0.006..0.044 rows=54 loops=1)"
  "              Buffers: shared hit=1"
  "Planning time: 0.291 ms"
  "Execution time: 115840.838 ms"
  </code></pre>
  
  <p>I have following indexes</p>
  
  <pre><code>CREATE INDEX "Sales_SalesDate_idx" ON public."Sales" USING btree ("SalesDate");
  ADD CONSTRAINT "FiscalWeekUnique" EXCLUDE USING gist (daterange("StartedAt", "EndedAt", '[]'::text) WITH &amp;&amp;);
  </code></pre>
  
  <p>Postgresql version is</p>
  
  <pre><code>"PostgreSQL 9.5.0, compiled by Visual C++ build 1800, 32-bit"
  </code></pre>
  
  <p>vacuum analyze was performed</p>
  
  <p>I think that postgresql does not understand that for each row in Sales table exists only one row in table FiscalWeeks and use nested loop. How can I explain it?</p>
  
  <p>Thank you.</p>