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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
