Query speed booster

Authors: Héctor Cánovas, Jos de Bruijne, and Alcione Mora

Writing efficient ADQL queries is not trivial and it is relatively common to introduce small mistakes or inefficiencies that significantly delay the query execution time. Fortunately, there are many tricks that boost the query performance and that can be implemented with very little effort. In most cases, these tricks can be combined to further reduce the computing time, as detailed in this intermediate level tutorial. 

Tutorial content:

1. Use gaia_source_lite
2. Don't be lazy: don't SELECT *
3. Use indexed fields + pre-computed fields + constraints
4. Optimise the cross-matches
5. Be careful with TOP + ORDER BY 
6. Randomise
7. Divide and conquer

1. Use gaia_source_lite

The third Gaia data release, Gaia DR3, includes for the first time a column-wise reduced version of its main catalogue: the "gaiadr3.gaia_source_lite" table. This shortened dataset only contains 51 selected fields from the "gaiadr3.gaia_source" catalogue (click on top of the table name in the tables tree to inspect it), but the number of sources (rows) in both tables remains identical. This reduction makes it possible to substantially improve the performance of various types of ADQL queries - obviously as long as the queries do not involve fields that have been excluded from "gaiadr3.gaia_source_lite". Three representative queries that benefit from targeting this table (instead of the full table "gaiadr3.gaia_source") are listed below.

SELECT COUNT(*)
   FROM gaiadr3.gaia_source_lite
   WHERE phot_g_mean_mag <= 18.25
   AND has_mcmc_msc = 'True'

SELECT ROUND(gs.logg_gspphot) AS logg, COUNT(*)
   FROM gaiadr3.gaia_source_lite AS gs
   WHERE gs.phot_g_mean_mag < 19
   GROUP BY logg ORDER BY logg

SELECT COUNT(gaia.source_id)
FROM gaiadr3.gaia_source_lite AS gaia
INNER JOIN gaiaedr3.tmass_psc_xsc_best_neighbour AS tmass_x USING (source_id)
INNER JOIN gaiadr1.tmass_original_valid AS tmass
   ON tmass.tmass_oid = tmass_x.clean_tmass_psc_xsc_oid
INNER JOIN gaiaedr3.allwise_best_neighbour AS wise_x USING (source_id)
INNER JOIN gaiadr1.allwise_original_valid AS wise USING(allwise_oid)
WHERE gaia_healpix_index(5, gaia.source_id) = 1151

2. Don't be lazy: don't select *

In many situations, users only need a few fields (columns) from a catalogue for their science. But, admittedly, writing a "SELECT *" statement is way more easy than specifying one by one the list of the required fields. However, when doing so, one is forcing the database to read and store the content of all the catalogue fields per explored row. As a result, the query execution time increases significantly when analysing catalogues with many fields, like gaiadr3.gaia_source or gaiadr3.astrophysical_parameters (both contain more than 150 columns). In addition, the associated job size also increases and, in several cases, it can exceed 1 GB. This will contribute to fill up your user space (the job results quota is large but not unlimited, as explained in this FAQ). For example, the following query:

SELECT source_id, ra, dec, pmra, pmdec, parallax
FROM gaiadr3.gaia_source
WHERE DISTANCE(POINT(81.28, -69.78), POINT(ra, dec)) < 1

is executed in ~3 minutes, and its job size is 120 MB. The same query but selecting all fields (SELECT * ...) roughly takes 2x longer to finish and its job size is 1.1 GB. In short, we recommend to minimise the use of the SELECT * statement to execute faster queries. 

3. Use indexed fields + pre-computed fields + constraints

Imagine one is interested in retrieving very blue stars in Gaia DR3. A naive way of implementing the query could be:

SELECT *
FROM gaiadr3.gaia_source
WHERE phot_bp_mean_mag - phot_rp_mean_mag < -2

This operation is too costly and prevents the request from being completed within the normal time-out limit. However, the same query while taking advantage of the indexed and pre-computed field bp_rp (= phot_bp_mean_mag - phot_rp_mean_mag) finishes in less than 2 minutes. 

SELECT *
FROM gaiadr3.gaia_source
WHERE bp_rp < -2

Examples of other indexed pre-computed fields are parallax_over_error (= parallax / parallax_error), pm (=SQRT(pmra * pmra + pmdec * pmdec)), phot_g_mean_flux_over_error (= phot_g_mean_flux / phot_g_mean_flux_error), and g_rp (= phot_g_mean_mag - phot_rp_mean_mag).

In fact, part of the preparation for each Gaia data release has been the identification, creation, and indexation of the most common derived quantities to be used in queries. The information on a given field being indexed or not can be retrieved from a number of places. Probably the most convenient way is to inspect the table content using the tables tree in the Archive web interface and looking for the table fields (columns) that are highlighted in bold font, as illustrated by Fig. 2 of the Advanced (ADQL) tutorial. 

Although several pre-computed fields have been added, the quantity of interest may often not be pre-computed. In those cases, adding additional restrictions based on indexed fields might be the difference between a successful and a failed (timing-out) query. For example, the following query applies constraints in the RUWE and parallax_over_error quality indicators:

SELECT phot_g_mean_mag - 5.0 * LOG10(distance_gspphot) + 5.0 AS phot_g_mean_mag_absolute, bp_rp
FROM gaiadr3.gaia_source
WHERE DISTANCE(POINT(266.41683, -29.00781),POINT(ra, dec)) < 2.0
-- Extra constraints
AND ruwe < 1.4
AND parallax_over_error > 10.0

Excluding these two constraints delays the execution time by a factor of ~7.

4. optimise the cross-matches

Cross-matching catalogues by coordinates is one of the most common operations executed in the Gaia ESA Archive. As explained in the tutorials included in the "Combine with other data" section of the Archive Help pages, an inefficient setting of the query that launches the cross-match can increase the execution time by a factor of 3 or more. The following query retrieves the first 1000 occurrences of a geometric cross-match between Gaia DR3 and the Hipparcos catalogue, and it is executed in roughly 2.5 minutes:

SELECT TOP 1000 gaia.source_id, gaia.ra, gaia.dec, gaia.parallax, hip.*
FROM public.hipparcos AS hip, gaiadr3.gaia_source AS gaia
WHERE
 DISTANCE(POINT(gaia.ra, gaia.dec),POINT(hip.ra, hip.de)) < 1./3600

However, this conceptually similar query:

SELECT TOP 1000 gaia.source_id, gaia.ra, gaia.dec, gaia.parallax, hip.*
FROM public.hipparcos AS hip
JOIN gaiadr3.gaia_source AS gaia
  ON DISTANCE(POINT(hip.ra, hip.de),POINT(gaia.ra, gaia.dec)) < 1./3600

takes only ~5 seconds to be executed (nearly a factor 30 faster!). This is because in the second query:

• the coordinates of the smaller catalogue being cross-matched (Hipparcos in this example) are placed before the coordinates of the larger catalogue, and
• an explicit join (JOIN + ON) is used instead of an implicit one (FROM multiple tables + WHERE).

Note that, as explained in the the "Combine with other data" tutorial:

  Warning! due to the way in which the Archive database works, retrieving fields from the large catalogue that only include positional coordinates (e.g., "SELECT gaia.ra, gaia.dec FROM ... ", "SELECT gaia.ra, gaia.dec, small_cat.* FROM ... ") or retrieving only fields from the small catalogue (e.g., "SELECT small_cat.* FROM ...", "SELECT small_cat.ra, small_cat.dec FROM ...", ) yields very inefficient queries. In these cases, the Archive response will benefit from adding a non-positional dummy field from the large catalogue in the SELECT statement, for instance a magnitude field.

5. Be careful with top + order by

It is a common mistake to assume that relational databases (like the one behind the Gaia ESA Archive) behave like humans when exploring large catalogues. The last query in the previous example is usually executed in ~5 seconds. Let's imagine that we want to sort its output by increasing parallax, in ascending order. Probably, we would try to execute something like

SELECT TOP 1000 gaia.source_id, gaia.ra, gaia.dec, gaia.parallax, hip.*
FROM public.hipparcos AS hip
JOIN gaiadr3.gaia_source AS gaia
   ON DISTANCE(POINT(hip.ra, hip.de),POINT(gaia.ra, gaia.dec)) < 1./3600
ORDER BY parallax ASC

However, this query times out (so its execution time exceeds 2 hours) due to the combined effect of the TOP + ORDER BY clauses in the query execution plan. This is because in the backend, the database is trying to first sort the entire catalogue by random_index, and then extract the first 1000 elements. If you really want the output to be sorted by a given field, then we recommend to divide the query in two steps as follows. First, execute the query excluding the "ORDER BY <field>". Then, apply the job_upload mechanism (see the Advanced ADQL features tutorial for details) as follows:

SELECT *
FROM job_upload."job<job_id>"
ORDER BY <field>

The <job_id> is the unique code associated to each job (e.g., 1654361201479O) that is listed in the Jobs list area (see the Advanced (ADQL) tab tutorial for details). This last query usually finishes in a few seconds.

6. Randomise

