From: Віталій Тимчишин <tivv00@gmail.com>
To: Matthew Wakeling <matthew@flymine.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Very specialised query
Date: Mon, 30 Mar 2009 19:14:30 +0300
Message-ID: <331e40660903300914x1c7747d0j94be7addeb2b31f5@mail.gmail.com> (raw)
In-Reply-To: <alpine.DEB.2.00.0903301618190.21772@aragorn.flymine.org>
References: <alpine.DEB.2.00.0903261313290.21772@aragorn.flymine.org>
<331e40660903270936u5db00807sb1169df66443ade3@mail.gmail.com>
<alpine.DEB.2.00.0903271708080.21772@aragorn.flymine.org>
<2298.1238179393@sss.pgh.pa.us>
<alpine.DEB.2.00.0903301618190.21772@aragorn.flymine.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com, matthew@flymine.org
Subject: Re: Very specialised query
In-Reply-To: <331e40660903300914x1c7747d0j94be7addeb2b31f5@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox