Page Comparison

...

1-- query on objects
SELECT objectId,ncand
FROM objects
WHERE ncand > 20
AND (latestgmag-maggmean)>2 order by jdmax desc

...

2-- specific object
SELECT objects.ncand, objects.ramean, objects.decmean, objects.jdmin, objects.maggmean,candidates.magpsf ,candidate.fid
FROM candidates, objects
WHERE objects.objectId = candidates.objectId
AND candidates.objectId ="ZTF17aabdaaq"

...

3-- specific object
SELECT *
FROM objects objectId
WHERE objectId="ZTF18acmzpbf"

...

4-- with huge list of objectIds
SELECT candidates.objectId, candidates.jd - 2400000.5 AS ago, candidates.magpsf, candidates.sigmapsf, candidates.fid
FROM candidates
WHERE candidates.objectID in ('ZTF18abebzog', 'ZTF18acbwghc', 'ZTF18acbujhw','ZTF18aamigmk', 'ZTF18abakjgq')

...

– Now reduced to 18 queries by removing what seem to be repeats.

– Removed ORDER BY from all queries. In a realtime pipeline the order is always time.

– Added LIMIT 10 where necessary, to all queries for the purposes of timing.

– Many queries have pointless joins with the candidates table that can be done without (see below).

– Those that have “real” joins with the candidates I have approximated as object queries. A good example is looking for lightcurves that have detections in a specific narrow time interval.

– Number 4 is asking for lightcurves of 47 objects, which is more suitable for jupyter/API interface

– Should we simply make subqueries impossible, as it is so easy to abuse?

• 1-- query on objects
  SELECT objectId,ncand
FROM objects
WHERE ncand > 20
AND (latestgmag-maggmean)>2 LIMIT 10

  • Executed in 0.01 seconds

• 2-- specific object
  SELECT objects.ncand, objects.ramean, objects.decmean, objects.jdmin, objects.maggmean,candidates.magpsf ,candidates.fid
FROM candidates, objects , sherlock_classifications
WHERE objects.objectId = candidates.objectId
AND objectscandidates.primaryId = sherlock_classifications.transient_object_id
AND objects.objectId in (u'ZTF18aavqmxr', u'ZTF18aaiugdp', u'ZTF18aaxddex', u'ZTF18aawcbyu', u'ZTF18aavlgby', u'ZTF18aavfhlv', u'ZTF18aainceg', u'ZTF18aaveejk', u'ZTF18aauesbd', u'ZTF18aavelyy', u'ZTF18aavqlnk', u'ZTF18aavefdz', u'ZTF18aasnjav', u'ZTF18aaxjkcm', u'ZTF18aaudxvh', u'ZTF18aauqlxd', u'ZTF18aaxnbfs', u'ZTF18aaxpkty', u'ZTF18aaxumvv', u'ZTF18aaxvbtx', u'ZTF17aadsntf', u'ZTF18aayeepw', u'ZTF18aavghok', u'ZTF18aayotqj', u'ZTF18aaywedv', u'ZTF18aaymybb', u'ZTF18aazmegj', u'ZTF17aabumwe', u'ZTF18aazvjuy', u'ZTF18aazwddf', u'ZTF17aabupib', u'ZTF18aazwdak', u'ZTF17aaapvpp', u'ZTF17aaarqox', u'ZTF18abbmbys', u'ZTF18abcbqxj', u'ZTF18abcbvxc', u'ZTF18abckoxz', u'ZTF18abctttf', u'ZTF18abehhbp', u'ZTF17aabuxdi', u'ZTF18abjhbss', u'ZTF18abjlwzq', u'ZTF18absrlhv', u'ZTF18abbmenz', u'ZTF18acbwaxk', u'ZTF19aaqtpet')

• 6-- objects AND sherlock
  SELECT objects.objectId, objects.ramean, objects.decmean, objects.jdmin - 2400000.5 AS mjdmin, objects.jdmax - 2400000.5 AS mjdmax, objects.magrmin, latestrmag, sherlock_classifications.classification
FROM objects, sherlock_classifications
WHERE objects.primaryId = sherlock_classifications.transient_object_id
AND objects.objectid='ZTF19abhpvfq'

• 7-- query on candidates
  SELECT candid, mjd, ra, decl, magpsf, fid
FROM candidates c
WHERE c.ra BETWEEN 191.8212890625 AND 199.9951171875
AND c.decl BETWEEN -27.826574830853872
AND -13.82353192939064
AND c.jd BETWEEN 2458581.5 AND 2458588.5
AND c.rb >= 0.5 AND c.elong <= 1.2 AND abs(c.magdiff) <= 0.3 AND c.isdiffpos ='t'

• 8-- query on candidates
  SELECT c.candid, c.ra, c.decl
FROM candidates c
WHERE c.ra BETWEEN 191.8212890625 AND 199.9951171875
AND c.decl BETWEEN -27.826574830853872 AND -13.82353192939064
AND c.jd BETWEEN 2458581.5 AND 2458588.5

• 9-- candidates AND objects joined
  SELECT o.objectId, o.ncand, c.candid
FROM candidates c NATURAL JOIN objects o
WHERE ncand < 3

• 10-- candidates AND objects joined
  SELECT DISTINCT o.objectId