Sometimes, using a representative random sample is sufficient to answer a scientific question. Naively, users might think to simply use the first (say) 1,000,000 rows of a table using the TOP function but this is extremely dangerous: a Postgres database does not maintain a fixed (or logical) order of the rows so use of the TOP function may lead to seriously biased results. A fixed, pre-assigned random index has therefore been introduced in a column in the main Gaia catalogues. In all cases, the column random_index runs from zero to the total number of elements (rows) minus one (and the total table size in rows can be easily found in the pop-up window of the web interface of the Archive that opens when clicking on top of the table name in the tables tree). For example, the following query shows how to construct an absolute G magnitude vs BP-RP colour diagram from a random sample extracted from Gaia DR3.

SELECT source_id, phot_g_mean_mag + 5.0 * log10(parallax) - 10.0 as g_mag_abs, bp_rp
FROM gaiadr3.gaia_source_lite
WHERE parallax_over_error >= 5
AND phot_bp_mean_flux_over_error > 0
AND phot_rp_mean_flux_over_error > 0
AND SQRT(POWER(2.5/log(10) / phot_bp_mean_flux_over_error, 2) + POWER(2.5/log(10) / phot_rp_mean_flux_over_error, 2)) <= 0.05
AND random_index BETWEEN 0 AND 10000000

This query usually takes ~20 minutes to be executed, and its output contains 937,877 sources. A quick TOPCAT plot is shown in the left panel of Fig. 1, showing many prominent features.

Alternatively, you may be interested in constructing a histogram to identify global trends. The following (fairly advanced) query shows how to construct the equivalent histogram with bin sizes of 0.1 in BP-RP and G absolute magnitude in two consequetive steps to minimise the query execution time:

SELECT source_id, FLOOR((phot_g_mean_mag+5.0*log10(parallax)-10.0) * 10) AS g_mag_abs_index, FLOOR(bp_rp * 10) as bp_rp_index
FROM gaiadr3.gaia_source
WHERE parallax_over_error >= 5
AND phot_bp_mean_flux_over_error > 0
AND phot_rp_mean_flux_over_error > 0
AND SQRT(POWER(2.5/log(10) / phot_bp_mean_flux_over_error, 2) + POWER(2.5/log(10) / phot_rp_mean_flux_over_error, 2)) <= 0.05
AND random_index BETWEEN 0 AND 10000000

followed by a query that uses the previous one (which in this example has job_id 1654361201479O) as input:

SELECT bp_rp_index / 10 AS bp_rp, g_mag_abs_index / 10 AS g_mag_abs, COUNT(*) AS n
FROM job_upload.job1654361201479O
GROUP BY bp_rp_index, g_mag_abs_index
ORDER BY bp_rp_index, g_mag_abs_index

Execution time ~0.5 minutes; output = 5928 sources. The output of this query is shown in the right panel of Fig. 1 below.

Fig. 1. Left: Colour-magnitude diagram produced with the first query in Sect. 6. Right: Histogram produced with the the second and third queries in Sect. 6. The colour bar indicates the number of sources per bin, in log scale. Both plots have been generated with TOPCAT.

7. Divide and conquer

Sometimes, a query is simply too large to be executed in one go, and the results of multiple requests need to be combined afterwards. Using the random index for chopping the query into pieces is usually advisable, to control the output size and to equalise the workload for each segment. For example, imagine we want to retrieve points for the colour-magnitude diagram shown in the previous section but for a final sample that contains ~10 million sources. The first query in Sect. 6 above retrieves roughly 1 million sources and is executed in ~20 minutes. Therefore, trying to retrieve 10 million sources in one single query will take ~200 minutes, which is beyond the query execution time limit (as explained in this FAQ). In those cases, we recommend to split the query in several chunks by random index and combine all the outputs afterwards. For example, you can run consecutive instances of the first query in Sect. 6 by simply doing a first execution using "... BETWEEN 0 AND 10000000" followed by a second one using  "... BETWEEN 10000001 AND 20000000", and so on - note the BETWEEN operator includes results at both limits.

Sometimes, we are not interested in scanning a fixed number of entries in the catalogue, but a fraction. Imagine we want to scan the second 1% chunk of the catalogue in search for points for the absolute magnitude vs colour diagram. The random index can still help us when combined with the table size (remember, 1811709771 rows for gaiadr3.gaia_source):

SELECT source_id, phot_g_mean_mag + 5.0 * log10(parallax)- 10.0 as g_mag_abs, bp_rp
FROM gaiadr3.gaia_source
WHERE parallax_over_error >= 5
AND phot_bp_mean_flux_over_error > 0
AND phot_rp_mean_flux_over_error > 0
AND SQRT(POWER(2.5/log(10) / phot_bp_mean_flux_over_error, 2) + POWER(2.5/log(10) / phot_rp_mean_flux_over_error, 2)) <= 0.05
AND random_index BETWEEN 1 * 0.01 * 1811709771 AND 2 * 0.01 * 1811709771 - 1

Please do NOT include conditions on integer division remainders between the random_index and the fraction to explore. That is, for the previous example:

WHERE MOD(random_index, 100) = 1

Even though they are formally equivalent conditions, the data base would take a very long time and eventually fail, because an operation would be carried out on each row, instead of a quick selection on random_index values.

Alternatively, for downloads of large fractions of the dataset, we strongly recommend to make use of the bulk download repository that we offer, as explained in this tutorial. Whereas it is reasonable to launch up to a few dozen queries to collect results in chunks, as explained above, users facing the need to launch hundreds or thousands of queries are kindly advised to offload the TAP+ service and use the bulk download repository.

Ask for longer execution time quota

If all of the above advise has been tried and everything fails, and if this is the only way your science can be done, please send a motivated request to the Gaia Helpdesk with an estimate on how much additional time or user space quota is needed. Include, when possible, the offending queries because they can sometimes be optimised.

Gaia Collaboration queries

This page contains many useful ADQL queries extracted from the DPAC documentation, including DPAC articles.

Content:

1. Gaia FPR selected queries
2. Gaia (E)DR3 selected queries
3. Gaia DR2 selected queries
4. Gaia DR1 selected queries

Gaia FPR selected queries

UPDATED ASTROMETRY FOR SOLAR SYSTEM OBJECTS

The following queries can be found in the Appendix section of the article: "Gaia Focused Product Release: Asteroid orbital solution" (Gaia Collaboration, P. David , et al. 2023):

Use case: I want to retrieve the state vector and semi-major axis uncertainty for all the valid orbital solutions, also excluding objects for which the heliocentric orbit cannot be computed (natural planetary satellites).
Notes: Note the filter for the first element of the covariance matrix, which turns out to be sometimes negative when the orbit solution is poor.
Target tables: gaiafpr.sso_source
Query:

SELECT number_mp, denomination, epoch_state_vector,
ESDC_ARRAY_ELEMENT(h_state_vector,1) AS x, ESDC_ARRAY_ELEMENT(h_state_vector,2) AS y, ESDC_ARRAY_ELEMENT(h_state_vector,3) AS z,
ESDC_ARRAY_ELEMENT(h_state_vector,4) AS vx,ESDC_ARRAY_ELEMENT(h_state_vector,5) AS vy, ESDC_ARRAY_ELEMENT(h_state_vector,6) AS vz,
SQRT(ESDC_ARRAY_ELEMENT(orbital_elements_var_covar_matrix,1)) AS sigma_a
FROM gaiafpr.sso_source
WHERE ESDC_ARRAY_ELEMENT(orbital_elements_var_covar_matrix,1) > 0.
ORDER BY number_mp ASC

Use case: I want to retrieve all the astrometric measurements for a given object based on its identifier.
Notes: 
Target tables: gaiafpr.sso_observation
Query:

SELECT * FROM gaiafpr.sso_observation
WHERE number_mp=704

Use case: I want to retrieve the astrometry for all asteroids associated to the best valid orbits with σ_a <1 x 10⁻¹⁰ au
Notes: 
Target tables: gaiafpr.sso_source, gaiafpr.sso_observation 
Query:

SELECT astrom.*
FROM gaiafpr.sso_observation AS astrom
INNER JOIN
gaiafpr.sso_source AS so USING(number_mp)
WHERE ESDC_ARRAY_ELEMENT(so.orbital_elements_var_covar_matrix,1) > 0. AND
SQRT(ESDC_ARRAY_ELEMENT (so.orbital_elements_var_covar_matrix,1)) < 1.e-10 AND
astrom.is_rejected = 'false'

Use case: I want to compute the time span covered by the observations of each asteroid
Notes: 
Target tables: gaiafpr.sso_source, gaiafpr.sso_observation 
Query:

SELECT number_mp, MAX(epoch) - MIN(epoch) AS t_delta_days
FROM (	SELECT astrom.number_mp, astrom.epoch
	FROM gaiafpr.sso_observation AS astrom
	INNER JOIN gaiafpr.sso_source AS so USING(number_mp)) AS subquery
WHERE(number_mp > 0)
GROUP BY number_mp

DIFFUSE INTERSTELLAR BANDS FROM AGGREGATED RVS SPECTRA

