Keywords: DataAccWG
Hi
I solved the problem with the "select time series data
for a given cone" query which used to trigger disk io
impossible to handle.
Original query:
SELECT o.objectId, o.ra, o.dec, i.MJD, d.*
FROM Object o, CCDImage i, DIASource d
WHERE o.ra BETWEEN ra1 + cos(decl1)*(0.5)
AND ra1 - cos(decl1)*(0.5)
AND o.decl between decl1 +0.5 and decl1 - 0.5
AND o.objectId = d.objectId
AND d.ccdImageId = i.ccdImageId
ORDER BY o.objectId, i.MJD ASC
Later we renamed MJD to TAI and added TAI column
to DIASource, so it became:
SELECT o.objectId, o.ra, o.dec, d.TAI, d.*
FROM Object o, DIASource d
WHERE o.ra BETWEEN <ra1> + cos(<decl1>)*(0.5)
AND <ra1> - cos(<decl1>)*(0.5)
AND o.decl between <decl1> +0.5 and <decl1> - 0.5
AND o.objectId = d.objectId
ORDER BY o.objectId, d.MJD ASC
By we actually have all the information inside DIASource,
so we can simply do
SELECT *
FROM DIASource
WHERE ra BETWEEN <ra1> + cos(<decl1>)*(0.5)
AND <ra1> - cos(<decl1>)*(0.5)
AND decl between <decl1> +0.5 and <decl1> - 0.5
ORDER BY objectId, MJD ASC ?
For such query, assuming ra,dec, objectId, mjd indexes
in memory (some of them are used by other queries,
so it is worth it), the disk io cost becomes
very reasonable - basically comparable to other
queries. The RAM size needed increased by 0.5TB
to 2.8 TB, but I don't think that is a big deal
(an alternative is to purchase 600,000 extra disks)
Jacek
_______________________________________________
LSST-data mailing list
[email protected]
http://www.lsstmail.org/mailman/listinfo/lsst-data
