Re: Very specialised query

On Mon, 30 Mar 2009, Віталій Тимчишин wrote:
> select
> case when n == 1 then id1 else id2 end,
> case when n == 2 then id1 else id2 end
>
> from (
> SELECT
>    l1.id AS id1,
>    l2.id AS id2
> FROM
>    location l1,
>    location l2
> WHERE
>        l1.objectid = 228000093
>    AND l2.objectid = 228000093
>    AND l1.id <> l2.id
>    AND l1.start < l2.end
>    AND l1.end > l2.start
>    AND l1.start < l2.start) a, (values (1),(2)) b(n)

It is a nice idea. However, the planner gets the join the wrong way round:

select distinct
case when n = 1 then id1 else id2 end,
case when n = 1 then id2 else id1 end
FROM (
select
l1.id AS id1,
l2.id AS id2
FROM
location l1,
location l2
WHERE
l1.id <> l2.id
AND l1.objectid = l2.objectid
AND l1.start <= l2.end
AND l2.start <= l1.end
AND l1.start <= l2.start
) AS a,
(values (1), (2)) b(n);

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=7366497963.75..7637346831.94 rows=36113182426 width=12)
(actual time=1642178.623..2206678.691 rows=139606782 loops=1)
-> Sort (cost=7366497963.75..7456780919.81 rows=36113182426 width=12)
(actual time=1642178.619..1899057.147 rows=166377424 loops=1)
Sort Key: (CASE WHEN ("*VALUES*".column1 = 1) THEN l1.subjectid ELSE l2.subjectid END), (CASE WHEN ("*VALUES*".column1 = 1) THEN l2.subjectid ELSE l1.subjectid END)
Sort Method: external merge Disk: 3903272kB
-> Nested Loop (cost=0.00..592890483.66 rows=36113182426 width=12)
(actual time=85.333..984211.011 rows=166377424 loops=1)
-> Values Scan on "*VALUES*" (cost=0.00..0.03 rows=2 width=4)
(actual time=0.002..0.008 rows=2 loops=1)
-> Nested Loop (cost=0.00..25596373.62 rows=18056591213 width=8)
(actual time=42.684..322743.335 rows=83188712 loops=2)
Join Filter: ((l1.subjectid <> l2.subjectid) AND (l1.intermine_start <= l2.intermine_end))
-> Seq Scan on location l1
(cost=0.00..78076.79 rows=3490079 width=16)
(actual time=0.008..3629.672 rows=3490079 loops=2)
-> Index Scan using location_test_obj_start on location l2
(cost=0.00..3.89 rows=152 width=16)
(actual time=0.005..0.038 rows=25 loops=6980158)
Index Cond: ((l2.objectid = l1.objectid) AND (l2.intermine_start <= l1.intermine_end) AND (l1.intermine_start <= l2.intermine_start))
Total runtime: 2339619.383 ms

The outer nested join has the VALUES as the main loop, and the complicated
join as the leaf. So, the complicated overlap-finding join gets run twice.

Oh, there's also the great big sort and unique, but I think I can get rid
of that.

Matthew

--
Contrary to popular belief, Unix is user friendly. It just happens to be
very selective about who its friends are. -- Kyle Hearn