-- Which are essentially a repeat of of another query in the list?
-- Which are just stupid?
-- Which will take a long time?
-- Which could be made much faster with a suitable change to the query?
-- Which could be made much faster with a suitable change to the Lasair system?
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
WHERE objects.objectId = candidates.objectId
AND candidates.objectId ="ZTF17aabdaaq"
Executed in 0.03 seconds
3-- specific object
SELECT *
FROM objects objectId
WHERE objectId="ZTF18acmzpbf"
Executed in 0.00 seconds
5-- 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))
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'
executed in 0.03 seconds
7-- 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)
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() - 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
11-- object sand 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.
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(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
15-- 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
16-- 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
18-- 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
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_id
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.rb >= 0.65
AND c.nbad = 0
AND c.fwhm <= 5
AND ABS(c.magdiff) <= 0.1
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, 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 DESC
30-- 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, 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,candidates,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.00000
AND candidates.jd > JDNOW() - 5.00000
AND sherlock_classifications.classification NOT IN ("VS" , "AGN", "CV", "BS")
AND objects.jdmin > JDNOW() - 5
AND objects.ncand > 3
AND candidates.objectId = objects.objectId
AND (candidates.jd > JDNOW() - 5)
AND candidates.magpsf < 20
AND candidates.drb >= 0.75
AND candidates.isdiffpos = "t" GROUP BY objects.objectId ORDER BY score , mjdmin DESC
31-- quadruple join
SELECT objects.objectId, ramean, decmean, jdmin-2400000.5 AS mjdmin, jdmax-2400000.5 AS mjdmax, magrmin, latestrmag, sherlock_classifications.classification, ncand, (sherlock_crossmatches.distance1000)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.00000
AND candidates.jd > JDNOW() - 5.00000
AND candidates.magpsf < 21
AND sherlock_classifications.classification NOT IN ("VS" , "AGN", "CV", "BS", "NULL")
AND candidates.drb >= 0.75
AND candidates.isdiffpos = "t"
AND candidates.isdiffpos = "t"
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.rank
32-- 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))
OR ((ra BETWEEN 241.49 AND 243.95) AND (dec BETWEEN -40.41 AND -36.83))
OR ((ra BETWEEN 239.60 AND 241.14) AND (dec BETWEEN -43.12 AND -40.71))
OR ((ra BETWEEN 233.92 AND 237.04) AND (dec BETWEEN -35.91 AND -32.10))
OR ((ra BETWEEN 190 AND 244 ) AND (dec BETWEEN -79 AND -32))
OR ((ra BETWEEN 250.49 AND 251.90) AND (dec BETWEEN -22.89 AND -20.24))
OR ((ra BETWEEN 244.63 AND 251.85) AND (dec BETWEEN -2614 AND -22.09))
OR ((ra BETWEEN 244 AND 252 ) AND (dec BETWEEN -27 AND -20))
OR ((ra BETWEEN 82.99 AND 86.21 ) AND (dec BETWEEN -10.22 AND -4.57))
OR ((ra BETWEEN 85.06 AND 87.16 ) AND (dec BETWEEN -2.66 AND 0.93))
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-- 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(distpsnr1 < 2 AND candidates.sgscore1 > 0.49, "Within 2arcsec 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