Where is the break point? How many attribute indices are too many? - Kirk
----- Original Message ----- From: Jacek Becla <[EMAIL PROTECTED]> Date: Wednesday, July 5, 2006 7:47 pm Subject: Re: [LSST-data] [Fwd: Re: [lsst-slac] Cone-magnitude-color search] > Kirk, > > It only helps if we have indexes for each and every one attribute > that is selected, if even one is missing, we still have to pay > the penalty of fetching data rows. If the number of selected > attributes is going to be much larger than what I listed, we will > not be able to keep indexes for all of them (plus at some point > it is better to fetch the data row than to merge data from > tens of index files...) > > Incidentally, it might help SQL Server, Ani mentioned that SQL Server > will likely not use indexes for queries that SELECT * > > Jacek > > > > > Kirk Borne wrote: > > For different reasons, but with same effect, the HST ARchive > > group (where I worked 11 years ago) decided to use VIEWs. > > Eventhough the VIEW will not be materialized (in the LSST DB), > > it does limit the number of attributes selected with "SELECT *" > > catch-all queries. For HST, we developed a SCIENCE table > > view, which included only the most likely interesting > > attributes for scientists. It was still a significant > > number of attributes, but much less than the underlying > > database tables that fed into it. Maybe we could try something > > similar for LSST, since maybe not every astronomer who > > issues a "SELECT *" query really wants E-V-E-R-Y-T-H-I-N-G. > > > > To see which attributes the HST SCIENCE table provides, look here: > > > > http://archdev.stsci.edu:8080/hst/manual/datadesc.htm#962859 > > > > Another subset of attributes is available in the HST AEC = Archived > > Exposures Catalog, which I invented 16 years ago, and it is still > > generated and in use today.... (amazing!) .... > > > > http://archdev.stsci.edu:8080/hst/aec.html > > > > The AEC has been quite valuable for a number of organizations > > who want to maintain their own small-ish local version of the HST > > science catalog for their own local queries and analyses. > > > > Therefore, I think Kem's comment can still be correct, > > but for a smaller number of attributes. However, what I don't > > know is whether the above limitations in the number of attributes > > help Jacek's situation. > > > > - Kirk > > > > > > > >>From [EMAIL PROTECTED] Wed Jul 5 18:09:00 2006 > >>Date: Wed, 05 Jul 2006 15:05:48 -0700 > >>From: Jacek Becla <[EMAIL PROTECTED]> > >>Subject: [LSST-data] [Fwd: Re: [lsst-slac] Cone-magnitude-color > search]>>To: LSST Data Management <[email protected]> > >> > >>Keywords: DataAccWG > >> > >>Sorry, sent to the wrong mailing list the first time... > >> > >>Jacek > >> > >> > >> > >>-------- Original Message -------- > >>Subject: Re: [lsst-slac] Cone-magnitude-color search > >>Date: Wed, 5 Jul 2006 14:18:48 -0700 (PDT) > >>From: Kem Cook <[EMAIL PROTECTED]> > >>Reply-To: lsst-llnl-slac collab <[EMAIL PROTECTED]> > >>To: lsst-llnl-slac collab <[EMAIL PROTECTED]> > >>CC: lsst-llnl-slac collab <[EMAIL PROTECTED]> > >>References: <[EMAIL PROTECTED]> > >> > >>Most school kids would want time, mag, magerr, colors, > colorserrs for each > >>objID returned. Professional astronomers would probably want > everything.>> > >>Kem > >> > >> > >>>Keywords: DataAccWG > >>> > >>>Hi Kem and Kirk, > >>> > >>>Now that I started to assume we have memory for caching indexes, > >>>I need to know whether selecting everything from Object table > >>>for the "Cone-magnitude-color search" > >>> > >>>SELECT * > >>>FROM Object > >>>WHERE ra BETWEEN <Ra1> AND <Ra2> > >>> AND decl BETWEEN <Decl1> AND <Decl2> > >>> AND zMag BETWEEN <z1> AND <z2> > >>> AND gMag-rMag BETWEEN <gr1> AND <gr2> > >>> AND iMag-zMag BETWEEN <iz1> AND <iz2> > >>> > >>>is realistic. The reason I am asking is that if user wanted > >>>objectId, ra, dec, plus all colors plus all magnitudes instead > >>>of "everything", we could do this query without fetching > >>>data rows. The "select *" in this query alone is driving number > >>>of disks up by 23% (assuming 300 low volume queries per > >>>10 high volume...). > >>> > >>>thanks, > >>>Jacek > >>>_______________________________________________ > >>>lsst-slac mailing list > >>>[EMAIL PROTECTED] > >>>http://www.lsstmail.org/mailman/listinfo/lsst-slac > >>> > >> > >>_______________________________________________ > >>lsst-slac mailing list > >>[EMAIL PROTECTED] > >>http://www.lsstmail.org/mailman/listinfo/lsst-slac > >>_______________________________________________ > >>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 > > _______________________________________________ LSST-data mailing list [email protected] http://www.lsstmail.org/mailman/listinfo/lsst-data