FROM objects o, candidates c
WHERE o.sherlock_classification IN ('NT')
AND o.jdmin > JDNOW() - 100
AND o.jdmin < JDNOW() - 0
AND o.ncand > 3
AND c.objectId = o.objectId
AND (c.jd > JDNOW() - 100
AND c.jd < JDNOW() - 0)
AND c.magpsf < 20
AND c.rb >= 0.75 AND c.nbad = 0 AND c.isdiffpos = 't' AND c.fwhm <= 5 AND ABS(c.magdiff) <= 0.1 AND c.elong <= 1.2

• 11-- object sand candidates joined
  SELECT objects.objectId, comments.content
FROM objects, comments
WHERE objects.objectId = comments.objectId
AND comments.content LIKE '% iPTF16geu%'

• 12-- distinct objects AND candidates join
  SELECT DISTINCT o.objectId
FROM objects o, candidates c
WHERE o.sherlock_classification IN ('NT')
AND o.jdmin > JDNOW() - 365
AND o.ncand > 3
AND c.objectId = o.objectId
AND (c.jd > JDNOW() - 365)
AND c.magpsf < 20
AND c.rb >= 0.75 AND c.nbad = 0 AND c.isdiffpos = 't' AND c.fwhm <= 5 AND ABS(c.magdiff) <= 0.1 AND c.elong <= 1.2

• 13-- distinct objects AND candidates join
  SELECT DISTINCT o.objectId, o.ramean, o.decmean, o.jdmin - 2400000.5 AS mjdmin, o.jdmax - 2400000.5 AS mjdmax,
o.magrmin, latestrmag, sherlock_classification,
IF(distpsnr1 < 2 AND c.sgscore1 > 0.49, 'Within 2arcsec of PS1 star', 'Not Near PS1 star') score
FROM objects o, candidates c
WHERE o.sherlock_classification NOT IN ( 'AGN', 'BS')
AND o.jdmin > JDNOW() - 4
AND o.ncand > 3
AND c.objectId = o.objectId
AND (c.jd > JDNOW() - 4)
AND c.magpsf < 20 AND c.rb >= 0.75 AND c.nbad = 0 AND c.isdiffpos = 't' AND c.fwhm <= 5 AND ABS(c.magdiff) <= 0.1
AND c.elong <= 1.2 ORDER BY score , mjdmin DESC

• 14-- distinct objects AND candidates join
  SELECT DISTINCT o.objectId IF(distpsnr1 < 2
AND c.sgscore1 > 0.49, 'Within 2arcsec of PS1 star', 'Not Near PS1 star') score
FROM objects o, candidates c
WHERE o.sherlock_classification IN ('NT')
AND o.jdmin > JDNOW() - 3
AND o.ncand > 3
AND c.objectId = o.objectId
AND (c.jd > JDNOW() - 3)
AND c.magpsf < 20 AND c.rb >= 0.75 AND c.nbad = 0 AND c.isdiffpos = 't' AND c.fwhm <= 5 AND ABS(c.magdiff) <= 0.1 AND c.elong <= 1.2;

• 15-- TNS AND objects
  SELECT c.Objectid,latestrmag, latestgmag,jdnow()-jdmax as delta, content
FROM objects NATURAL JOIN comments
WHERE content LIKE '%TNS%'
AND jdmin > JDNOW() - 30
AND jdmin < JDNOW() - 0
AND (jdmin-jdmax>1 or jdmin-jdmax<-1)
AND ncand > 2
AND (maggmin>latestgmag-0.3 OR magrmin>latestrmag-0.3)
AND (latestgmag<17.2 or latestrmag<17.2)
AND DECMEAN>-10
AND (sherlock_classification='SN'or sherlock_classification='ORPHAN') ORDER BY delta

• 16-- TNS AND objects
  SELECT objects.objectId, objects.latestrmag, objects.latestgmag, objects.jdmax - jdnow() AS delta, sherlock_classifications.classification, comments.content
FROM objects, comments
WHERE objects.objectId = comments.objectId
AND comments.content LIKE '%TNS%'

• 17-- distinct TNS AND comments
  SELECT DISTINCT Objectid,latestrmag, latestgmag,jdnow()-jdmax as delta, content
FROM objects NATURAL JOIN comments
WHERE content LIKE '%TNS%'
AND jdmin > JDNOW() - 30
AND jdmin < JDNOW() - 0
AND (jdmin-jdmax>1 or jdmin-jdmax<-1)
AND ncand > 2
AND (maggmin>latestgmag-0.3 OR magrmin>latestrmag-0.3)
AND (latestgmag<17.2 or latestrmag<17.2)
AND DECMEAN>-10
AND (sherlock_classification='SN'or sherlock_classification='ORPHAN') ORDER BY delta

• 18-- subquery
  SELECT *
FROM objects
WHERE ncand >= 5
AND objectId in
(SELECT objectId FROM candidates WHERE rb >= 0.75
AND nbad = 0 AND fwhm <= 5 AND elong <= 1.2 AND abs(magdiff) <= 0.1
AND sgscore1 < 0.4
AND jdstarthist > 2458563.7)

• 19-- subquery
  SELECT *,candid,objectId
FROM candidates
WHERE rb >= 0.75 AND nbad = 0 AND fwhm <= 5 AND elong <= 1.2 AND abs(magdiff) <= 0.1
AND sgscore1 < 0.4
AND jdstarthist > 2458359.1 AND objectId in
(SELECT objectId FROM objects WHERE ncand >= 5 )

• 20-- subquery
  SELECT distinct objectId, ramean, decmean, mjdmin, mjdmax, magrmin, latestrmag, sherlock_classification, ncand
