# Qserv joins between director tables using match tables

XMLWordPrintable

## Details

• Type: Bug
• Status: To Do
• Resolution: Unresolved
• Fix Version/s: None
• Component/s:
• Labels:
None
• Story Points:
6
• Epic Link:
• Team:
Data Access and Database

## Description

 select blah from dbs.source as s, dbd.detection as d, dbm.neighbour as n where s.sourceID=n.sourceID and d.detectionID=n.detectionIDI getERROR 4110    (Proxy): Query processing error: QI=?: Failed to instantiate query: AnalysisError:Query involves partitioned table joins that Qserv does not know how to evaluate using only partition-local data

## Activity

Vaikunth Thukral created issue -
Vaikunth Thukral made changes -
Field Original Value New Value
Epic Link DM-16792 [ 236150 ]
Fritz Mueller made changes -
 Epic Link DM-16792 [ 236150 ] DM-20061 [ 315688 ]
Hide
Gregory Dubois-Felsmann added a comment -

As part of implementing this, we need to think through at least two different cases:

• Where the two "primary" tables are completely independent (e.g., LSST and Gaia's main object tables) and are partitioned independently, perhaps with overlaps included in both partitionings. In this fully symmetrical case, we then need to understand how to partition a neighbor table, and how to ensure Qserv uses it correctly.
• Where the two "primary" tables have a relationship like "Object" to "Source" - they represent independent detections, but stemming from the same underlying experiment, and with coordinates computed in the same astrometric frame. The UK group's example in the associated C.L.O. post and in other communications about their Qserv tests seems to be more of this type. The difference from the "symmetric" case is that, at least tentatively, LSST was not planning to include overlaps in the partitioning of the Source table.

It is not obvious that in the end the neighbor tables in these two types of cases need any different underlying handling, but it still has to be thought through carefully before implementation.

Show
Gregory Dubois-Felsmann added a comment - As part of implementing this, we need to think through at least two different cases: Where the two "primary" tables are completely independent (e.g., LSST and Gaia's main object tables) and are partitioned independently, perhaps with overlaps included in both partitionings. In this fully symmetrical case, we then need to understand how to partition a neighbor table, and how to ensure Qserv uses it correctly. Where the two "primary" tables have a relationship like "Object" to "Source" - they represent independent detections, but stemming from the same underlying experiment, and with coordinates computed in the same astrometric frame. The UK group's example in the associated C.L.O. post and in other communications about their Qserv tests seems to be more of this type. The difference from the "symmetric" case is that, at least tentatively, LSST was not planning to include overlaps in the partitioning of the Source table. It is not obvious that in the end the neighbor tables in these two types of cases need any different underlying handling, but it still has to be thought through carefully before implementation.
Hide
Nate Pease added a comment - - edited

Gregory Dubois-Felsmann helped me understand this issue better today in Slack, I'm pasting most of his explanation here for future reference:

Gregory Dubois-Felsmann:
Basically a neighbor table is what you get when you already have spatial tables a and b, and you compute and store back to the database as a new table the result of
SELECT a.id, b.id FROM a, b WHERE scisql_angsep(a.ra,a.decl,b.ra.b.decl) < (something) AND (perhaps some other conditions, like quality flag cuts)
In general this produces an N:M relation.
In some cases there might be a small number of additional columns in the neighbor table reflecting some assessments of the likelihood that objects that are near each other are actually related, or different manifestations of the same astrophysical source.
So, in a Qserv situation, if a and b are sharded, then you have to figure out how to shard the neighbor table itself as well, and how to deal with overlaps. (E.g., do both a and b have overlaps included in their sharding scheme? Or only, say, a?)
We could test this on an all-sky basis by trying to develop a neighbor table connecting Gaia (gaia_source) and AllWISE (allwise_p3as_psd).

Nate Pease:
so the neighbor table is the result of the join of a and b?

Gregory Dubois-Felsmann:
Yes, that’s right.
You could write:
SELECT a.id, b.id FROM a INNER JOIN b ON scisql_angsep(a.ra,a.decl,b.ra.b.decl) < (something) WHERE (perhaps some other conditions, like quality flag cuts)
equivalently.
The output of that is the “neighbor table”.
The UK folks’ point is that, at least in some situations, using such a table is substantially faster than performing all the floating-point computations involved in the angular separation calculation.
The thing about Qserv is that when it sees the scisql_angsep() it knows that the join is spatially restricted and therefore how to assign it to the worker nodes.
If you now try to do a science-user query that’s based on the precomputed neighbor table (called n, columns a_id and b_id), though, it looks like this:
SELECT (stuff from a), (stuff from b) FROM a INNER JOIN n ON a.id = n.a_id INNER JOIN b on b.id = n.b_id WHERE (bla, bla, bla)
This is basically the same operation as
SELECT (stuff from a), (stuff from b) FROM a INNER JOIN b ON scisql_angsep(a.ra,a.decl,b.ra.b.decl) < (something) WHERE (bla, bla, bla)
but without the trigonometry.
The double-join with the n table, however, cannot prima facie be recognized by Qserv as partionable, because in theory any row in a could be related to any row in b.
new messages
So there has to be some logic in Qserv to say “I trust that, when n was ingested, it was validated as containing only relationships that don’t cross shards outside their overlap radius”, and then to apply the double join locally at shard level, and then to remove duplicates across shards successfully.
I don’t really know how hard that will be.
We do have feedback from the LSP review last April about the desire for neighbor tables (also known as “match tables” or “bridge tables”).

Show
Nate Pease added a comment - - edited Gregory Dubois-Felsmann helped me understand this issue better today in Slack, I'm pasting most of his explanation here for future reference: Gregory Dubois-Felsmann: Basically a neighbor table is what you get when you already have spatial tables a and b, and you compute and store back to the database as a new table the result of SELECT a.id, b.id FROM a, b WHERE scisql_angsep(a.ra,a.decl,b.ra.b.decl) < (something) AND (perhaps some other conditions, like quality flag cuts) In general this produces an N:M relation. In some cases there might be a small number of additional columns in the neighbor table reflecting some assessments of the likelihood that objects that are near each other are actually related, or different manifestations of the same astrophysical source. So, in a Qserv situation, if a and b are sharded, then you have to figure out how to shard the neighbor table itself as well, and how to deal with overlaps. (E.g., do both a and b have overlaps included in their sharding scheme? Or only, say, a?) We could test this on an all-sky basis by trying to develop a neighbor table connecting Gaia (gaia_source) and AllWISE (allwise_p3as_psd). Nate Pease: so the neighbor table is the result of the join of a and b? Gregory Dubois-Felsmann: Yes, that’s right. You could write: SELECT a.id, b.id FROM a INNER JOIN b ON scisql_angsep(a.ra,a.decl,b.ra.b.decl) < (something) WHERE (perhaps some other conditions, like quality flag cuts) equivalently. The output of that is the “neighbor table”. The UK folks’ point is that, at least in some situations, using such a table is substantially faster than performing all the floating-point computations involved in the angular separation calculation. The thing about Qserv is that when it sees the scisql_angsep() it knows that the join is spatially restricted and therefore how to assign it to the worker nodes. If you now try to do a science-user query that’s based on the precomputed neighbor table (called n, columns a_id and b_id), though, it looks like this: SELECT (stuff from a), (stuff from b) FROM a INNER JOIN n ON a.id = n.a_id INNER JOIN b on b.id = n.b_id WHERE (bla, bla, bla) This is basically the same operation as SELECT (stuff from a), (stuff from b) FROM a INNER JOIN b ON scisql_angsep(a.ra,a.decl,b.ra.b.decl) < (something) WHERE (bla, bla, bla) but without the trigonometry. The double-join with the n table, however, cannot prima facie be recognized by Qserv as partionable, because in theory any row in a could be related to any row in b. new messages So there has to be some logic in Qserv to say “I trust that, when n was ingested, it was validated as containing only relationships that don’t cross shards outside their overlap radius”, and then to apply the double join locally at shard level, and then to remove duplicates across shards successfully. I don’t really know how hard that will be. We do have feedback from the LSP review last April about the desire for neighbor tables (also known as “match tables” or “bridge tables”).
Gregory Dubois-Felsmann made changes -
 Link This issue relates to DM-20837 [ DM-20837 ]
Nate Pease made changes -
 Epic Link DM-20061 [ 315688 ] DM-22397 [ 427072 ]
Fritz Mueller made changes -
 Epic Link DM-22397 [ 427072 ] DM-25261 [ 435605 ]

## People

• Assignee:
Fritz Mueller
Reporter:
Vaikunth Thukral
Watchers:
Fritz Mueller, Gregory Dubois-Felsmann, Nate Pease, Vaikunth Thukral
• Votes:
0 Vote for this issue
Watchers:
4 Start watching this issue

## Dates

• Created:
Updated: