Skip site navigation (1) Skip section navigation (2)

Re: Very specialised query

From: Віталій Тимчишин <tivv00(at)gmail(dot)com>
To: Matthew Wakeling <matthew(at)flymine(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Very specialised query
Date: 2009-03-30 16:14:30
Message-ID: 331e40660903300914x1c7747d0j94be7addeb2b31f5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi.

Look, what I did mean by "symmetric" is that you don't need to make second
part of query because you will get just same results simply by

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)

(I may miss some border cases like when l1.start=l2.start and/or
l1.end=l2.end, but this can be fixed by adding "=" to query).

Look,  You can have 4 types of intersections:
a)  1s 2s 2e 1e - 2 inside 1
b)  2s 1s 1e 2e - 1 inside 2 (symmetric to (a), if you have 1,2 from (a) you
can generate 2,1 for (b))
c)  1s 2s 1e 2e - 1 to the left of 2
d)  2s 1s 2e 1e - 2 to the left of 1 (symmetric to (c), if you have 1,2 from
(c) you can generate 2,1 for (d))

The query above gives you results for (a) and (c) and you don't need  any
second part - simply add "symmetric" results.

Correct me if I miss something.

Best Regards, Vitalii Tymchyshyn

In response to

Responses

pgsql-performance by date

Next:From: Matthew WakelingDate: 2009-03-30 16:22:15
Subject: Re: Very specialised query
Previous:From: Matthew WakelingDate: 2009-03-30 15:59:05
Subject: Re: Very specialised query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group