FROM
(SELECT o.objectId, c.jd as mjd, c.fid, c.magpsf, o.jdmin as mjdmin, jdmax as mjdmax,
o.maggmin, o.maggmax, o.magrmin, o.magrmax, o.latestrmag, o.sherlock_classification,
o.ramean, o.decmean, o.ncand FROM objects o NATURAL JOIN candidates c
WHERE c.magpsf < 21
AND c.jd > JDNOW() - 20
AND o.jdmin > JDNOW() - 20
AND o.jdmax > JDNOW() - 3
AND o.ncand >= 3
AND abs(glatmean) > 10.0
AND o.sherlock_classification != 'VS'
AND o.sherlock_classification != 'AGN'
AND o.sherlock_classification != 'CV'
AND o.sherlock_classification != 'BS'
AND o.sherlock_classification != 'NT'
AND c.rb >= 0.4 AND c.nbad = 0 AND c.fwhm <= 5 AND c.elong <= 1.2 AND abs(c.magdiff) <= 0.2 AND c.isdiffpos = 't'
AND c.distnr > 5 ) temp ORDER BY ncand asc, mjdmax desc, mjdmin desc, magrmin desc

• 21-- subquery
  SELECT distinct objectId, ramean, decmean, mjdmin, mjdmax, magrmin, latestrmag, sherlock_classification, ncand,separationArcsec
FROM
(SELECT o.objectId, c.jd as mjd, c.fid, c.magpsf, o.jdmin as mjdmin, jdmax as mjdmax,
o.maggmin, o.maggmax, o.magrmin, o.magrmax, o.latestrmag, o.sherlock_classification,
o.ramean, o.decmean, o.ncand, sc.separationArcsec FROM sherlock_crossmatches sc
JOIN objects o ON sc.transient_object_id = o.primaryid NATURAL JOIN candidates c
WHERE c.magpsf < 21
AND c.jd > JDNOW() - 20
AND o.jdmin > JDNOW() - 20
AND o.jdmax > JDNOW() - 5
AND o.ncand >= 2
AND o.sherlock_classification NOT IN ('VS' , 'AGN', 'CV', 'BS','NT')
AND c.rb >= 0.4 AND c.nbad = 0 AND c.fwhm <= 5 AND c.elong <= 1.2 AND abs(c.magdiff) <= 0.
AND c.isdiffpos = 't'
AND (sc.rank = 1 OR sc.rank=2)
AND sc.catalogue_object_type = 'galaxy'
AND o.sherlock_classification = 'SN'
AND sc.separationArcsec > 0.5
AND sc.separationArcsec < 100 ) temp
FROM WHERE mjdmax desc, separationArcsec desc, ncand asc, mjdmin desc, magrmin desc

• 22-- triple join
  SELECT objects.objectId
FROM objects,candidates,sherlock_classifications
WHERE objects.objectId = candidates.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id
AND sherlock_classifications.classification IN ("NT")
AND objects.ncand > 3
AND candidates.objectId = objects.objectId
AND candidates.magpsf < 20
AND candidates.rb >= 0.75
AND candidates.nbad = 0
AND candidates.isdiffpos = "t"
AND candidates.fwhm <= 5
AND ABS(candidates.magdiff) <= 0.1
AND candidates.elong <= 1.2
AND objects.jdmin > JDNOW() - 30
AND candidates.jd > JDNOW() - 30

• 23-- triple join
  SELECT objects.objectId, candidates.jd - 2400000.5 AS ago, candidates.magpsf, candidates.sigmapsf, candidates.fid
FROM candidates, objects, sherlock_classifications
WHERE objects.objectId = candidates.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id
AND objects.objectId = candidates.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id
AND sherlock_classifications.classification NOT IN ("VS" , "AGN", "CV", "BS")
AND objects.jdmin > JDNOW() - 250
AND objects.ncand > 100
AND candidates.objectId = objects.objectId
AND (candidates.jd > JDNOW() - 100)
AND candidates.magpsf < 20
AND candidates.drb >= 0.75
AND candidates.isdiffpos = "t" ORDER BY jdmin ASC

• 24-- triple join
  FROM objects,candidates,sherlock_classifications
WHERE objects.objectId = candidates.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id
AND objects.jdmax > JDNOW() - 5.00000
AND candidates.jd > JDNOW() - 5.00000
AND sherlock_classifications.classification NOT IN ("VS" , "AGN", "CV", "BS")
AND objects.jdmin > JDNOW() - 14
AND objects.ncand > 3
AND candidates.objectId = objects.objectId
AND (candidates.jd > JDNOW() - 14)
AND candidates.magpsf < 20
AND candidates.drb >= 0.75
AND candidates.isdiffpos = "t" GROUP BY objects.objectId ORDER BY score , mjdmin DESC

• 25-- triple join
  SELECT objects.objectId, jdnow()-jdmax as delta, comments.content
FROM objects, comments, sherlock_classifications
WHERE objects.objectId = comments.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id
AND objects.jdmin>jdnow()-30
AND objects.jdmin<jdnow()-0
AND (objects.jdmin-objects.jdmax>0.1 or objects.jdmin-objects.jdmax<-0.1)
AND ncand>2
AND ncand<20
AND (maggmin>latestgmag-0.5 or magrmin>latestrmag-0.5)
AND (latestgmag<17.3 or latestrmag<17.3)
AND comments.content LIKE '%TNS%'
AND comments.content LIKE '%AT20%'
AND decmean>-20
AND sherlock_classifications.classification NOT IN ("VS" , "AGN", "CV", "BS", "NT") order by delta

