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
