JOIN commands can be improved to execute in less time

[eblur]

How could the content be improved?

I was running into some problems using the JOIN queries in Episode 6. The query would take hours and simply time out. This could be because when the JOIN command is run, for example, between gaiadr2.source_id and panstarrs1_best_neighbor.source_id, it is perhaps evaluated for the entire database before filtering for ra and dec (Source: ChatGPT -- so take explanation with a grain of salt).

I eventually figured out how to create subqueries and how to force the order of the queries so that only the filtered Gaia results were considered during the join with panstarrs1_best_neighbor. However, the operation still seemed to take over an hour. So in addition to that, I found that filtering out just the columns needed from each table (using another subquery) and using the OFFSET 0 modifier in order to to force the order of operations (see Gaia tutorials on combining tables) reduced the query time from hours (or completely stalling) to 30 seconds or less.

Here is an example, which could replace the neighbours_query string in the tutorial:

"""SELECT 
gaia.source_id, gaia.ra, gaia.dec, gaia.pmra, gaia.pmdec, 
best.source_id, best.original_ext_source_id,
best.best_neighbour_multiplicity, best.number_of_mates
FROM (
  SELECT source_id, ra, dec, pmra, pmdec
  FROM gaiadr2.gaia_source
  WHERE 1=CONTAINS(
      POINT(ra, dec),
      CIRCLE(88.8, 7.4, 0.08333333)
      )
  OFFSET 0) as gaia
JOIN (
        SELECT source_id, original_ext_source_id, best_neighbour_multiplicity, number_of_mates
        FROM gaiadr2.panstarrs1_best_neighbour
    OFFSET 0) AS best
    ON gaia.source_id = best.source_id
"""

This particular query ran in less than 10 seconds. It looks a lot more complicated, but it could make things easier for folks who are trying to execute this workshop in one day.

Which part of the content does your suggestion apply to?

Episode 6

[abostroem]

Thank you so much for this @eblur. I just taught this workshop at the AAS and encountered this problem. The issue seems to be the combination of JOIN and the ADQL CONTAINS in the same query. I contacted the Gaia Helpdesk and they are aware of the issue (they had seen other slow downs as a result of their DR4 optimization). They said they are working on the problem but don't have a time frame for a solution (they are still diagnosing it). The workaround I came up with was similar - to run the first select command and then upload that table to Gaia and JOIN to it without the CONTAINS statement.

I would like to discuss how to proceed with the curriculum advisory committee asap. I don't like having the lesson in a state that is unexecutable - but I also don't want to make a change that complicates the lesson for a temporary issue.

Regardless - I really appreciate the work you put into this and your contribution here so that others who encounter the issue can see your solution. I'll report back from the curriculum advisory committee.

[eblur]

Yeah, I didn't recall having the same problem in the past, so it makes sense that there was a change on the Gaia database side. I am having my students look through portions of the tutorial for reference to do another activity, which is what spurred me to work through it again. I now have fast queries working for all portions of Episode 6, and I'm happy to share my solutions if you decide to go the route of changing the site (or just want them as an option).