See, for reference, the article: "Gaia Focused Product Release: Spatial distribution of two diffuse interstellar bands" (Gaia Collaboration, M. Schultheis , et al., 2023)

Use case: I want to retrieve the (stacked) interestellar medium spectra contained in a given region of the sky.
Notes: 
Target tables: gaiafpr.interstellar_medium_spectra
Query:

SELECT *
FROM gaiafpr.interstellar_medium_spectra AS gaia
WHERE (gaia.lc > 10.0 AND gaia.lc < 15.0 AND gaia.bc > 2.0 AND gaia.bc < 6.0)

Use case: I want to retrieve the (stacked) interestellar medium spectra associated to the line of sight of a given source.
Notes: The GAIA_HEALPIX_INDEX UDF extracts the HEALPix level of order "norder" that is encoded in the gaia_source field.
Target tables: gaiafpr.interstellar_medium_spectra
Query:

SELECT *
FROM gaiafpr.interstellar_medium_spectra
WHERE healpix = GAIA_HEALPIX_INDEX(5, 5997091358307172224)

Use case: I want to retrieve the DIB parameters derived from the interestellar medium spectra associated to the line of sight of a given source.
Notes: The GAIA_HEALPIX_INDEX UDF extracts the HEALPix level of order "norder" that is encoded in the gaia_source field. 
Target tables: gaiafpr.interstellar_medium_spectra
Query:

SELECT *
FROM gaiafpr.interstellar_medium_params
WHERE healpix = GAIA_HEALPIX_INDEX(5, 5997091358307172224)

ASTROMETRY AND PHOTOMETRY FROM ENGINEERING IMAGES TAKEN IN THE OMEGA CENTAURI REGION

See, for reference, the article: "Gaia Focused Product Release: Sources from Service Interface Function image analysis - half a million new sources in omega Centauri" (Gaia Collaboration, K. Weingrill, et al., 2023)

Use case: I want to retrieve all the Gaia SIF CF and Gaia DR3 sources within 0.8 degree radius around Omega centauri (ω Cen) cluster centre.
Notes: The field "n_scans" does not exist in "gaiadr3.gaia_source", so a value of -1 is used in the query in order to combine the output of the two queries. See this tutorial to learn more about the ADQL UNION operator.
Target tables: gaiafpr.crowded_field_source, gaiadr3.gaia_source
Query:

SELECT source_id, designation, ref_epoch, ra, ra_error, dec, dec_error, pmra, pmra_error, pmdec, pmdec_error, phot_g_mean_mag, l, b, n_scans
FROM gaiafpr.crowded_field_source
UNION
SELECT source_id, designation, ref_epoch, ra, ra_error, dec, dec_error, pmra, pmra_error, pmdec, pmdec_error, phot_g_mean_mag, l, b, -1 AS nscans
FROM gaiadr3.gaia_source
WHERE DISTANCE(POINT(201.69399972775088, -47.484610741298994), POINT(ra, dec)) <0.8

EXTENDED RADIAL VELOCITY EPOCH DATA FOR LONG PERIOD VARIABLES

The following queries can be found in the Appendix D of the article: "Gaia Focused Product Release: Radial velocity time series of long-period variables" (Gaia Collaboration, M. Trabucchi, et al., 2023)

Use case: I want to retrieve selected quantities from the source catalog and LPV FPR catalog for all LPV FPR sources.
Notes: 
Target tables: gaiadr3.gaia_source, gaiafpr.vari_long_period_variable
Query:

SELECT gs.source_id,gs.ra, gs.dec, fprlpv.frequency_rv, fprlpv.frequency_rv_error,fprlpv.amplitude_rv, fprlpv.flag_rv
FROM gaiadr3.gaia_source AS gs
LEFT JOIN gaiafpr.vari_long_period_variable AS fprlpv
ON gs.source_id = fprlpv.source_id
WHERE fprlpv.source_id IS NOT NULL

Use case: I want to retrieve all data from the gaiadr3.gaia_source catalogue, the LPV FPR catalogue, the RV statistics table, and the tables of data on LPVs from DR3 for all sources published in the LPV FPR
Notes: 
Target tables: gaiadr3.gaia_source, gaiafpr.vari_long_period_variable, gaiadr3.vari_long_period_variable, gaiadr3.vari_summary
Query:

SELECT *
FROM gaiadr3.gaia_source AS src
LEFT JOIN gaiafpr.vari_long_period_variable AS fprlpv USING (source_id)
LEFT JOIN gaiafpr.vari_rad_vel_statistics AS rvstat USING (source_id)
LEFT JOIN gaiadr3.vari_long_period_variable AS lpv USING (source_id)
LEFT JOIN gaiadr3.vari_summary AS varistat USING (source_id)
WHERE fprlpv.source_id IS NOT NULL

Use case: I want to retrieve all the RV time series published as part of this FPR using a source.
Notes: 
Target tables: gaiafpr.interstellar_medium_spectra
Query:

SELECT fprlpv.source_id, varirv.transit_id,varirv.rv_obs_time,varirv.radial_velocity,varirv.radial_velocity_error, varirv.rejected_by_variability
FROM gaiafpr.vari_long_period_variable AS fprlpv
LEFT JOIN gaiafpr.vari_epoch_radial_velocity AS varirv USING (source_id)
WHERE fprlpv.source_id IS NOT NULL

Gaia (E)DR3 selected queries

Content:

1. Integrated extragalactic tables
2. Astrophysical parameters tables
3. The celestial reference frame (Gaia-CRF3)
4. Miscellaneous

Integrated extragalactic tables

The integrated extragalactic tables (see Chapter 12 in the Gaia DR3 documentation) are composed of sources stemming from various DPAC processing modules, with overlaps between the corrresponding sub-samples. We indicate here how these can be selected based on ADQL queries on the qso_candidates and galaxy_candidates tables.

DSC and DSC-joint samples

The DSC sources used in the samples described in this chapter can be obtained with the following queries in the respective qso_candidates and galaxy_candidates tables:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE MOD(source_selection_flags,POWER(2,6+1))-MOD(source_selection_flags,POWER(2,6)) != 0

SELECT source_id FROM gaiadr3.galaxy_candidates
WHERE MOD(source_selection_flags,POWER(2,3+1))-MOD(source_selection_flags,POWER(2,3)) != 0

Similarly, the following provides the sources from the DSC-Joint samples of quasars and galaxies:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE classlabel_dsc_joint = 'quasar'

SELECT source_id FROM gaiadr3.galaxy_candidates
WHERE classlabel_dsc_joint = 'galaxy'

Vari-Classification samples

The respective 'AGN' and 'GALAXY' samples from the Vari-Classification module are selected as follows:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE vari_best_class_name = 'AGN'

SELECT source_id FROM gaiadr3.galaxy_candidates
WHERE vari_best_class_name = 'GALAXY'

Vari-AGN sample

The Vari-AGN sample is simply obtained by:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE qso_variability IS NOT NULL

Surface brightness samples

The sources considered eligible from the surface brightness samples are selected as:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE host_galaxy_flag < 6

SELECT source_id FROM gaiadr3.galaxy_candidates
WHERE radius_sersic IS NOT NULL

Quasar classifier (SOC) sample

The sources considered eligible from the QSOC processing (see Chapter 11.3.14 in the Gaia DR3 documentation) are selected as:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE source_selection_flags >= 128

The following query shows how to select quasar candidates having the k^th bit of the flags_qsoc set to a certain value:

SELECT *
FROM gaiadr3.qso_candidates
WHERE MOD(flags_qsoc,POWER(2,k+1)) - MOD(flags_qsoc,POWER(2,k)) <> 0

And the query below shows how to select quasar candidates having both an estimated redshift from QSOC and XP continuous spectra available via DataLink.

SELECT gdr3.source_id, gdr3.ra, gdr3.dec, gdr3.phot_g_mean_mag, gdr3.phot_bp_mean_mag, gdr3.phot_rp_mean_mag, qso.redshift_qsoc, qso.flags_qsoc
FROM gaiadr3.qso_candidates AS qso
JOIN gaiadr3.gaia_source AS gdr3 USING (source_id)
WHERE qso.redshift_qsoc IS NOT NULL
  AND gdr3.has_xp_continuous = 'TRUE'

Unresolved Galaxy Classifier (UGC) sample

The sources considered eligible from the UGC (see Chapter 11.3.13 in the Gaia DR3 documentation) processing are selected as:

SELECT source_id FROM gaiadr3.galaxy_candidates
WHERE redshift_ugc IS NOT NULL

The total number of galaxy candidates having estimated redshifts by UGC can be computed as follows:

SELECT COUNT(*)
FROM gaiadr3.galaxy_candidates
WHERE redshift_ugc IS NOT NULL

OA samples

Although not used to define which sources should enter into the integrated extragalactic tables, the OA sources used in this chapter can be obtained by simply selecting those sources where field classlabel_oa is not null.

Astrophysical parameters tables

This section contains different use cases mostly related to the astrophysical_parameters and astrophysical_parameters_supp tables. The examples are sorted following the structure of Sect 11.3 (Apsis modules) in the Gaia DR3 documentation.

