Getting rid of UNION

[Viktor Rosenfeld <listuser36@xxxxxxxxxxxxxx>]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general