title: Postgres query doesn't execute when has WITH statement
body: <p>I'm applying <a href="https://github.com/thoughtbot/scenic" rel="nofollow noreferrer">scenic gem</a> for creating database view for Postgres database. I have a order (ops_orders) table with 73k records and order line item (ops_order_line_items) that belongs to order with 170k records.</p>
  
  <p>The problem is that the query has WITH statement executed too long so that it hangs indefinitely:</p>
  
  <pre><code>WITH 
  excluded_order_ids AS (
    SELECT ord.id
    FROM ops_orders ord
    WHERE NOT EXISTS (
      SELECT 1
      FROM ops_order_line_items oli
      WHERE oli.processing_status != 6 -- PENDING
        AND oli.order_id = ord.id
      )
  
    UNION ALL
  
    SELECT ord.id
    FROM ops_orders ord
    WHERE NOT EXISTS (
      SELECT 1
      FROM ops_order_line_items oli
      WHERE oli.processing_status != 1 -- FULFILLED
        AND oli.order_id = ord.id
      )
  
    UNION ALL
  
    SELECT ord.id
    FROM ops_orders ord
    WHERE NOT EXISTS (
      SELECT 1
      FROM ops_order_line_items oli
      WHERE oli.processing_status != 4 -- CANCELLED
        AND oli.order_id = ord.id
      )
  
    UNION ALL
  
    SELECT ord.id
    FROM ops_orders ord
    WHERE NOT EXISTS (
      SELECT 1
      FROM ops_order_line_items oli
      WHERE oli.line_item_type != 1 -- RESENT
        AND oli.order_id = ord.id
      )
  ),
  included_order_ids AS (
    SELECT ord.id
    FROM ops_orders ord
    WHERE NOT EXISTS (
      SELECT 1
      FROM ops_order_line_items oli
      WHERE oli.processing_status != 3 -- PENDING
        AND oli.deadline &gt; (current_date + 1)
        AND oli.order_id = ord.id
      )
  )
  
  SELECT oli.*
  FROM ops_order_line_items oli
  JOIN ops_purchase_line_items pli ON pli.id = oli.purchase_line_item_id
  JOIN ops_purchase_orders po ON po.id = pli.purchase_order_id
  JOIN ops_orders ord ON ord.id = oli.order_id
  WHERE ((oli.processing_status IN (0, 2) AND oli.deadline &lt;= (current_date + 3)) -- status: ?, ORDERED
    OR (oli.processing_status IN (5, 7) AND oli.deadline &lt;= current_date) -- status: DROPSHIP, PACKING
      OR (oli.processing_status IN (2) AND po.countdown_date &gt; po.created_at) -- status: FULFILLED
      OR (oli.order_id IN (SELECT id FROM included_order_ids)))
      AND oli.order_id NOT IN (SELECT id FROM excluded_order_ids)
  ORDER BY ord.order_number DESC, oli.created_at ASC
  </code></pre>
  
  <p>However, the query worked when I changed to:</p>
  
  <pre><code>WITH
  included_order_ids AS (
    SELECT ord.id
    FROM ops_orders ord
    WHERE NOT EXISTS (
      SELECT 1
      FROM ops_order_line_items oli
      WHERE oli.processing_status != 3 -- PENDING
        AND oli.deadline &gt; (current_date + 1)
        AND oli.order_id = ord.id
      )
  )
  SELECT oli.*
  FROM ops_order_line_items oli
  JOIN ops_purchase_line_items pli ON pli.id = oli.purchase_line_item_id
  JOIN ops_purchase_orders po ON po.id = pli.purchase_order_id
  JOIN ops_orders ord ON ord.id = oli.order_id
  WHERE ((oli.processing_status IN (0, 2) AND oli.deadline &lt;= (current_date + 3)) -- status: ?, ORDERED
    OR (oli.processing_status IN (5, 7) AND oli.deadline &lt;= current_date) -- status: DROPSHIP, PACKING
    OR (oli.processing_status IN (2) AND po.countdown_date &gt; po.created_at) -- status: FULFILLED    
    OR (oli.order_id IN (SELECT id FROM included_order_ids)))
    AND oli.order_id NOT IN (
      SELECT ord.id
      FROM ops_orders ord
      WHERE NOT EXISTS (
        SELECT 1
        FROM ops_order_line_items oli
        WHERE oli.processing_status != 6 -- PENDING
          AND oli.order_id = ord.id
        ))
    AND oli.order_id NOT IN (
      SELECT ord.id
      FROM ops_orders ord
      WHERE NOT EXISTS (
        SELECT 1
        FROM ops_order_line_items oli
        WHERE oli.processing_status != 1 -- FULFILLED
          AND oli.order_id = ord.id
        ))
    AND oli.order_id NOT IN (
      SELECT ord.id
      FROM ops_orders ord
      WHERE NOT EXISTS (
        SELECT 1
        FROM ops_order_line_items oli
        WHERE oli.processing_status != 4 -- CANCELLED
          AND oli.order_id = ord.id
        ))
    AND oli.order_id NOT IN (
      SELECT ord.id
      FROM ops_orders ord
      WHERE NOT EXISTS (
        SELECT 1
        FROM ops_order_line_items oli
        WHERE oli.line_item_type != 1 -- RESENT
          AND oli.order_id = ord.id
        ))
  ORDER BY ord.order_number DESC, oli.created_at ASC
  </code></pre>
  
  <p>Does the WITH statement in Postgres have bad performance ? I researched but don't find any documents mention about bad performance of WITH statement. Looking forward to helpful explanation.</p>