• 26-- triple join
  SELECT objects.objectId, latestgmag, latestrmag, jdnow()-jdmax as delta, comments.content, sherlock_classifications.classification
FROM objects, comments, sherlock_classifications
WHERE objects.objectId = comments.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id
AND objects.jdmin>jdnow()-30
AND objects.jdmin<jdnow()-0
AND (objects.jdmin-objects.jdmax>0.5 or objects.jdmin-objects.jdmax<-0.5)
AND ncand>2
AND ncand<20
AND (maggmax-maggmin>1.0 or magrmax-magrmin>1.0)
AND (latestgmag < maggmin+0.1
AND latestgmag > maggmin-0.1 )
AND (latestrmag < magrmin+0.1
AND latestrmag > magrmin-0.1)
AND (latestgmag<19.9 or latestrmag<19.9)
AND comments.content LIKE '%TNS%'
AND comments.content LIKE '%SN20%'
AND decmean>-10
AND sherlock_classifications.classification NOT IN ("xxx") order by delta

• 27-- distinct triple join
  SELECT DISTINCT o.objectId, o.ramean, o.decmean, o.jdmin - 2400000.5 AS mjdmin, o.jdmax - 2400000.5 AS mjdmax, o.magrmin, latestrmag, sherlock_classification, IF(distpsnr1 < 2
AND c.sgscore1 > 0.49, 'Within 2arcsec of PS1 star', 'Not Near PS1 star') score
FROM objects,candidates,sherlock_classifications
WHERE objects.objectId = candidates.objectId
AND objects.primaryId = sherlock_classifications.transient_object_idobjectId ="ZTF17aabdaaq"

  • Executed in 0.03 seconds

• 3-- specific object
  SELECT *
FROM objects objectId
WHERE objectId="ZTF18acmzpbf"

  • Executed in 0.00 seconds

• 4-- triple join with list of objectIds
  SELECT objects.objectId, candidates.jd - 2400000.5 AS ago, candidates.magpsf, candidates.sigmapsf, candidates.fid
FROM candidates, objects, sherlock_classifications
WHERE objects.objectId = candidates.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id
AND objects.objectId in ('ZTF18aavqmxr', 'ZTF18aaiugdp', 'ZTF18aaxddex', 'ZTF18aawcbyu', 'ZTF18aavlgby', 'ZTF18aavfhlv', 'ZTF18aainceg', 'ZTF18aaveejk', 'ZTF18aauesbd', 'ZTF18aavelyy', 'ZTF18aavqlnk', 'ZTF18aavefdz', 'ZTF18aasnjav', 'ZTF18aaxjkcm', 'ZTF18aaudxvh', 'ZTF18aauqlxd', 'ZTF18aaxnbfs', 'ZTF18aaxpkty', 'ZTF18aaxumvv', 'ZTF18aaxvbtx', 'ZTF17aadsntf', 'ZTF18aayeepw', 'ZTF18aavghok', 'ZTF18aayotqj', 'ZTF18aaywedv', 'ZTF18aaymybb', 'ZTF18aazmegj', 'ZTF17aabumwe', 'ZTF18aazvjuy', 'ZTF18aazwddf', 'ZTF17aabupib', 'ZTF18aazwdak', 'ZTF17aaapvpp', 'ZTF17aaarqox', 'ZTF18abbmbys', 'ZTF18abcbqxj', 'ZTF18abcbvxc', 'ZTF18abckoxz', 'ZTF18abctttf', 'ZTF18abehhbp', 'ZTF17aabuxdi', 'ZTF18abjhbss', 'ZTF18abjlwzq', 'ZTF18absrlhv', 'ZTF18abbmenz', 'ZTF18acbwaxk', 'ZTF19aaqtpet')

  • Wants the light curves of all these objects. Takes more than 5 minutes. Is not using any information from the Sherlock table even though it is joined in. How about a lightcurve fetch in Jupyter:

  • objects = ['ZTF18aavqmxr', 'ZTF18aaiugdp', 'ZTF18aaxddex', 'ZTF18aawcbyu', 'ZTF18aavlgby', 'ZTF18aavfhlv', 'ZTF18aainceg', 'ZTF18aaveejk', 'ZTF18aauesbd', 'ZTF18aavelyy', 'ZTF18aavqlnk', 'ZTF18aavefdz', 'ZTF18aasnjav', 'ZTF18aaxjkcm', 'ZTF18aaudxvh', 'ZTF18aauqlxd', 'ZTF18aaxnbfs', 'ZTF18aaxpkty', 'ZTF18aaxumvv', 'ZTF18aaxvbtx', 'ZTF17aadsntf', 'ZTF18aayeepw', 'ZTF18aavghok', 'ZTF18aayotqj', 'ZTF18aaywedv', 'ZTF18aaymybb', 'ZTF18aazmegj', 'ZTF17aabumwe', 'ZTF18aazvjuy', 'ZTF18aazwddf', 'ZTF17aabupib', 'ZTF18aazwdak', 'ZTF17aaapvpp', 'ZTF17aaarqox', 'ZTF18abbmbys', 'ZTF18abcbqxj', 'ZTF18abcbvxc', 'ZTF18abckoxz', 'ZTF18abctttf', 'ZTF18abehhbp', 'ZTF17aabuxdi', 'ZTF18abjhbss', 'ZTF18abjlwzq', 'ZTF18absrlhv', 'ZTF18abbmenz', 'ZTF18acbwaxk', 'ZTF19aaqtpet']
