That looks like a winning solution to me! I think that in your final query below, you want to ORDER BY objectId, TAI ASC not this: ORDER BY objectId, MJD ASC
and therefore the indexes that you store in RAM are ra, dec, objectId, and tai. Thanks Jacek. - Kirk > Date: Fri, 07 Jul 2006 00:51:16 -0700 > From: Jacek Becla <[EMAIL PROTECTED]> > Subject: [LSST-data] "select time series data" query problem solved > To: LSST Data Management <[email protected]> > > 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 _______________________________________________ LSST-data mailing list [email protected] http://www.lsstmail.org/mailman/listinfo/lsst-data
