title: PostgreSQL 9.6 query run more than 24 hours in nested loop
body: <p>I have a query that I run in my PostgreSQL 9.6 database and it runs for more than 24 hours and doesn't finish.</p>
  <p>My select consist from few joins :</p>
  <pre><code>SELECT a.inst_prod_id,
         product_id,
         nap_area2,
         nap_phone_num,
         nap_product_id,
         b.nap_makat_cd,
         b.nap_discount_num,
         b.nap_debt_line,
         nap_act_start_dt,
         nap_act_end_dt,
         b.row_lastmant_dttm,
         b.row_added_dttm
  FROM   ps_rf_inst_prod a,
         nap_ip_discount b
  WHERE  nap_crm_status  = 'C_04'
  AND    a.setid  || ''= 'SHARE'
  AND    b.setid  || ''= 'SHARE'
  AND    a.inst_prod_id = b.inst_prod_id
  AND    b.nap_makat_cd IN (SELECT term_code
           FROM   tv_finterm
           WHERE  pricing_method_code in ('2', '4')
           AND    start_date &lt;= b.nap_rishum_date
           AND    coalesce(end_date, to_date('01/01/2095','DD/MM/YYYY')) &gt;=
                  b.nap_rishum_date
           AND    coalesce(end_date, to_date('01/01/2095','DD/MM/YYYY')) !=
                  start_date)
  AND    b.nap_act_end_dt &gt; clock_timestamp()
  AND    TRUNC(b.nap_act_start_dt) &lt; TRUNC(b.nap_act_end_dt)
  AND (b.row_lastmant_dttm &gt;  to_date('01/01/2005','DD/MM/YYYY') OR
        b.nap_rishum_date &gt; to_date('01/01/2005','DD/MM/YYYY') OR
        b.row_added_dttm &gt; to_date('01/01/2005','DD/MM/YYYY'))
  AND   b.nap_discount_num IN (SELECT k.discount_line
           FROM   tv_discounts_details k
           WHERE  PERCENT IS NOT NULL
           AND    k.start_month = 1)
  AND (NOT EXISTS(SELECT /*+index(c TC_FINTERMS_I_SERVICE) */
                       1
                      FROM   tc_finterms c
                      WHERE  c.area = a.nap_area2
                      AND    c.phone = a.nap_phone_num
                      AND    c.term_code = b.nap_makat_cd
                      AND    deb_cred_line_no = b.nap_debt_line
                      AND (payment_end_date &gt; clock_timestamp())
                      AND    term_type = '2')
  OR  NOT EXISTS(SELECT 1
           FROM   ip_service_discounts
           WHERE  service_uid = (a.inst_prod_id)::integer
           AND    service_code = b.nap_makat_cd
           AND    discount_code = b.nap_discount_num
           AND (end_date IS NULL OR coalesce(discount_end_date, clock_timestamp() + interval '1 days') &gt; clock_timestamp())))
            and  b.nap_purch_instprod  = ' ';
  </code></pre>
  <p>Before trying to work on performance I checked locks and nothing returned :</p>
  <pre><code>=# select a1.query as blocking_query, a2. query as waiting_query, 
          t.schemaname ||'.'||t.relname as locked_table from pg_stat_activity            
          a1 join pg_locks p1 on a1. pid = p1.pid and p1.granted join pg_locks 
          p2 on p1.relation = p2.relation and not p2.granted join 
          pg_stat_activity a2 on a2. pid = p2.pid join pg_stat_all_tables t on 
           p1.relation = t.relid;
          blocking_query | waiting_query | locked_table
          ----------------+---------------+--------------
       (0 rows)
  </code></pre>
  <p>I checked the explain plan of my query :</p>
  <pre><code> Nested Loop Semi Join  (cost=0.43..7565655389.26 rows=1 width=93)
     Join Filter: (b.nap_discount_num = (k.discount_line)::numeric)
     -&gt;  Nested Loop  (cost=0.43..7565653159.07 rows=2 width=93)
           -&gt;  Seq Scan on ps_rf_inst_prod a  (cost=0.00..4337158.91     rows=40452 width=41)
                 Filter: (((nap_crm_status)::text = 'C_04'::text) AND     (((setid)::text || ''::text) = 'SHARE'::text))
           -&gt;  Index Scan using ps_nap_ip_discount on nap_ip_discount b      (cost=0.43..186920.69 rows=1 width=60)
                 Index Cond: ((inst_prod_id)::text = (a.inst_prod_id)::text)
                 Filter: (((nap_purch_instprod)::text = ' '::text) AND     (nap_act_end_dt &gt; clock_timestamp()) AND (((setid)::text || ''::text) = 'SHARE'::text) AND (trunc(nap_act_start_dt, 'DDD'::text) &lt; trunc(nap_act_end_dt, 'DDD'::text)) AND ((row_lastmant_dttm &gt; to_date('01/01/2005'::text, 'DD/MM/YYYY'::text)) OR (nap_rishum_date &gt;     to_date('01/01/2005'::text, 'DD/MM/YYYY'::text)) OR (row_added_dttm &gt; to_date('01/01/2005'::text, 'DD/MM/YYYY'::text))) AND ((NOT (SubPlan 2)) OR (NOT (SubPlan 3))) AND (SubPlan 1))
                 SubPlan 2
                   -&gt;  Index Scan using tc_finterms_ix1 on tc_finterms c (cost=0.56..8.60 rows=1 width=0)
                     Index Cond: (((area)::text = (a.nap_area2)::text) AND ((phone)::text = (a.nap_phone_num)::text))
                     Filter: (((term_type)::text = '2'::text) AND     (payment_end_date &gt; clock_timestamp()) AND ((term_code)::numeric =     b.nap_makat_cd) AND (deb_cred_line_no = (b.nap_debt_line)::double     precision))
                 SubPlan 3
                   -&gt;  Index Scan using ip_service_discounts_pkey on     ip_service_discounts  (cost=0.56..10.78 rows=1 width=0)
                         Index Cond: (service_uid = a.inst_prod_id)::integer)
                         Filter: (((service_code)::numeric = b.nap_makat_cd)     AND ((discount_code)::numeric = b.nap_discount_num) AND ((end_date IS NULL) OR (COALESCE((discount_end_date)::timestamp with time zone,     (clock_timestamp() + '1 day'::interval)) &gt; clock_timestamp())))
                    SubPlan 1
                   -&gt;  Bitmap Heap Scan on tv_finterm  (cost=2290.83..17301.61     rows=26907 width=4)
                         Recheck Cond: (((pricing_method_code)::text = ANY     ('{2,4}'::text[])) AND (start_date &lt;= b.nap_rishum_date))
                         Filter: ((COALESCE(end_date,     (to_date('01/01/2095'::text, 'DD/MM/YYYY'::text))::timestamp without time zone) &gt;= b.nap_rishum_date) AND (COALESCE(end_date, (to_date('01/01/2095'::text,     'DD/MM/YYYY'::text))::timestamp without time zone) &lt;&gt; start_date))
                         -&gt;  Bitmap Index Scan on index_test_mariel      (cost=0.00..2284.11 rows=81126 width=0)
                               Index Cond: (((pricing_method_code)::text = ANY     ('{2,4}'::text[])) AND (start_date &lt;= b.nap_rishum_date))
     -&gt;  Materialize  (cost=0.00..1407.38 rows=43933 width=4)
           -&gt;  Seq Scan on tv_discounts_details k  (cost=0.00..1187.71 rows=43933 width=4)
                 Filter: ((percent IS NOT NULL) AND (start_month = 1))
  (25 rows)
  </code></pre>
  <p>I run <code>vacuum analyze database</code> before running the query. Some info about the tables :</p>
  <pre><code>ps_rf_inst_prod - 32G 
  nap_ip_discount-1G 
  tc_finterms - 6G
  tv_finterm - 100M
  TV_FINTERM -  1G
  </code></pre>
  <p>This query is part of an app that I migrated from Oracle to PostgreSQL. I don't want to change the query much, looking for a way to change the plan to make it faster. I have indexes on ps_rf_inst_prod, when I delete the pipelines in :</p>
  <pre><code>  AND    a.setid  || ''= 'SHARE'
    AND    b.setid  || ''= 'SHARE'
  </code></pre>
  <p>The plan is changing and it uses indexes on ps_rf_inst_prod but it costs more and the performance is worse.</p>