for all object in objects: print(lightcurve(object))

• 5-- objects AND sherlock
  SELECT objects.objectId, objects.ramean, objects.decmean, objects.jdmin - 2400000.5 AS mjdmin, objects.jdmax - 2400000.5 AS mjdmax, objects.magrmin, latestrmag, sherlock_classifications.classification
FROM objects, sherlock_classifications
WHERE objects.primaryId = sherlock_classifications.transient_object_id
AND objects.objectid='ZTF19abhpvfq'

  • executed in 0.03 seconds

• 6-- query on candidates with specific position and time
  SELECT candid, mjd, ra, decl, magpsf, fid
FROM candidates c
WHERE c.ra BETWEEN 191.8212890625 AND 199.9951171875
AND c.decl BETWEEN -27.826574830853872
AND -13.82353192939064
AND c.jd BETWEEN 2458581.5 AND 2458588.5
AND c.drb >= 0.75

  • executed in 72 seconds, empty set

  • This alternate objects query gets the empty set in 16 seconds:

  • SELECT objectId, ramean, decmean
FROM objects
WHERE objects.ramean BETWEEN 191.8212890625 AND 199.9951171875
AND objects.decmean BETWEEN -27.826574830853872 AND -13.82353192939064
AND objects.jdmin < 2458581.5 AND objects.jdmax > 2458588.5
AND objects.ncandgp > 2

Empty set (15.91 sec)

• 7-- candidates and objects joined
  SELECT DISTINCT o.objectId
FROM objects o, candidates c
WHERE o.sherlock_classification IN ('NT')
AND o.jdmin > JDNOW() - 100
AND o.jdmin < JDNOW() - 0
AND o.ncand > 3
AND c.objectId = o.objectId
AND (c.jd > JDNOW() - 100AND c.jd < JDNOW() - 0)
AND c.magpsf < 20 AND c.drb >= 0.75 LIMIT 10

  • Takes more than 5 minutes. How about this instead which is 4 seconds

  • SELECT o.objectId FROM objects o WHERE o.sherlock_classification IN ('NT')
AND o.jdmin > JDNOW() - 100 AND o.jdmin < JDNOW() - 0
AND o.ncandgp > 3
AND (o.maggmin < 20 OR o.maggmin < 20) LIMIT 10

• 8-- objects and candidates joined
  SELECT objects.objectId, comments.content
FROM objects, comments
WHERE objects.objectId = comments.objectId
AND comments.content LIKE '% iPTF16geu%'

  • Empty set in 0.05 seconds. Because the comments from Lasair Bot are TNS object names not PTF object names.

• 9-- distinct objects AND candidates join
  SELECT DISTINCT o.objectId, o.ramean, o.decmean, o.jdmin - 2400000.5 AS mjdmin, o.jdmax - 2400000.5 AS mjdmax, o.magrmin, latestrmag, sherlock_classification,
IF(c.distpsnr1 < 2 AND c.sgscore1 > 0.49, 'Within 2arcsec of PS1 star', 'Not Near PS1 star') score
FROM objects o, candidates c
WHERE o.sherlock_classification NOT IN ( 'AGN', 'BS')AND o.jdmin > JDNOW() - 4
AND o.ncand > 3 AND c.objectId = o.objectId AND (c.jd > JDNOW() - 4) AND c.magpsf < 20 AND c.drb >= 0.75

  • executed in 7 seconds

• 10-- TNS AND objects
  SELECT objectId,latestrmag, latestgmag,jdnow()-jdmax as delta, content
FROM objects NATURAL JOIN comments
WHERE content LIKE '%TNS%'
AND jdmin > JDNOW() - 30 AND jdmin < JDNOW() - 0
AND (jdmin-jdmax>1 or jdmin-jdmax<-1)
AND ncand > 2
AND (maggmin>latestgmag-0.3 OR magrmin>latestrmag-0.3)
AND (latestgmag<17.2 or latestrmag<17.2)
AND DECMEAN>-10
AND (sherlock_classification='SN'or sherlock_classification='ORPHAN')

  • executed in 0.5 seconds

• 11-- TNS AND objects
  SELECT objects.objectId, objects.latestrmag, objects.latestgmag, objects.jdmax - jdnow() AS delta, objects.sherlock_classification, comments.content
FROM objects, comments
WHERE objects.objectId = comments.objectId AND comments.content LIKE '%TNS%' LIMIT 10

  • executed in 0.0 seconds

• 12-- subquery
  SELECT * FROM objects
WHERE ncand >= 5 AND objectId in
(SELECT objectId FROM candidates WHERE drb >= 0.75 AND sgscore1 < 0.4 AND jdstarthist > 2458563.7)

  • Takes a long time because of the massive subquery! Burns up temp storgae on database node!

  • This query does the same and runs in 2.4 seconds:

  • SELECT * FROM objects
WHERE objects.ncandgp >= 5 AND objects.sgscore1 < 0.4 and objects.jdmin > 2458563.7
LIMIT 10

• 13-- subquery
  SELECT distinct objectId, ramean, decmean, mjdmin, mjdmax, magrmin, latestrmag, sherlock_classification, ncand