Chapter 11.3.2: Discrete Source classifier (DSC)

Use case: Select high-probability quasars according to their Specmod and Allosmod probabilities, excluding circular regions centred in the Large and Small Magallanic Clouds with radii of 9 and 6 degrees, respectively.
Notes: The "<>" operator stands for "not equal to" in PostgreSQL
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.gaia_source_lite
Query:

SELECT gaia.source_id
FROM gaiadr3.astrophysical_parameters
JOIN gaiadr3.gaia_source_lite AS gaia USING (source_id)
WHERE gaia.in_qso_candidates = 'true'
  AND classprob_dsc_specmod_quasar > 0.9
  AND classprob_dsc_allosmod_quasar > 0.9
  AND 1 <> CONTAINS(POINT(81.3, -68.7), CIRCLE(gaia.ra, gaia.dec, 9.0))
  AND 1 <> CONTAINS(POINT(16.0, -72.8), CIRCLE(gaia.ra, gaia.dec, 6.0))

Chapter 11.3.3: General Stellar Parametrizer from Photometry (GSP-Phot)

Use case: Select a random sample of best measured giant stars having A₀ < 1 mag within a distance < 5 kpc.
Notes: 
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.gaia_source_lite
Query:

SELECT gaia.source_id, gaia.l, gaia.b, gaia.phot_g_mean_mag, apsis.teff_gspphot, apsis.logg_gspphot, apsis.mh_gspphot, apsis.azero_gspphot,
       apsis.distance_gspphot, apsis.distance_gspphot_lower, apsis.distance_gspphot_upper, apsis.libname_gspphot
FROM gaiadr3.astrophysical_parameters AS apsis
JOIN gaiadr3.gaia_source_lite AS gaia USING (source_id)
WHERE apsis.azero_gspphot < 1
  AND apsis.distance_gspphot < 5000
  AND apsis.logg_gspphot BETWEEN 0 AND 3
  AND gaia.random_index < 100000

Chapter 11.3.4: General Stellar Parametrizer from Spectroscopy (GSP-Spec)

Use case: Select the atmospheric parameters for a high-quality sample (first 13 GSP-Spec parameter flags equal to zero) of 10,000 stars (see flags_gspspec for details).
Notes: 
Target tables: gaiadr3.astrophysical_parameters
Query:

SELECT TOP 10000 source_id, flags_gspspec, teff_gspspec, logg_gspspec, mh_gspspec, alphafe_gspspec
FROM gaiadr3.astrophysical_parameters
WHERE flags_gspspec LIKE '0000000000000%'

Use case: Return T_eff, log g, and [Fe/H] (not [M/H]) and [Ca/Fe] (computing their uncertainties), for a sample of 10,000 stars using a selection criterion based on the Fe and Ca GSP-Spec flags equal to 0. The KMgiantPar flag is allowed to be 0 or 1 (see flags_gspspec description for details).
Notes: 
Target tables: gaiadr3.astrophysical_parameters
Query:

WITH gsp_ap AS (
   SELECT source_id, flags_gspspec, teff_gspspec, logg_gspspec, mh_gspspec + fem_gspspec AS feh_gspspec, cafe_gspspec, 
   0.5*SQRT(POWER(mh_gspspec_upper-mh_gspspec_lower, 2) + POWER(fem_gspspec_upper-fem_gspspec_lower, 2)) AS feh_gspspec_sigma,
   0.5*(cafe_gspspec_upper-cafe_gspspec_lower) AS cafe_gspspec_sigma, spectraltype_esphs
   FROM gaiadr3.astrophysical_parameters)
SELECT TOP 10000 source_id, feh_gspspec, feh_gspspec_sigma, cafe_gspspec, cafe_gspspec_sigma, teff_gspspec, spectraltype_esphs, logg_gspspec, flags_gspspec
FROM gsp_ap
WHERE (flags_gspspec LIKE '0000000000000________00____00%' OR flags_gspspec LIKE '0000000000001________00____00%')
AND feh_gspspec_sigma < 0.1
AND cafe_gspspec_sigma < 0.1

Use case: Retrieve Artificial Neural Network (ANN) parametrisation results for high-quality objects (see flags_gspspec description for details). 
Notes: As an alternative (that produces identical output and has similar execution time), the WHERE clause could be modified as: ... WHERE flags_gspspec_ann LIKE '000000000000'
Target tables: astrophysical_parameters_supp
Query:

SELECT source_id, teff_gspspec_ann, teff_gspspec_ann_lower, teff_gspspec_ann_upper, logg_gspspec_ann, logg_gspspec_ann_lower, logg_gspspec_ann_upper,
mh_gspspec_ann, mh_gspspec_ann_lower, mh_gspspec_ann_upper, alphafe_gspspec_ann, alphafe_gspspec_ann_lower, alphafe_gspspec_ann_upper,
logchisq_gspspec_ann, flags_gspspec_ann
FROM astrophysical_parameters_supp
WHERE CAST(flags_gspspec_ann AS BIGINT) = 0

Use case: Retrieve the first 1,000 high-quality objects with all necessary parameters from ANN to plot Kiel, [alpha/Fe] versus [M/H], log g versus [M/H], and T_eff versus G diagrams (see flags_gspspec description for details).
Notes: 
Target tables: gaiadr3.astrophysical_parameters_supp, gaiadr3.gaia_source_lite
Query:

SELECT TOP 1000 gs.source_id, aps.teff_gspspec_ann AS teff, aps.logg_gspspec_ann AS logg, aps.mh_gspspec_ann AS mh, aps.alphafe_gspspec_ann AS alphafe,
gs.phot_g_mean_mag + 5.0 * log10(gs.parallax) + 10.0 AS abs_gmag
FROM gaiadr3.gaia_source_lite AS gs
RIGHT JOIN gaiadr3.astrophysical_parameters_supp AS aps USING (source_id)
WHERE gs.parallax > 0 AND 
CAST(aps.flags_gspspec_ann AS BIGINT) = 0)

Chapter 11.3.5: Multiple Star Classifier (MSC)

Use case: Select distances from MSC and GSP-Phot for a random subset of sources with parallax SNR > 10.
Notes: 
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.gaia_source_lite
Query:

SELECT gaia.source_id, gaia.parallax, gaia.parallax_error, apsis.distance_gspphot, apsis.distance_msc, apsis.teff_msc1, apsis.teff_msc2, apsis.flags_msc
FROM gaiadr3.gaia_source_lite AS gaia
JOIN gaiadr3.astrophysical_parameters AS apsis USING (source_id)
WHERE gaia.random_index < 100000
  AND gaia.parallax_over_error > 10
  AND apsis.distance_gspphot IS NOT NULL
  AND apsis.distance_msc IS NOT NULL

Chapter 11.3.6: Final Luminosity Age Mass Estimator (FLAME)

Use case: To derive the distributions of FLAME parameters and uncertainties for about five thousand stars randomly chosen.
Notes: 
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.gaia_source_lite
Query:

SELECT ap.source_id, ap.age_flame, ap.age_flame_lower, ap.age_flame_upper, ap.mass_flame, ap.mass_flame_lower, ap.mass_flame_upper, ap.lum_flame,
ap.lum_flame_lower, ap.lum_flame_upper, ap.radius_flame, ap.radius_flame_lower, ap.radius_flame_upper, ap.gravredshift_flame, ap.gravredshift_flame_lower,
ap.gravredshift_flame_upper, ap.evolstage_flame, ap.flags_flame, ap.bc_flame, ap.teff_gspphot, ap.mh_gspphot, ap.logg_gspphot, ap.ag_gspphot, ap.radius_gspphot,
ap.distance_gspphot, ap.mg_gspphot, gs.parallax, gs.parallax_error, gs.phot_g_mean_mag, gs.phot_bp_mean_mag, gs.phot_rp_mean_mag, gs.l, gs.b, gs.ra, gs.dec,
gs.pmra, gs.pmdec
FROM gaiadr3.astrophysical_parameters AS ap
JOIN gaiadr3.gaia_source_lite AS gs USING(source_id)
WHERE ap.lum_flame IS NOT NULL
  AND ap.bc_flame IS NOT NULL
  AND random_index < 32500

Use case: Select a sample of sources that use the parallax instead of the distance (better sample) and get their masses (see the flags_flame description for details).
Notes: 
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.gaia_source
Query:

SELECT mass_flame, source_id
FROM gaiadr3.astrophysical_parameters
WHERE flags_flame = '20'
  OR flags_flame = '10'
  OR flags_flame = '00'

Chapter 11.3.7: Extended Stellar Parametrizer for Emission-Line Stars (ESP-ELS)

Use case: Get the H_alpha pseudo-equivalenth width information for 1,000 random candidate Be stars with H_alpha < -2 nm and class label quality better than 3 (see the classlabel_espels_flag description for details).
Notes: 
Target tables: gaiadr3.astrophysical_parameters
Query:

