Getting rid of UNION

From: Viktor Rosenfeld <listuser36(at)googlemail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Getting rid of UNION
Date: 2009-10-22 18:24:34
Message-ID: 20091022182434.GB25752@kyle
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Bierbryer, Andrew 2009-10-22 18:43:15 Right Join Question
Previous Message Greg Stark 2009-10-22 18:20:09 Re: Fwd: Reversing flow of WAL shipping