{"type":"doc","content":[{"type":"paragraph","content":[{"text":"– Now reduced to 18 queries by removing what seem to be repeats.","type":"text"}]},{"type":"paragraph","content":[{"text":"– Removed ORDER BY from all queries. In a realtime pipeline the order is always time.","type":"text"}]},{"type":"paragraph","content":[{"text":"– Added LIMIT 10 where necessary, to all queries for the purposes of timing.","type":"text"}]},{"type":"paragraph","content":[{"text":"– Many queries have pointless joins with the candidates table that can be done without (see below).","type":"text"}]},{"type":"paragraph","content":[{"text":"– 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.","type":"text"}]},{"type":"paragraph","content":[{"text":"– Number 4 is asking for lightcurves of 47 objects, which is more suitable for jupyter/API interface","type":"text"}]},{"type":"paragraph","content":[{"text":"– Should we simply make subqueries impossible, as it is so easy to abuse?","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"1-- query on objects","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT objectId,ncand","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"FROM objects","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"WHERE ncand > 20 ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (latestgmag-maggmean)>2 LIMIT 10","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Executed in 0.01 seconds","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"2-- specific object","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT objects.ncand, objects.ramean, objects.decmean, objects.jdmin, objects.maggmean,candidates.magpsf ,candidates.fid","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"FROM candidates, objects ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objects.objectId = candidates.objectId","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"AND candidates.objectId =\"ZTF17aabdaaq\"","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Executed in 0.03 seconds","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"3-- specific object","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT *","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"FROM objects objectId ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objectId=\"ZTF18acmzpbf\"","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Executed in 0.00 seconds","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"4-- triple join with list of objectIds","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT objects.objectId, candidates.jd - 2400000.5 AS ago, candidates.magpsf, candidates.sigmapsf, candidates.fid","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"FROM candidates, objects, sherlock_classifications","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"WHERE objects.objectId = candidates.objectId ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.primaryId = sherlock_classifications.transient_object_id ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"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')","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"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:","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"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']","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"for all object in objects: print(lightcurve(object))","type":"text","marks":[{"type":"code"}]}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"5-- objects AND sherlock","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"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","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"FROM objects, sherlock_classifications ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objects.primaryId = sherlock_classifications.transient_object_id ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.objectid='ZTF19abhpvfq'","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"executed in 0.03 seconds","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"6-- query on candidates with specific position and time","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT candid, mjd, ra, decl, magpsf, fid","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"FROM candidates c ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE c.ra BETWEEN 191.8212890625 AND 199.9951171875 ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND c.decl BETWEEN -27.826574830853872 ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND -13.82353192939064","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"AND c.jd BETWEEN 2458581.5 AND 2458588.5 ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND c.drb >= 0.75","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"executed in 72 seconds, empty set","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This alternate objects query gets the empty set in 16 seconds:","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"SELECT objectId, ramean, decmean ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM objects ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objects.ramean BETWEEN 191.8212890625 AND 199.9951171875 ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.decmean BETWEEN -27.826574830853872 AND -13.82353192939064 ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.jdmin < 2458581.5 AND objects.jdmax > 2458588.5 ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.ncandgp > 2","type":"text","marks":[{"type":"code"}]}]}]}]}]}]},{"type":"paragraph","content":[{"text":"Empty set (15.91 sec)","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"7-- candidates and objects joined","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT DISTINCT o.objectId","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM objects o, candidates c","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE o.sherlock_classification IN ('NT')","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.jdmin > JDNOW() - 100","type":"text","marks":[{"type":"code"}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"AND o.jdmin < JDNOW() - 0 ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.ncand > 3 ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND c.objectId = o.objectId ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (c.jd > JDNOW() - 100AND c.jd < JDNOW() - 0) ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND c.magpsf < 20 AND c.drb >= 0.75 LIMIT 10","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Takes more than 5 minutes. How about this instead which is 4 seconds","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"SELECT o.objectId FROM objects o WHERE o.sherlock_classification IN ('NT') ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.jdmin > JDNOW() - 100 AND o.jdmin < JDNOW() - 0 ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.ncandgp > 3 ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (o.maggmin < 20 OR o.maggmin < 20) LIMIT 10","type":"text","marks":[{"type":"code"}]}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"8-- objects and candidates joined","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT objects.objectId, comments.content","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM objects, comments","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objects.objectId = comments.objectId","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND comments.content LIKE '% iPTF16geu%'","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Empty set in 0.05 seconds. Because the comments from Lasair Bot are TNS object names not PTF object names.","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"9-- distinct objects AND candidates join","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"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,","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"IF(c.distpsnr1 < 2 AND c.sgscore1 > 0.49, 'Within 2arcsec of PS1 star', 'Not Near PS1 star') score","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM objects o, candidates c","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE o.sherlock_classification NOT IN ( 'AGN', 'BS')AND o.jdmin > JDNOW() - 4","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.ncand > 3 AND c.objectId = o.objectId AND (c.jd > JDNOW() - 4) AND c.magpsf < 20 AND c.drb >= 0.75","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"executed in 7 seconds","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"10-- TNS AND objects","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT objectId,latestrmag, latestgmag,jdnow()-jdmax as delta, content","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM objects NATURAL JOIN comments","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE content LIKE '%TNS%'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND jdmin > JDNOW() - 30 AND jdmin < JDNOW() - 0","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (jdmin-jdmax>1 or jdmin-jdmax<-1)","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND ncand > 2","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (maggmin>latestgmag-0.3 OR magrmin>latestrmag-0.3)","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (latestgmag<17.2 or latestrmag<17.2)","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND DECMEAN>-10","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (sherlock_classification='SN'or sherlock_classification='ORPHAN')","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"executed in 0.5 seconds","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"11-- TNS AND objects","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT objects.objectId, objects.latestrmag, objects.latestgmag, objects.jdmax - jdnow() AS delta, objects.sherlock_classification, comments.content ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM objects, comments ","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objects.objectId = comments.objectId AND comments.content LIKE '%TNS%' LIMIT 10","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"executed in 0.0 seconds","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"12-- subquery","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT * FROM objects","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE ncand >= 5 AND objectId in","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":" (SELECT objectId FROM candidates WHERE drb >= 0.75 AND sgscore1 < 0.4 AND jdstarthist > 2458563.7)","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Takes a long time because of the massive subquery! Burns up temp storgae on database node!","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This query does the same and runs in 2.4 seconds:","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"SELECT * FROM objects","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objects.ncandgp >= 5 AND objects.sgscore1 < 0.4 and objects.jdmin > 2458563.7","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"LIMIT 10","type":"text","marks":[{"type":"code"}]}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"13-- subquery","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT distinct objectId, ramean, decmean, mjdmin, mjdmax, magrmin, latestrmag, sherlock_classification, ncand","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"(SELECT o.objectId, c.jd as mjd, c.fid, c.magpsf, o.jdmin as mjdmin, jdmax as mjdmax,","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"o.maggmin, o.maggmax, o.magrmin, o.magrmax, o.latestrmag, o.sherlock_classification,","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"o.ramean, o.decmean, o.ncand FROM objects o NATURAL JOIN candidates c","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE c.magpsf < 21","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND c.jd > JDNOW() - 20","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.jdmin > JDNOW() - 20","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.jdmax > JDNOW() - 3","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.ncand >= 3","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND abs(glatmean) > 10.0","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification != 'VS'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification != 'AGN'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification != 'CV'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification != 'BS'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification != 'NT'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"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'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND c.distnr > 5 ) temp ORDER BY ncand asc, mjdmax desc, mjdmin desc, magrmin desc","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Even the subquery takes more than 5 minutes. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"I wonder about the ","type":"text"},{"text":"distnr","type":"text","marks":[{"type":"code"}]},{"text":" condition, ","type":"text"},{"text":"distnr","type":"text","marks":[{"type":"code"}]},{"text":" is “distance to nearest source in reference image PSF-catalog within 30 arcsec [pixels]”","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"How about this instead which takes 24 seconds:","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"SELECT o.objectId, o.jdmin as mjdmin, jdmax as mjdmax,","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"o.maggmin, o.maggmax, o.magrmin, o.magrmax, o.latestrmag, o.sherlock_classification,","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"o.ramean, o.decmean, o.ncand FROM objects o","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE (o.maggmin < 21 OR o.magrmin < 21)","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.jdmin > JDNOW() - 20 AND o.jdmax > JDNOW() - 3","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.ncandgp >= 3 AND abs(o.glatmean) > 10.0","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification != 'VS'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification != 'AGN'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification != 'CV'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification != 'BS'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification != 'NT'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"LIMIT 10","type":"text","marks":[{"type":"code"}]}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"14-- subquery","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT distinct objectId, ramean, decmean, mjdmin, mjdmax, magrmin, latestrmag, sherlock_classification, ncand,separationArcsec","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"(SELECT o.objectId, c.jd as mjd, c.fid, c.magpsf, o.jdmin as mjdmin, jdmax as mjdmax,","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"o.maggmin, o.maggmax, o.magrmin, o.magrmax, o.latestrmag, o.sherlock_classification,","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"o.ramean, o.decmean, o.ncand, sc.separationArcsec FROM sherlock_crossmatches sc","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"JOIN objects o ON sc.transient_object_id = o.primaryid NATURAL JOIN candidates c","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE c.magpsf < 21","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND c.jd > JDNOW() - 20","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.jdmin > JDNOW() - 20","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.jdmax > JDNOW() - 5","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.ncand >= 2","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification NOT IN ('VS' , 'AGN', 'CV', 'BS','NT')","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND c.rb >= 0.4 AND c.nbad = 0 AND c.fwhm <= 5 AND c.elong <= 1.2 AND abs(c.magdiff) <= 0.","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND c.isdiffpos = 't'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (sc.rank = 1 OR sc.rank=2)","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND sc.catalogue_object_type = 'galaxy'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification = 'SN'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND sc.separationArcsec > 0.5","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND sc.separationArcsec < 100 ) temp","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Why does this need to be in a subquery??","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"How about this one that takes 10 seconds:","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"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","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM sherlock_crossmatches sc JOIN objects o ON sc.transient_object_id = o.primaryid","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE (o.maggmin < 21 OR o.magrmin < 21) AND o.jdmin > JDNOW() - 20 AND o.jdmax > JDNOW() - 5 AND o.ncandgp >= 2","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification NOT IN ('VS' , 'AGN', 'CV', 'BS','NT')","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (sc.rank = 1 OR sc.rank=2) AND sc.catalogue_object_type = 'galaxy'","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND o.sherlock_classification = 'SN' AND sc.separationArcsec > 0.5 AND sc.separationArcsec < 100 LIMIT 10","type":"text","marks":[{"type":"code"}]}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"15-- triple join","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT objects.objectId, jdnow()-jdmax as delta, comments.content","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM objects, comments, sherlock_classifications","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objects.objectId = comments.objectId AND objects.primaryId = sherlock_classifications.transient_object_id","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.jdmin>jdnow()-30 AND objects.jdmin0.1 or objects.jdmin-objects.jdmax<-0.1)","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND ncand>2 AND ncand<20 AND (maggmin>latestgmag-0.5 or magrmin>latestrmag-0.5) AND (latestgmag<17.3 or latestrmag<17.3)","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND comments.content LIKE '%TNS%' AND comments.content LIKE '%AT20%'AND decmean>-20","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND sherlock_classifications.classification NOT IN (\"VS\" , \"AGN\", \"CV\", \"BS\", \"NT\")","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Executes in 1.6 seconds","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"16-- quadruple join","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT distinct objects.objectId, ramean, decmean, jdmin-2400000.5 AS mjdmin, jdmax-2400000.5 AS mjdmax, magrmin, latestrmag, sherlock_classifications.classification, ncand,","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"(sherlock_crossmatches.distance*1000)TAN((sherlock_crossmatches.separationArcsec/3600)(PI()/180)) as sep","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM candidates, objects, sherlock_classifications, sherlock_crossmatches","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objects.objectId = candidates.objectId","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.primaryId = sherlock_classifications.transient_object_id","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.primaryId = sherlock_crossmatches.transient_object_id","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.jdmax > JDNOW() - 15.00000","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND candidates.jd > JDNOW() - 15.00000","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND candidates.magpsf < 21.5","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.ncand >= 2 AND objects.ncand < 5","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.latestgmag - objects.latestrmag < 0.2","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND sherlock_classifications.classification NOT IN (\"VS\" , \"AGN\", \"CV\", \"BS\")","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND candidates.drb >= 0.6","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.latestgmag - candidates.sgmag1 < 0.5","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND candidates.isdiffpos = \"t\"","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (sherlock_crossmatches.rank = 1)","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND sherlock_crossmatches.catalogue_object_type = \"galaxy\"","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND sherlock_classifications.classification = \"SN\"","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Executes in 43 seconds","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This version avoids the join with the candidates table and takes only 0.7 seconds:","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"SELECT distinct objects.objectId, ramean, decmean, jdmin-2400000.5 AS mjdmin, jdmax-2400000.5 AS mjdmax, magrmin, latestrmag, sherlock_classifications.classification, ncand,","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"(sherlock_crossmatches.distance*1000)TAN((sherlock_crossmatches.separationArcsec/3600)(PI()/180)) as sep","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM objects, sherlock_classifications, sherlock_crossmatches","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objects.primaryId = sherlock_classifications.transient_object_id","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.primaryId = sherlock_crossmatches.transient_object_id","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.jdmax > JDNOW() - 15.00000","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (objects.maggmin < 21 OR objects.magrmin < 21)","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.ncandgp >= 2 AND objects.ncand < 5","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.latestgmag - objects.latestrmag < 0.2","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND sherlock_classifications.classification NOT IN (\"VS\" , \"AGN\", \"CV\", \"BS\")","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.latestgmag - objects.sgmag1 < 0.5","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND (sherlock_crossmatches.rank = 1)","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND sherlock_crossmatches.catalogue_object_type = \"galaxy\"","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND sherlock_classifications.classification = \"SN\"","type":"text","marks":[{"type":"code"}]}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"17-- watchlist using sql","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"SELECT jd,ra,decl FROM candidates","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE candidates.jd > JDNOW() - 10.00000","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND ( ((ra BETWEEN 190.44 AND 198.67) AND (decl BETWEEN -78.32 AND -76.49))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 241.49 AND 243.95) AND (decl BETWEEN -40.41 AND -36.83))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 239.60 AND 241.14) AND (decl BETWEEN -43.12 AND -40.71))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 233.92 AND 237.04) AND (decl BETWEEN -35.91 AND -32.10))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 190 AND 244 ) AND (decl BETWEEN -79 AND -32))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 250.49 AND 251.90) AND (decl BETWEEN -22.89 AND -20.24))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 244.63 AND 251.85) AND (decl BETWEEN -2614 AND -22.09))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 244 AND 252 ) AND (decl BETWEEN -27 AND -20))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 82.99 AND 86.21 ) AND (decl BETWEEN -10.22 AND -4.57))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 85.06 AND 87.16 ) AND (decl BETWEEN -2.66 AND 0.93))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 82 AND 88 ) AND (decl BETWEEN -11 AND 1))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 50.66 AND 57.26 ) AND (decl BETWEEN 29.62 AND 33.16))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 276.80 AND 277.84) AND (decl BETWEEN -0.55 AND 1.86))","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"OR ((ra BETWEEN 61.85 AND 73.27 ) AND (decl BETWEEN 21.00 AND 30.02)))","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Executes in 14 seconds","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"18-- noncandidates","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"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,","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"IF(candidates.distpsnr1 < 2 AND candidates.sgscore1 > 0.49, \"Within 2 arcsec of PS1 star\", \"Not Near PS1 star\") score","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM candidates, noncandidates, objects, sherlock_classifications","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objects.objectId = candidates.objectId","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.objectId = noncandidates.objectId","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"AND objects.primaryId = sherlock_classifications.transient_object_id","type":"text","marks":[{"type":"code"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Takes many minutes and does not finish … does not actually need either candidates or noncandidates table, this executes in 0.0 seconds:","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"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,","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"IF(objects.distpsnr1 < 2 AND objects.sgscore1 > 0.49, \"Within 2 arcsec of PS1 star\", \"Not Near PS1 star\") score","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"FROM objects, sherlock_classifications","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"WHERE objects.primaryId = sherlock_classifications.transient_object_id","type":"text","marks":[{"type":"code"}]},{"type":"hardBreak"},{"text":"LIMIT 10","type":"text","marks":[{"type":"code"}]}]}]}]}],"version":1}