FROM
(SELECT o.objectId, c.jd as mjd, c.fid, c.magpsf, o.jdmin as mjdmin, jdmax as mjdmax,
o.maggmin, o.maggmax, o.magrmin, o.magrmax, o.latestrmag, o.sherlock_classification,
o.ramean, o.decmean, o.ncand FROM objects o NATURAL JOIN candidates c
WHERE c.magpsf < 21
AND c.jd > JDNOW() - 20
AND o.jdmin > JDNOW() - 20
AND o.jdmax > JDNOW() - 3
AND o.ncand >= 3
AND abs(glatmean) > 10.0
AND o.sherlock_classification != 'VS'
AND o.sherlock_classification != 'AGN'
AND o.sherlock_classification != 'CV'
AND o.sherlock_classification != 'BS'
AND o.sherlock_classification != 'NT'
AND c.rb >= 0.4 AND c.nbad = 0 AND c.fwhm <= 5 AND c.elong <= 1.2 AND abs(c.magdiff) <= 0.2 AND c.isdiffpos = 't'
AND c.distnr > 5 ) temp ORDER BY ncand asc, mjdmax desc, mjdmin desc, magrmin desc

  • Even the subquery takes more than 5 minutes.

  • I wonder about the distnr condition, distnr is “distance to nearest source in reference image PSF-catalog within 30 arcsec [pixels]”

  • How about this instead which takes 24 seconds:

  • SELECT o.objectId, o.jdmin as mjdmin, jdmax as mjdmax,
o.maggmin, o.maggmax, o.magrmin, o.magrmax, o.latestrmag, o.sherlock_classification,
o.ramean, o.decmean, o.ncand FROM objects o
WHERE (o.maggmin < 21 OR o.magrmin < 21)
AND o.jdmin > JDNOW() - 20 AND o.jdmax > JDNOW() - 3
AND o.ncandgp >= 3 AND abs(o.glatmean) > 10.0
AND o.sherlock_classification != 'VS'
AND o.sherlock_classification != 'AGN'
AND o.sherlock_classification != 'CV'
AND o.sherlock_classification != 'BS'
AND o.sherlock_classification != 'NT'
LIMIT 10

• 14-- subquery
  SELECT distinct objectId, ramean, decmean, mjdmin, mjdmax, magrmin, latestrmag, sherlock_classification, ncand,separationArcsec
FROM
(SELECT o.objectId, c.jd as mjd, c.fid, c.magpsf, o.jdmin as mjdmin, jdmax as mjdmax,
o.maggmin, o.maggmax, o.magrmin, o.magrmax, o.latestrmag, o.sherlock_classification,
o.ramean, o.decmean, o.ncand, sc.separationArcsec FROM sherlock_crossmatches sc
JOIN objects o ON sc.transient_object_id = o.primaryid NATURAL JOIN candidates c
WHERE c.magpsf < 21
AND c.jd > JDNOW() - 20
AND o.jdmin > JDNOW() - 20
AND o.jdmax > JDNOW() - 5
AND o.ncand >= 2
AND o.sherlock_classification NOT IN ('VS' , 'AGN', 'CV', 'BS')
AND o.jdmin > JDNOW() - 5
AND o.ncand > 3
AND c.objectId = o.objectId
AND (c.jd > JDNOW() - 5)
AND c.magpsf < 22
AND c.,'NT')
AND c.rb >= 0.65
4 AND c.nbad = 0
AND c.fwhm <= 5
AND ABS(c.magdiff) <= 0.1
fwhm <= 5 AND c.elong <= 1.2 ORDER BY score , mjdmin DESC

• 28-- quadruple join
  SELECT distinct objects.objectId, ramean, decmean, jdmin-2400000.5 AS mjdmin, jdmax-2400000.5 AS mjdmax, magrmin, latestrmag, sherlock_classifications.classification, ncand, (sherlock_crossmatches.distance*1000)TAN((sherlock_crossmatches.separationArcsec/3600)(PI()/180)) as sep
FROM candidates, objects, sherlock_classifications, sherlock_crossmatches
WHERE objects.objectId = candidates.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id
AND objects.primaryId = sherlock_crossmatches.transient_object_id
AND objects.jdmax > JDNOW() - 15.00000
AND candidates.jd > JDNOW() - 15.00000
AND candidates.magpsf < 21.5
AND objects.ncand >= 2
AND objects.ncand < 5
AND objects.latestgmag - objects.latestrmag <0.2
AND sherlock_classifications.classification NOT IN ("VS" , "AGN", "CV", "BS")
AND candidates.rb >= 0.6
AND candidates.nbad = 0
AND candidates.fwhm <= 5
AND candidates.elong <= 1.2
AND abs(candidates.magdiff) <= 0.2
AND objects.latestgmag - candidates.sgmag1 < 0.5
AND candidates.isdiffpos = "t"
AND (sherlock_crossmatches.rank = 1)
AND sherlock_crossmatches.catalogue_object_type = "galaxy"
AND sherlock_classifications.classification = "SN"

• 29-- quadruple join
  SELECT objects.objectId, objects.ramean, objects.decmean, objects.jdmin - 2400000.5 AS mjdmin, objects.jdmax - 2400000.5 AS mjdmax, objects.magrmin, latestrmag, sherlock_classifications.classification, IF(objects.distpsnr1 < 2
AND objects.sgscore1 > 0.49, "Within 2arcsec of PS1 star", "Not Near PS1 star") score
FROM candidates, AND abs(c.magdiff) <= 0.
AND c.isdiffpos = 't'
AND (sc.rank = 1 OR sc.rank=2)
AND sc.catalogue_object_type = 'galaxy'
AND o.sherlock_classification = 'SN'
AND sc.separationArcsec > 0.5
AND sc.separationArcsec < 100 ) temp

  • Why does this need to be in a subquery??

  • How about this one that takes 10 seconds:

  • SELECT o.objectId, o.jdmin as mjdmin, o.jdmax as mjdmax, o.maggmin, o.maggmax, o.magrmin, o.magrmax, o.latestrmag, o.sherlock_classification, o.ramean, o.decmean, o.ncand, sc.separationArcsec