SELECT TOP 1000 ap.source_id, ap.ew_espels_halpha, ap.ew_espels_halpha_uncertainty, ap.ew_espels_halpha_model, ap.ew_espels_halpha_flag
FROM gaiadr3.astrophysical_parameters AS ap
WHERE ap.classlabel_espels = 'beStar'
  AND ap.ew_espels_halpha < -2
  AND CAST(ap.classlabel_espels_flag AS INTEGER) <= 2

Chapter 11.3.8: Extended Stellar Parametrizer for Hot Stars (ESP-HS)

Use case: Select the first 1,000 results from the ESP-HS module in RVS+XP mode (see the flags_esphs description for details).
Notes: 
Target tables: gaiadr3.astrophysical_parameters
Query:

SELECT TOP 1000 source_id, teff_esphs, teff_esphs_uncertainty, logg_esphs, logg_esphs_uncertainty, vsini_esphs,
vsini_esphs_uncertainty, ag_esphs, ag_esphs_uncertainty, azero_esphs, azero_esphs_uncertainty, ebpminrp_esphs, ebpminrp_esphs_uncertainty, flags_esphs
FROM gaiadr3.astrophysical_parameters
WHERE teff_esphs > 0 AND
CAST(flags_esphs AS INTEGER) < 10

Use case: Select the first 1,000 results from the ESP-HS module in XP-only mode (see the flags_esphs description for details).
Notes: 
Target tables: gaiadr3.astrophysical_parameters
Query:

SELECT TOP 1000 source_id, teff_esphs, teff_esphs_uncertainty, logg_esphs, logg_esphs_uncertainty, ag_esphs,
ag_esphs_uncertainty, azero_esphs, azero_esphs_uncertainty, ebpminrp_esphs, ebpminrp_esphs_uncertainty, flags_esphs
FROM gaiadr3.astrophysical_parameters
WHERE teff_esphs > 0 AND
CAST(flags_esphs AS INTEGER) > 10

Chapter 11.3.12: Outlier Analysis (OA)

Use case: Retrieve all the sources that belong to a certain neuron. In this example, the first neuron (located at (0,0), and identified by neuron_id = 202105281205440000) is used.
Notes: 
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.oa_neuron_information
Query:

SELECT ap.source_id
FROM gaiadr3.oa_neuron_information AS neuron
JOIN gaiadr3.astrophysical_parameters AS ap
 ON neuron.neuron_id = ap.neuron_oa_id
WHERE neuron.neuron_id = 202105281205440000

Use case: Obtain the statistical parameters that were computed for the neuron where a given source lies in.
Notes: 
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.oa_neuron_information
Query:

SELECT neuron.*
FROM gaiadr3.astrophysical_parameters AS ap
JOIN gaiadr3.oa_neuron_information AS neuron
 ON ap.neuron_oa_id = neuron.neuron_id
WHERE ap.source_id = 4296182638654046592

Use case: Retrieve the first 100 sources that belong to a SOM neuron that were assigned a specific class label. For this example, all galaxy labels are considered.
Notes: 
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.oa_neuron_information
Query:

SELECT TOP 100 ap.source_id, neuron.*
FROM gaiadr3.oa_neuron_information AS neuron
JOIN gaiadr3.astrophysical_parameters AS ap
 ON neuron.neuron_id = ap.neuron_oa_id
WHERE neuron.class_label LIKE 'GAL%'

Use case: Obtain all the neurons that achieve a certain quality category threshold. In this example, just high-quality neurons (HQN) are retrieved.
Notes: 
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.oa_neuron_information
Query:

SELECT *
FROM gaiadr3.oa_neuron_information
WHERE quality_category < 4

Use case: Identify the best-quality sources that are assigned to a certain neuron by applying a filter on the distance between the source XP spectra and the neuron prototype. In this example, the first neuron (located at (0,0), and identified by neuron_id = 202105281205440000) is used, as well as a distance filter based on the neuron_oa_dist_percentile_rank field.
Notes: Defining a filter based on the neuron_oa_dist field from the astrophysical_parameters table could provide a much finer filtering.
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.oa_neuron_information
Query:

SELECT ap.source_id
FROM gaiadr3.oa_neuron_information AS neuron
JOIN gaiadr3.astrophysical_parameters AS ap
 ON neuron.neuron_id = ap.neuron_oa_id
WHERE neuron.neuron_id = 202105281205440000
  AND ap.neuron_oa_dist_percentile_rank >= 75

Use case: Identify the first 100 best quality sources that are assigned to any neuron by applying a filter based on both the neuron quality index and the distance between the source XP spectra and the neuron prototype.
Notes: 
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.oa_neuron_information
Query:

SELECT TOP 100 ap.source_id
FROM gaiadr3.oa_neuron_information AS neuron
JOIN gaiadr3.astrophysical_parameters AS ap
 ON neuron.neuron_id = ap.neuron_oa_id
WHERE neuron.quality_category < 4
  AND ap.neuron_oa_dist < 0.001

Use case: Given a certain source, retrieve the XP spectrum of the neuron prototype that represents such a source. Note that the outputs should be ordered by the prototype wavelength in order to be able to reconstruct the XP spectrum.
Notes: 
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.oa_neuron_xp_spectra
Query:

SELECT spectra.xp_spectrum_prototype_wavelength, spectra.xp_spectrum_prototype_flux
FROM gaiadr3.astrophysical_parameters AS ap
JOIN gaiadr3.oa_neuron_xp_spectra AS spectra
 ON ap.neuron_oa_id = spectra.neuron_id
WHERE ap.source_id = 4296182638654046592
ORDER BY spectra.xp_spectrum_prototype_wavelength

Chapter 11.3.13: Unresolved Galaxy Classifier (UGC)

Use case: Count galaxy candidates having estimated redshifts by UGC.
Notes: 
Target tables: gaiadr3.galaxy_candidates
Query:

SELECT COUNT(*)
FROM gaiadr3.galaxy_candidates
WHERE redshift_ugc IS NOT NULL

Use case: Retrieve the UGC parameters for 10 galaxy candidates having estimated redshift.
Notes: 
Target tables: gaiadr3.galaxy_candidates
Query:

SELECT TOP 10 source_id, redshift_ugc, redshift_ugc_upper, redshift_ugc_lower
FROM gaiadr3.galaxy_candidates
WHERE redshift_ugc IS NOT NULL

Chapter 11.3.14: Quasar Classifier (QSOC)

Use case: Select quasar candidates having the k^th bit of the flags_qsoc set to a certain value.
Target tables: gaiadr3.qso_candidates
Query:

SELECT *
FROM gaiadr3.qso_candidates
WHERE MOD(flags_qsoc,POWER(2,k+1)) - MOD(flags_qsoc,POWER(2,k)) <> 0

Use case: Select quasar candidates having both an estimated redshift from QSOC and XP continuous spectra available via DataLink.
Notes: 
Target tables: gaiadr3.astrophysical_parameters, gaiadr3.gaia_source
Query:

SELECT gdr3.source_id, gdr3.ra, gdr3.dec, gdr3.phot_g_mean_mag, gdr3.phot_bp_mean_mag, gdr3.phot_rp_mean_mag, qso.redshift_qsoc, qso.flags_qsoc
FROM gaiadr3.qso_candidates AS qso
JOIN gaiadr3.gaia_source AS gdr3 USING (source_id)
WHERE qso.redshift_qsoc IS NOT NULL
  AND gdr3.has_xp_continuous = 'TRUE'

The celestial reference frame  (Gaia-CRF3)

Use case: I want to retrieve the astrometric data for all the 1,614,173 Gaia-CRF3 sources (Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3)).
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT agn.source_name_in_catalogue, agn.catalogue_name, edr3.*
FROM gaiaedr3.agn_cross_id AS agn
INNER JOIN gaiaedr3.gaia_source AS edr3 USING (source_id)

Gaia-CRF3: 5 parameter solutions

Use case: I want to retrieve the astrometric data for the Gaia-CRF3 sources with five-parameter astrometric solutions (Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3)).
Notes: Replacing '31' by '95' one gets the data for the 398,231 sources with six-parameter solutions.
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT agn.source_name_in_catalogue, agn.catalogue_name, edr3.*
FROM gaiaedr3.agn_cross_id AS agn
INNER JOIN gaiaedr3.gaia_source AS edr3 USING (source_id)
WHERE edr3.astrometric_params_solved = 31

gaia-crf3: ORIENTATION sources

Use case: I want to retrieve the data for the 2007 sources that were used to fix the orientation of Gaia-CRF3 (Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3)).
Notes: The complexity of the query results from the fact that gaiaedr3.frame_rotator_source can contain sources that are NOT in gaiaedr3.agn_cross_id.
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT sub.source_name_in_catalogue, sub.catalogue_name, edr3.*
FROM (
SELECT frame.source_id, agn.source_name_in_catalogue, agn.catalogue_name
FROM gaiaedr3.frame_rotator_source AS frame
LEFT OUTER JOIN gaiaedr3.agn_cross_id AS agn USING (source_id)
WHERE frame.used_for_reference_frame_orientation = 'True'
) AS sub
INNER JOIN gaiaedr3.gaia_source AS edr3 USING (source_id)

gaia-crf3: SPIN sources

