Quote:> And that's exactly what I can't understand: why is it soo harder for
> the SQL engine to perform the 2xJOINs requests than it is to perform
> 2 times a single JOIN request? In both cases I get the data I need.
> This makes me think that I missed something obvious, and that my
> first version (2xJOINs) is doing much more work than what I need it
> to do...
Then please do "explain select ..." or even "explain analyze select ..."
and post results to http://explain.depesz.com/ and refer them here.
Quote:> My fallback option would be to create a temporary table, and then
> populate it with the result of the two SELECTs above (done one after
> the other, possibly inside a single transaction), and then GROUP BY
> all entries to get my data... But is there really no way to perform
> the same job with one single magic SQL incantation?
Well, you proved that you *can* execute it with a single SELECT. It's
just not as quick (yet) as you expect. So you need to find out what's
Btw, since there a foreign key on the subnet.probe1 and subnet.probe2
you can (and probably should) use a regular join. No need for an outer
join because you know that every occurrence of subnet.probe1 and
subnet.probe2 has an entry in probes. So the query would be
JOIN probes AS val1 ON subnet.probe1 = val1.id
JOIN probes AS val2 ON subnet.probe2 = val2.id