FROM sherlock_crossmatches sc JOIN objects o ON sc.transient_object_id = o.primaryid
WHERE (o.maggmin < 21 OR o.magrmin < 21) AND o.jdmin > JDNOW() - 20 AND o.jdmax > JDNOW() - 5 AND o.ncandgp >= 2
AND o.sherlock_classification NOT IN ('VS' , 'AGN', 'CV', 'BS','NT')
AND (sc.rank = 1 OR sc.rank=2) AND sc.catalogue_object_type = 'galaxy'
AND o.sherlock_classification = 'SN' AND sc.separationArcsec > 0.5 AND sc.separationArcsec < 100 LIMIT 10

• 15-- triple join
  SELECT objects.objectId, jdnow()-jdmax as delta, comments.content
FROM objects, comments, sherlock_classifications
WHERE objects.objectId = candidates.objectId
AND objects.objectId = comments.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id
AND sherlock_classifications.classification NOT IN ("VS" , "AGN", "CV", "BS")
AND objects.jdmin > JDNOW() - 14
AND objects.ncand > 3
AND magrmin<16.5
AND comments.content LIKE '%TNS%'and candidates.objectId = objects.objectId
AND (candidates.jd > JDNOW() - 14)
AND candidates.magpsf < 20
AND candidates.drb >= 0.75
AND candidates.isdiffpos = "t" ORDER BY score , mjdmin DESC30objects.jdmin>jdnow()-30 AND objects.jdmin<jdnow()-0
AND (objects.jdmin-objects.jdmax>0.1 or objects.jdmin-objects.jdmax<-0.1)
AND ncand>2 AND ncand<20 AND (maggmin>latestgmag-0.5 or magrmin>latestrmag-0.5) AND (latestgmag<17.3 or latestrmag<17.3)
AND comments.content LIKE '%TNS%' AND comments.content LIKE '%AT20%'AND decmean>-20
AND sherlock_classifications.classification NOT IN ("VS" , "AGN", "CV", "BS", "NT")

  • Executes in 1.6 seconds

• 16-- quadruple join
  SELECT distinct objects.objectId, objects. ramean, objects. decmean, objects. jdmin-2400000.5 AS mjdmin, objects. jdmax-2400000.5 AS mjdmax, objects. magrmin, latestrmag, sherlock_classifications.classification, ncand,
(sherlock_crossmatches.z,candidates.jd - 2400000.5 AS date, IF(objects.distpsnr1 < 2
AND objects.sgscore1 > 0.49, "Within 2arcsec of PS1 star", "Not Near PS1 star") score, candidates.rb,candidates.drb,candidates.distnr
FROM objects,candidatesdistance*1000)TAN((sherlock_crossmatches.separationArcsec/3600)(PI()/180)) as sep
FROM candidates, objects, sherlock_classifications, sherlock_crossmatches
WHERE objects.objectId = candidates.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id
AND objects.primaryId = sherlock_crossmatches.transient_object_id
AND objects.jdmax > JDNOW() - 515.00000
AND candidates.jd > JDNOW() - 5.00000- 15.00000
AND candidates.magpsf < 21.5
AND objects.ncand >= 2 AND objects.ncand < 5
AND objects.latestgmag - objects.latestrmag < 0.2
AND sherlock_classifications.classification NOT IN ("VS" , "AGN", "CV", "BS")
AND objectscandidates.jdmin > JDNOW() - 5drb >= 0.6
AND objects.ncand > 3latestgmag - candidates.sgmag1 < 0.5
AND candidates.objectId = objects.objectIdisdiffpos = "t"
AND (candidates.jd > JDNOW() - 5sherlock_crossmatches.rank = 1)
AND candidates.magpsf < 20
AND candidates.drb >= 0.75
AND candidates.isdiffpos = "t" GROUP BY objects.objectId ORDER BY score , mjdmin DESC31-- quadruple join
  SELECT sherlock_crossmatches.catalogue_object_type = "galaxy"
AND sherlock_classifications.classification = "SN"

  • Executes in 43 seconds

  • This version avoids the join with the candidates table and takes only 0.7 seconds:

  • SELECT distinct objects.objectId, ramean, decmean, jdmin-2400000.5 AS mjdmin, jdmax-2400000.5 AS mjdmax, magrmin, latestrmag, sherlock_classifications.classification, ncand,
(sherlock_crossmatches.

  distance1000

  • distance*1000)TAN((sherlock_crossmatches.separationArcsec/3600)(PI()/180)) as sep
FROM

  candidates,

  • objects, sherlock_classifications, sherlock_crossmatches
WHERE

  objects.objectId = candidates.objectId
  AND

  • objects.primaryId = sherlock_classifications.transient_object_id
AND objects.primaryId = sherlock_crossmatches.transient_object_id
AND objects.jdmax > JDNOW() -

  5

  • 15.00000
AND (objects.maggmin < 21 OR objects.magrmin < 21)
AND

  candidates

  • objects.

  jd > JDNOW() - 5.00000
  AND candidates.magpsf < 21

  • ncandgp >= 2 AND objects.ncand < 5