Use case: I want to retrieve the data for the 428,034 sources used to define the rotation (spin) of Gaia-CRF3 (Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3)).
Notes: The complexity of the query results from the fact that gaiaedr3.frame_rotator_source can contain sources that are NOT in gaiaedr3.agn_cross_id.
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT sub.source_name_in_catalogue, sub.catalogue_name, edr3.*
FROM (
SELECT frame.source_id, agn.source_name_in_catalogue, agn.catalogue_name
FROM gaiaedr3.frame_rotator_source AS frame
LEFT OUTER JOIN gaiaedr3.agn_cross_id AS agn USING (source_id)
WHERE frame.used_for_reference_frame_spin = 'True'
) AS sub
INNER JOIN gaiaedr3.gaia_source AS edr3 USING (source_id)

gaia crf3: catalogue selection

Use case: I want to retrieve the Gaia-CRF3 sources from a given external catalog: "$$$" should be replaced by the catalogue code given in Table 1 in Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3).
Notes: This query involves EDR3 and DR3 tables.
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT xm.$$$_name, edr3.*
FROM gaiadr3.gaia_source AS edr3
INNER JOIN gaiadr3.gaia_crf3_xm AS xm USING (source_id)
WHERE xm.$$$ = 'True'

gaia crf3: confusion sources

Use case: I want to retrieve the data for the 30,723,995 confusion sources in GEDR3 with five-parameter solutions (adapted from Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3)).
Notes: Replacing '31' by '95' gives the 182,814,959 confusion sources with six-parameter solutions. This query is somewhat dangerous since taken verbatim it returns huge tables.
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT * FROM gaiaedr3.gaia_source AS g
WHERE g.astrometric_params_solved = 31
  AND ABS((g.parallax + 0.017)/g.parallax_error) < 5
  AND ( POWER(g.pmra/g.pmra_error,2) + POWER(g.pmdec/g.pmdec_error,2) -2*g.pmra_pmdec_corr*g.pmra/g.pmra_error*g.pmdec/g.pmdec_error)/
      (1-POWER(g.pmra_pmdec_corr,2)) < 25

MISCELLANEOUS

Synthetic photometry from Gaia XP spectra - PVP

Use case: I want to retrieve SDSS synthetic photometery for the sources that have an absolute corrected BP/RP flux excess factor smaller than the 1-sigma relation suggested in Gaia Early Data Release 3: Photometric content and validation.
Notes: This query runs on a 0.001 random selection of the Gaia source catalogue thanks to the additional constraint on random_index.
Target tables: gaiadr3.gaia_source, gaiadr3.synthetic_photometry_gspc
Query: 

SELECT dr3.source_id, dr3.ra, dr3.dec, dr3.parallax, dr3.parallax_over_error, dr3.ruwe, gspc.g_sdss_mag, gspc.i_sdss_mag
FROM gaiadr3.gaia_source AS dr3
INNER JOIN gaiadr3.synthetic_photometry_gspc AS gspc USING (source_id)
WHERE
ABS(gspc.c_star)<(0.0059898 + 8.817481e-12 * POWER(dr3.phot_g_mean_mag, 7.618399)) AND dr3.random_index < 1811709

Synthetic photometry from Gaia XP spectra - PVP (II)

Use case: I want to retrieve some selected parameters from the main Gaia DR3 table and all the parameters available in the GSPC for a given set of stars, i.e. those having |b|> 50 deg (Galactic Caps). Selections in RUWE and in c_star (following the criterion suggested by Gaia Early Data Release 3: Photometric content and validation) are also adopted.
Target tables: gaiadr3.gaia_source_lite, gaiadr3.synthetic_photometry_gspc
Notes: Execution time ~25 mins. Job output size = 1.3 GB.
Query:

SELECT dr3.source_id, dr3.ra, dr3.dec, dr3.parallax, dr3.parallax_error, dr3.pmra, dr3.pmra_error, dr3.pmdec, dr3.pmdec_error, dr3.phot_g_mean_mag, 
dr3.phot_bp_mean_mag, dr3.phot_rp_mean_mag, dr3.bp_rp, dr3.radial_velocity, dr3.radial_velocity_error, dr3.ruwe, dr3.phot_variable_flag, 
dr3.non_single_star, dr3.teff_gspphot, dr3.logg_gspphot, dr3.mh_gspphot, dr3.distance_gspphot, dr3.ag_gspphot,
gspc.c_star, gspc.u_sdss_mag, gspc.g_sdss_mag,gspc.r_sdss_mag, gspc.i_sdss_mag, gspc.z_sdss_mag, 
gspc.u_jkc_mag, gspc.b_jkc_mag,gspc.v_jkc_mag, gspc.r_jkc_mag, gspc.i_jkc_mag, gspc.y_ps1_mag, f606w_acswfc_mag, f814w_acswfc_mag,
1.086*(gspc.u_sdss_flux_error/gspc.u_sdss_flux) AS du_sdss, 1.086*(gspc.g_sdss_flux_error/gspc.g_sdss_flux) AS dg_sdss, 
1.086*(gspc.r_sdss_flux_error/gspc.r_sdss_flux) AS dr_sdss, 1.086*(gspc.i_sdss_flux_error/gspc.i_sdss_flux) AS di_sdss, 
1.086*(gspc.z_sdss_flux_error/gspc.z_sdss_flux) AS dz_sdss, 1.086*(gspc.u_jkc_flux_error/gspc.u_jkc_flux)   AS du_jkc, 
1.086*(gspc.b_jkc_flux_error/gspc.b_jkc_flux) AS db_jkc,    1.086*(gspc.v_jkc_flux_error/gspc.v_jkc_flux)   AS dv_jkc, 
1.086*(gspc.r_jkc_flux_error/gspc.r_jkc_flux) AS dr_jkc,    1.086*(gspc.i_jkc_flux_error/gspc.i_jkc_flux)   AS di_jkc, 
1.086*(gspc.y_ps1_flux_error/gspc.y_ps1_flux) AS dy_ps1,    1.086*(gspc.f606w_acswfc_flux_error/gspc.f606w_acswfc_flux) AS df606w_acswfc, 
1.086*(gspc.f814w_acswfc_flux_error/gspc.f814w_acswfc_flux) AS df814w_acswfc,
gspc.u_sdss_flag, gspc.g_sdss_flag, gspc.r_sdss_flag, gspc.i_sdss_flag, gspc.z_sdss_flag,
gspc.u_jkc_flag, gspc.b_jkc_flag, gspc.v_jkc_flag, gspc.r_jkc_flag, gspc.i_jkc_flag,
gspc.y_ps1_flag, gspc.f606w_acswfc_flag, gspc.f606w_acswfc_flag
FROM gaiadr3.gaia_source_lite AS dr3
INNER JOIN gaiadr3.synthetic_photometry_gspc AS gspc USING (source_id)
WHERE ABS(dr3.b)>50.0
AND dr3.ruwe<1.3
AND ABS(gspc.c_star)<(0.0059898 + 8.817481e-12 * POWER(dr3.phot_g_mean_mag,7.618399))

Extragalactic objects in Gaia DR3 (QSOS PURER SUBSET)

Use case: Selects the purer subset of quasars recommended by Gaia Data Release 3: The extragalactic content.
Target table: gaiadr3.qso_candidates
Query: 

SELECT *
FROM gaiadr3.qso_candidates
WHERE (gaia_crf_source = 'True' OR host_galaxy_flag<6 OR classlabel_dsc_joint = 'quasar' OR vari_best_class_name = 'AGN')

Extragalactic objects in Gaia DR3 (GALAXIES PURER SUBSET)

Use case: Selects the purer subset of galaxies recommended by Gaia Data Release 3: The extragalactic content.
Target table: gaiadr3.galaxy_candidates
Query: 

SELECT *
FROM gaiadr3.galaxy_candidates
WHERE (radius_sersic IS NOT NULL OR classlabel_dsc_joint = 'galaxy' OR vari_best_class_name = 'GALAXY')

Extragalactic objects in Gaia DR3

Use case: Shows how to combine three tables to select sources with higher classification probabilities that exclude the LMC and SMC (extracted from Gaia Data Release 3: The extragalactic content).
Target tables: gaiadr3.qso_candidates, gaiadr3.astrophysical_parameters
Query: 

SELECT source_id
FROM (SELECT *
	  FROM gaiadr3.qso_candidates
	  JOIN gaiadr3.astrophysical_parameters USING (source_id)
      WHERE classprob_dsc_specmod_quasar>0.9 AND classprob_dsc_allosmod_quasar>0.9
     ) AS temp
JOIN gaiadr3.gaia_source USING (source_id)
WHERE 1 != CONTAINS(
  POINT(81.3, -68.7), CIRCLE(ra, dec, 9.0)) AND
  1 != CONTAINS(
  POINT(16.0, -72.8), CIRCLE(ra, dec, 6.0))

Golden sample of Astrophysical Parameters

Use case: The query used iteratively for the analysis and selection of the FGKM sample of stars. The iteration runs over random_index in steps of 10,000,000 sources (Gaia Data Release 3: A golden sample of astrophysical parameters).
Target tables: gaiadr3.gaia_source, gaiadr3.astrophysical_parameters, gaiadr3.astrophysical_parameters_supp.
Notes: This ADQL query retrieves the "ipd_gof_harmonic_amplitude", whose use has been discouraged for Gaia (E)DR3 and the FPR - see this Known issue for details.
Query:

