Jacek can tell you that BaBar uses "tag tables" (vertical partitions of fat tables that select the popular rows). This has a big payoff in both bandwidth and also allows some very popular data to live in ram.
Database indices can serve as automatically maintained tag tables (SDSS has a 50-column tag table that subsets the 400 column PhotoObj table). If the read:write ratio is high, then maintaining the tag table is a very good deal. Modern DBs have a "wizard" that monitors activity and recommends what indices you need. They are already better than people (they give a 6x speedup on the SDSS query set -- a substantial improvement over my design :< ! ). The bad news is that maintaining these indices costs IO (the also consume space, but that is not a scarce resource). The good news is that the index maintenance task is partition able and can be "batched" and "sorted" to make the index maintenance a sequential update (the fastest way). So, indeed, we do not need to fine tune the index design much before 2011. All we need to know is that we are disk arm limited, so we can waste disk space to save arms (e.g. do not use RAID5). The challenge is to find ways to do that. Jim Gray Microsoft Research, Suite 1690, 455 Market, SF CA 94105, tel: 415 778 8222 fax: 425 706 7329 [EMAIL PROTECTED] http://research.Microsoft.com/~gray -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jacek Becla Sent: Wednesday, July 05, 2006 9:08 PM To: Kirk D Borne Cc: LSST Data Management Subject: Re: [LSST-data] [Fwd: Re: [lsst-slac] Cone-magnitude-color search] The answer is not simple, especially for 2013. It depends how large the data row is. We are talking about Object table, for which we estimate 1.8KB per row so fetching 10-20 indexes x 8 bytes sounds like a good deal, but a) maintaining indexes is expensive (index has to be rebuilt when new data is added, indexes take disk space (and RAM)), so we really have to have good reasons to build an index. b) merging data from indexes costs CPU. If we knew which rows would be needed, we could maintain a composite index which should mitigate the need to merge indexes. A database engine would probably choose to fetch a data rows instead of merging more than a handful indexes (we can send hints to optimizers to force different behavior). My recommendation would be to not fine tune it at this early stage, if the common set of indexes is not sufficient, let's assume fetching data rows, especially if we are talking about fetching larger fraction of Object row. If we fetch only 20%, that is 360 bytes, so around 50 columns, way too many to consider avoiding fetching data rows. Jacek Kirk D Borne wrote: > 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 _______________________________________________ LSST-data mailing list [email protected] http://www.lsstmail.org/mailman/listinfo/lsst-data