AND objects.latestgmag - objects.latestrmag < 0.2
AND sherlock_classifications.classification NOT IN ("VS" , "AGN

  ", "CV", "BS

  • ", "

  NULL")
  AND candidates.drb >= 0.75
AND candidates.isdiffpos = "t"
AND candidates.isdiffpos = "t"

  • CV", "BS")
AND objects.latestgmag - objects.sgmag1 < 0.5
AND (sherlock_crossmatches.rank = 1

  or sherlock_crossmatches.rank = 2

  • )
AND sherlock_crossmatches.catalogue_object_type = "galaxy"

  
  AND sherlock_classifications.classification = "SN"
AND (sherlock_crossmatches.distance1000)TAN((sherlock_crossmatches.separationArcsec/3600)(PI()/180)) > 20

  • 
    AND

  (

  • sherlock_

  crossmatches.distance*1000)TAN((sherlock_crossmatches.separationArcsec/3600)(PI()/180)) < 125
  AND sherlock_crossmatches.z <=0.05 GROUP BY objects.objectId, sherlock_crossmatches.rank32-- watchlist using sql
  SELECT jd,ra,decl
FROM candidates
WHERE candidates.jd > JDNOW() - 10.00000
  AND ( ((ra BETWEEN 190.44 AND 198.67) AND (dec BETWEEN -78.32 AND -76.49

  • classifications.classification = "SN"

• 17-- watchlist using sql
  SELECT jd,ra,decl FROM candidates
WHERE candidates.jd > JDNOW() - 10.00000
AND ( ((ra BETWEEN 190.44 AND 198.67) AND (decl BETWEEN -78.32 AND -76.49))
OR ((ra BETWEEN 241.49 AND 243.95) AND (decl BETWEEN -40.41 AND -36.83))
OR ((ra BETWEEN 239.60 AND 241.14) AND (decl BETWEEN -43.12 AND -40.71))
OR ((ra BETWEEN 233.92 AND 237.04) AND (decl BETWEEN -35.91 AND -32.10))
OR ((ra BETWEEN 241.49 AND 243.95190 AND 244 ) AND (dec decl BETWEEN -40.41 79 AND -36.8332))
OR ((ra BETWEEN 239250.60 49 AND 241251.1490) AND (dec decl BETWEEN -4322.12 89 AND -4020.7124))
OR ((ra BETWEEN 233244.92 63 AND 237251.0485) AND (dec decl BETWEEN -35.91 2614 AND -3222.1009))
OR ((ra BETWEEN 190 244 AND 244 252 ) AND (dec decl BETWEEN -79 27 AND -3220))
OR ((ra BETWEEN 25082.49 99 AND 25186.9021 ) AND (dec decl BETWEEN -10.22 .89 AND -204.2457))
OR ((ra BETWEEN 24485.63 06 AND 25187.8516 ) AND (dec decl BETWEEN -2614 AND -22.092.66 AND 0.93))
OR ((ra BETWEEN 244 82 AND 252 88 ) AND (dec decl BETWEEN -27 11 AND -201))
OR ((ra BETWEEN 8250.99 66 AND 8657.21 26 ) AND (dec decl BETWEEN -1029.22 62 AND -433.5716))
OR ((ra BETWEEN 85276.06 80 AND 87277.16 84) AND (dec decl BETWEEN -20.66 55 AND 01.9386))
OR ((ra BETWEEN 82 AND 88 ) AND (dec BETWEEN -11 AND 1))
OR ((ra BETWEEN 50.66 AND 57.26 ) AND (dec BETWEEN 29.62 AND 33.16))
OR ((ra BETWEEN 276.80 AND 277.84) AND (dec BETWEEN -0.55 AND 1.86))
OR ((ra BETWEEN 61.85 AND 73.27 ) AND (dec BETWEEN 21.00 AND 30.02)))33-- noncandidates61.85 AND 73.27 ) AND (decl BETWEEN 21.00 AND 30.02)))

  • Executes in 14 seconds

• 18-- noncandidates
  SELECT objects.objectId, objects.ramean, objects.decmean, objects.jdmin - 2400000.5 AS mjdmin, objects.jdmax - 2400000.5 AS mjdmax, objects.magrmin, latestrmag, sherlock_classifications.classification,
IF(candidates.distpsnr1 < 2 AND candidates.sgscore1 > 0.49, "Within 2 arcsec of PS1 star", "Not Near PS1 star") score
FROM candidates, noncandidates, objects, sherlock_classifications
WHERE objects.objectId = candidates.objectId
AND objects.objectId = noncandidates.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id

  • Takes many minutes and does not finish … does not actually need either candidates or noncandidates table, this executes in 0.0 seconds:

• SELECT objects.objectId, objects.ramean, objects.decmean, objects.jdmin - 2400000.5 AS mjdmin, objects.jdmax - 2400000.5 AS mjdmax, objects.magrmin, latestrmag, sherlock_classifications.classification,
IF(objects.distpsnr1 < 2 AND candidatesobjects.sgscore1 > 0.49, "Within 2arcsec 2 arcsec of PS1 star", "Not Near PS1 star") score
FROM candidates, noncandidates, objects, sherlock_classifications
WHERE objects.objectId = candidates.objectId
AND objects.objectId = noncandidates.objectId
AND objects.primaryId = sherlock_classifications.transient_object_id
LIMIT 10