SELECT gs.source_id, phot_bp_mean_mag, phot_rp_mean_mag, phot_bp_mean_flux_over_error, phot_rp_mean_flux_over_error, 
phot_g_mean_mag, parallax_error, parallax, parallax_over_error, astrometric_excess_noise, astrometric_excess_noise_sig, 
ruwe, ipd_gof_harmonic_amplitude, rvs_spec_sig_to_noise, rv_expected_sig_to_noise,
ap.ebpminrp_gspphot, ap.radius_gspphot, ap.teff_gspphot, ap.logg_gspphot, ap.mg_gspphot,
ap.abp_gspphot, gs.ag_gspphot, ap.arp_gspphot, ap.teff_gspphot_upper, gs.mh_gspphot,
ap.teff_gspphot_lower, mg_gspphot_lower, mg_gspphot_upper, ap.ebpminrp_gspphot_lower,ap.ebpminrp_gspphot_upper, 
ap.ag_gspphot_upper, ap.ag_gspphot_lower, ap.mh_gspphot_upper, ap.mh_gspphot_lower, ap.logg_gspphot_upper, ap.logg_gspphot_lower, 
teff_gspphot_marcs, teff_gspphot_phoenix, logg_gspphot_marcs, logg_gspphot_phoenix, 
radius_flame, mass_flame, age_flame, evolstage_flame, lum_flame, bc_flame,
radius_flame_upper, radius_flame_lower, lum_flame_upper, lum_flame_lower, 
mass_flame_upper, mass_flame_lower, age_flame_upper, age_flame_lower, flags_flame,
spectraltype_esphs, flags_esphs, 
teff_gspspec, mh_gspspec, logg_gspspec, alphafe_gspspec, 
teff_gspspec_upper, teff_gspspec_lower, logg_gspspec_upper, logg_gspspec_lower, mh_gspspec_upper, mh_gspspec_lower, 
radius_flame_spec, lum_flame_spec, age_flame_spec, mass_flame_spec, evolstage_flame_spec, 
radius_flame_spec_upper, radius_flame_spec_lower, lum_flame_spec_lower, lum_flame_spec_upper, mass_flame_spec_upper, 
mass_flame_spec_lower, age_flame_spec_lower, age_flame_spec_upper, bc_flame_spec
FROM gaiadr3.gaia_source AS gs 
INNER JOIN gaiadr3.astrophysical_parameters_supp AS aps USING (source_id)
INNER JOIN gaiadr3.astrophysical_parameters AS ap USING (source_id)
WHERE ipd_frac_multi_peak < 6 AND parallax_over_error >10 AND gs.teff_gspphot > 2500 AND gs.teff_gspphot < 7500 AND 
gs.distance_gspphot < 1e3/(parallax-4*parallax_error) AND gs.distance_gspphot > 1e3/(parallax +4*parallax_error) AND
gs.libname_gspphot='MARCS' AND gs.classprob_dsc_combmod_star > 0.9 AND gs.mh_gspphot > -0.8 AND
ABS(teff_gspphot_marcs - teff_gspphot_phoenix + 65) < 150 AND radius_gspphot < 100 and mg_gspphot < 12 
AND random_index >= 1600000000 AND random_index < 1610000000

RETRIEVE SAMPLE OF OB STARS IN THE MILKY WAY SPIRAL ARMS

Use case: I want to retrieve the sample of OB stars used in the Mapping the spiral arms of the Milky Way (Gaia Data Release 3: Mapping the asymmetric disc of the Milky Way).
Target table: gaiadr3.gaia_source, gaiadr3.astrophysical_parameters
Query:

SELECT g.*, ap.*
FROM gaiadr3.gaia_source AS g
INNER JOIN gaiadr3.astrophysical_parameters AS ap
ON g.source_id = ap.source_id
WHERE (
(ap.teff_gspphot > 10000 AND (ap.spectraltype_esphs = 'O' OR ap.spectraltype_esphs = 'B' OR ap.spectraltype_esphs = 'A') AND ap.teff_esphs IS NULL) OR
(ap.teff_esphs > 10000 AND ap.teff_gspphot > 8000) OR (ap.teff_esphs > 10000 AND ap.teff_esphs < 50000 AND ap.teff_gspphot IS NULL))
AND power(g.parallax/100.,5) < power(10.,2.-g.phot_g_mean_mag+1.8*g.bp_rp)

RETRIEVE SAMPLE OF RGB STARS IN THE MILKY WAY SPIRAL ARMS

Use case: I want to retrieve the sample of RGB stars used in the Mapping the spiral arms of the Milky Way (Gaia Data Release 3: Mapping the asymmetric disc of the Milky Way).
Target table: gaiadr3.gaia_source
Query:

SELECT g.*
FROM gaiadr3.gaia_source AS g
WHERE (g.teff_gspphot<5500 AND g.teff_gspphot>3000) AND (g.logg_gspphot<3.)

Chemical cartography of the Milky Way disc

Use case: I want to retrieve the "medium quality sample" of stars with GSPspec parameters described in (Gaia Data Release 3: Chemical cartography of the Milky Way - see their Sect. 2.5)
Target table: gaiadr3.astrophysical_parameters
Query:

SELECT source_id
FROM gaiadr3.astrophysical_parameters
WHERE (teff_gspspec>3500) AND (logg_gspspec>0) AND (logg_gspspec<5) AND ((teff_gspspec_upper-teff_gspspec_lower)<750) AND ((logg_gspspec_upper-logg_gspspec_lower)<1.) AND
((mh_gspspec_upper-mh_gspspec_lower)<.5) AND (teff_gspspec>=3800 OR logg_gspspec<=3.5) AND (teff_gspspec>=4150 OR logg_gspspec<=2.4 OR logg_gspspec>=3.6 ) AND
((flags_gspspec LIKE '____________0%') OR (flags_gspspec LIKE '____________1%')) AND ((flags_gspspec LIKE '0%') OR (flags_gspspec LIKE '1%')) AND ((flags_gspspec LIKE '_0%') OR
(flags_gspspec LIKE '_1%')) AND ((flags_gspspec LIKE '__0%') OR (flags_gspspec LIKE '__1%')) AND ((flags_gspspec LIKE '___0%') OR (flags_gspspec LIKE '___1%')) AND
((flags_gspspec LIKE '____0%') OR (flags_gspspec LIKE '____1%')) AND((flags_gspspec LIKE '_____0%') OR (flags_gspspec LIKE '_____1%')) AND ((flags_gspspec LIKE '______0%') OR
(flags_gspspec LIKE '______1%') OR (flags_gspspec LIKE '______2%') OR (flags_gspspec LIKE '______3%')) AND ((flags_gspspec LIKE '_______0%') OR (flags_gspspec LIKE '_______1%') OR (flags_gspspec LIKE '_______2%'))

Reflectance spectra of Main Belt small bodies

Use case: I want to retrieve the wavelengths, reflectances, and reflectance errors for the asteroid numbered 21 by the Minor Planet Center (Reflectance spectra of Main Belt small bodies, Gaia Data Release 3: Reflectance spectra of solar system small bodies).
Target table: gaiadr3.gaia_source, gaiadr3.astrophysical_parameters
Query:

SELECT wavelength, reflectance_spectrum, reflectance_spectrum_err
FROM gaidr3.sso_reflectance_spectrum
WHERE number_mp = 21

Radial-velocity variable stars

Use case: I want to identify radial-velocity variable stars applying the criteria described in Gaia Data Release 3: Properties and validation of the radial velocities.
Target table: gaiadr3.gaia_source
Query:

SELECT * FROM gaiadr3.gaia_source
WHERE
rv_nb_transits >= 10 AND
rv_template_teff BETWEEN 3900 AND 8000 AND
rv_chisq_pvalue <= 0.01 AND
rv_renormalised_gof > 4

Stellar multiplicity, a teaser for the hidden treasure

Use case: I want to compare orbital periods from the "gaiadr3.nss_two_body_orbit" table against photometric periods from the "gaiadr3.vari_long_period_variable" table. Adapted from Gaia Data Release 3: Stellar multiplicity, a teaser for the hidden treasure.
Target tables: gaiadr3.nss_two_body_orbit, gaiadr3.vari_long_period_variable
Query:

SELECT *
FROM gaiadr3.nss_two_body_orbit AS TBO, gaiadr3.vari_long_period_variable AS LPV
WHERE LPV.source_id = TBO.source_id AND LPV.frequency IS NOT NULL

Gaia DR2 sElected queries

Light Curves / epoch photometry

Use case: I want to retrieve the light curves for two variable stars (DR2, Gaia Collaboration, Holl et al. 2018 A&A 618, A30; adapted Fig. A1).
Target table: gaiadr2.gaia_source, gaiadr2.vari_rrlyrae, gaiadr2.vari_long_period_variable, gaiadr2.vari_classifier_result
Query:

