Thread: Getting rid of UNION

Hi,

the following query takes 13 seconds to run vs. 31 milliseconds for an
(almost) equivalent query using UNION.  The main penalty comes from two
nestloops in the plan (http://explain.depesz.com/s/2o).

Is this approach feasable and if so, what am I doing wrong?

Also, is there a shorter idiom for the construction of the alternative "table"?

Here's the query without UNION:

  SELECT DISTINCT
      alternative.index,
      node_v1.id AS id1,
      CASE alternative.index
        WHEN 1 THEN NULL
        WHEN 2 THEN node_v2.id
      END AS id2
  FROM
      (SELECT 1 AS index UNION SELECT 2 AS index) AS alternative,
      node_v AS node_v1,
      node_v AS node_v2
  WHERE
      (
        alternative.index = 1 AND
        node_v1.span ~=~ 'der' AND
        node_v2.id = 7 -- guaranteed to exist in the DB, without this line the query needs 2 minutes (node_v2 cross
product)
      ) OR (
        alternative.index = 2 AND
        node_v1.span ~=~ 'das' AND
        node_v1.text_ref = node_v2.text_ref AND
        node_v1.right_token = node_v2.left_token - 1 AND
        node_v2.token_index IS NOT NULL
      )
  ;

And here's the query with UNION.

  SELECT DISTINCT
      node_v1.id AS id1,
      NULL::numeric AS id2
  FROM
      node_v AS node_v1
  WHERE
      node_v1.span ~=~ 'der'

  UNION SELECT DISTINCT
      node_v1.id AS id1,
      node_v2.id AS id2
  FROM
      node_v AS node_v1,
      node_v AS node_v2
  WHERE
      node_v1.span ~=~ 'das' AND
      node_v1.text_ref = node_v2.text_ref AND
      node_v1.right_token = node_v2.left_token - 1 AND
      node_v2.token_index IS NOT NULL
  ;

Cheers,
Viktor