SELECT gaia.source_id, phot_variable_flag, best_class_name, datalink_url, sosrrl.pf, sosrrl.epoch_g, soslpv.frequency
FROM gaiadr2.gaia_source AS gaia
LEFT OUTER JOIN gaiadr2.vari_classifier_result AS variables USING (source_id)
LEFT OUTER JOIN gaiadr2.vari_rrlyrae AS sosrrl USING (source_id)
LEFT OUTER JOIN gaiadr2.vari_long_period_variable AS soslpv USING (source_id)
WHERE gaia.source_id IN (1372970619261887104, 5368939678153068288)

Density distribution of radial velocities

Use case:  I want to show the average distribution of Gaia DR2 radial velocities in Galactic Coordinates (DR2, Gaia Collaboration, Katz et al. 2019 A&A 622, A205; adapted Fig. 7).
Target table: gaiadr2.gaia_source
Query:

SELECT GAIA_HEALPIX_INDEX(6, source_id) AS healpix6, AVG(radial_velocity) AS avg_radial_velocity
FROM gaiadr2.gaia_source
WHERE radial_velocity IS NOT NULL
GROUP BY healpix6

Observed CMD diagram

Use case:   I want to construct an observed CMD diagram (DR2, Gaia Collaboration, Andrae et al. 2018 A&A 616, A8; adapted Fig. 19).
Target table: gaiadr2.gaia_source
Query:

SELECT bp_rp_index / 40 AS bp_rp, g_abs_index / 10 AS g_abs, n
FROM (
   SELECT FLOOR(bp_rp * 40) AS bp_rp_index,FLOOR((phot_g_mean_mag + 5 * LOG10(parallax) - 10) * 10) AS g_abs_index, COUNT(*) AS n
   FROM gaiadr2.gaia_source
   WHERE parallax_over_error > 5
   AND a_g_val IS NOT NULL
   AND random_index < 800000
   GROUP BY bp_rp_index, g_abs_index
   ) AS subquery

Average Extinction distribution

Use case:  I want to obtain the parallax-averaged A_G Extinction (DR2, Gaia Collaboration, Andrae et al. 2018 A&A 616, A8; adapted Fig. 21).
Target table: gaiadr2.gaia_source
Query:

SELECT GAIA_HEALPIX_INDEX(8, source_id) AS healpix8, AVG(a_g_val) AS avg_a_g_val
FROM gaiadr2.gaia_source
WHERE a_g_val IS NOT NULL
AND random_index < 800000
GROUP BY healpix8

Extinction towards Orion

Use case:  I want to obtain the extinction towards Orion for a distance slice between 0.6 AND 1.0 kpc (DR2, Gaia Collaboration, Andrae et al. 2018 A&A 616, A8; adapted Fig. 34).
Target table: gaiadr2.gaia_source
Query:

SELECT l_index / 2 AS l, b_index / 4 AS b, extinction, n
FROM (
  SELECT FLOOR((l - 360) * 2) AS l_index, FLOOR(b * 4) AS b_index, AVG(a_g_val) AS extinction, COUNT(*) AS n
  FROM gaiadr2.gaia_source
  WHERE l BETWEEN 180 AND 230
    AND b BETWEEN -25 AND 0
    AND parallax_over_error > 5
    AND a_g_val IS NOT NULL
  AND parallax BETWEEN 1. AND 1. / 0.6
  AND random_index < 800000
  GROUP BY l_index, b_index
) AS subquery

RR Lyrae sky density distribution.

Use case:  I want to retrieve the sky density distribution of RR Lyrae stars (DR2, Gaia Collaboration, Holl et al. 2018 A&A 618, A30; adapted Fig. 6).
Target table: gaiadr2.gaia_source
Query:

SELECT GAIA_HEALPIX_INDEX(6, source_id) AS healpix6, COUNT (*)/ 0.9161 AS stars_per_sq_deg
FROM gaiadr2.vari_classifier_result
WHERE best_class_name='RRAB'
  OR best_class_name='RRC'
  OR best_class_name='RRD'
  OR best_class_name='ARRD'
GROUP BY healpix6

Gaia DR1 sElected queries

Gaia DR1-2MASS HR diagram

Use case:  I want to construct the Gaia DR1-TMASS HR diagram (DR1, Gaia Collaboration, Brown Holl et al. 2016 A&A 595, A2; adapted Fig. 5).
Target table: gaiadr1.gaia_source, gaiadr1.tmass_best_neighbour, gaiadr1.tmass_original_valid
Query:

SELECT TOP 100 gaia.source_id, gaia.phot_g_mean_mag + 5 * LOG10(gaia.parallax) - 10 AS g_mag_abs, gaia.phot_g_mean_mag - tmass.ks_m AS g_min_ks
FROM gaiadr1.gaia_source AS gaia
INNER JOIN gaiadr1.tmass_best_neighbour AS xmatch USING (source_id)
INNER JOIN gaiadr1.tmass_original_valid AS tmass  USING (tmass_oid)
WHERE gaia.parallax/gaia.parallax_error >= 5
AND ph_qual = 'AAA'
AND SQRT(POWER(2.5 / LOG(10) * gaia.phot_g_mean_flux_error / gaia.phot_g_mean_flux, 2) ) <= 0.05
AND SQRT(POWER(2.5 / LOG(10) * gaia.phot_g_mean_flux_error  gaia.phot_g_mean_flux, 2) + POWER(tmass.ks_msigcom, 2)) <= 0.05

Gaia DR1-2MASS HR diagram

Use case:   I want to construct the Gaia DR2 HR diagram. (DR1, Gaia Collaboration, Brown Holl et al. 2016 A&A 595, A2; adapted Fig. 5).
Target table: gaiadr1.gaia_source
Query:

SELECT g_min_ks_index / 10 AS g_min_ks, g_mag_abs_index / 10 AS g_mag_abs, COUNT(*) AS n
FROM (
  SELECT TOP 100 gaia.source_id, FLOOR((gaia.phot_g_mean_mag+5*LOG10(gaia.parallax)-10) * 10) AS g_mag_abs_index,
  FLOOR((gaia.phot_g_mean_mag-tmass.ks_m) * 10) AS g_min_ks_index
  FROM gaiadr2.gaia_source AS gaia
  INNER JOIN gaiadr2.tmass_best_neighbour AS xmatch USING (source_id)
  INNER JOIN gaiadr1.tmass_original_valid AS tmass  USING (tmass_oid)
  WHERE gaia.parallax/gaia.parallax_error >= 5
  AND ph_qual = 'AAA'
  AND SQRT(POWER(2.5 / LOG(10) * gaia.phot_g_mean_flux_error / gaia.phot_g_mean_flux, 2)) <= 0.05
  AND SQRT(POWER(2.5 / LOG(10) * gaia.phot_g_mean_flux_error / gaia.phot_g_mean_flux, 2) + POWER(tmass.ks_msigcom, 2)) <= 0.05
  ) AS subquery
GROUP BY g_min_ks_index, g_mag_abs_index

Cepheids: light curve retrieval for all stars

Use case: I want to retrieve complete epoch photometry of a given object class for offline analysis (DR1, Gaia Collaboration, Clementini et al. 2016 A&A 595, A133).
Notes: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial).
Target table: gaiadr1.cepheid, gaiadr1.phot_variable_time_series_gfov
Query:

SELECT gaia.*
FROM gaiadr1.phot_variable_time_series_gfov AS gaia
INNER JOIN gaiadr1.cepheid AS cep USING (source_id)

RR Lyrae: phase-folded light curve reconstruction, including errors

Use case: I want to construct a phase-folded light curve for comparison to stars with different periods (DR1, Gaia Collaboration, Brown et al. 2016 A&A 595, A2; adapted Fig. 7).
Notes: After Gaia DR1, light curves are being provided via the DataLink interface (see dedicated Tutorial).
Target table: gaiadr1.rrlyrae, gaiadr1.phot_variable_time_series_gfov
Query:

SELECT curves.observation_time, MOD(curves.observation_time - rrlyrae.epoch_g, rrlyrae.p1)/ rrlyrae.p1 AS phase,
curves.g_magnitude, 2.5 / log(10) * curves.g_flux_error / curves.g_flux AS g_magnitude_error
FROM gaiadr1.phot_variable_time_series_gfov AS curves
INNER JOIN gaiadr1.rrlyrae AS rrlyrae USING (source_id)
WHERE rrlyrae.source_id = 5284240582308398080

RR Lyrae: number of data points and estimated parameters

Use case: I want high-level information for a given class of variable objects (DR1, Gaia Collaboration, Clementini et al. 2016 A&A 595, A133).
Notes: After Gaia DR1, light curves are being provided via the DataLink interface (see dedicated Tutorial).
Target table: gaiadr1.rrlyrae, gaiadr1.phot_variable_time_series_gfov_statistical_parameters
Query:

SELECT stat.num_observations_processed, rr.*
FROM gaiadr1.phot_variable_time_series_gfov_statistical_parameters AS stat
INNER JOIN gaiadr1.rrlyrae AS rr USING (source_id)

Back to Gaia Help