performance of des dr1 tap queries

Question

Hello,

I am trying to retrieve values from DES DR1 for which I already know the object's unique identifier.   The following query takes a few minutes for a single object:

select mag_auto_g from des_dr1.mag where coadd_object_id = 171179340

Can you please suggest a way to make this query more performant or consider indexing the coadd_object_id column?

Presumably a cone search would be much faster? But this seems kind of silly given that I already have the desired ID.

Thank you!

Comments

Thank you Robert and Adam for the quick replies.  Both suggestions seem to solve my issue equally well.  I noticed that these columns do not appear as indexed in TOPCAT's 'Columns' display, nor on this page: https://datalab.noao.edu/query.php?name=des_dr1.main.  Does it just take time for these changes to propagate?

Answer 1

Thank you for reaching out. The reason for the slowness is that the column(s) in question are not indexed in the des_dr1.mag table. For the example you provided, you can get the same information from the main table much faster:

%%time
print(qc.query('select mag_auto_g from des_dr1.main where coadd_object_id = 171179340'))

mag_auto_g
18.4541

CPU times: user 26 ms, sys: 0 ns, total: 26 ms
Wall time: 114 ms
Let us know if this will do. We can also compute indices on the mag table, in case that only that table has columns that you need.

Best regards,

Robert for the DL team

Answer 2

Thank you for contacting us.

An index is now created on des_dr1.mag(coadd_object_id).

Let us know if you find any other performance issues.

Cheers!

Adam

Comments

Hi,
Im trying to get des_dr1.mags with des_allwise matches.
Im seeing very slow performance. when i try and match several hundred designations or more.
Could it be an issue with not having an index on des_allwise.coadd_object_id and des_allwise.designation?
Here is an example query.

SELECT daw.designation,daw.coadd_object_id,dm.mag_auto_g,dm.mag_auto_r,dm.mag_auto_i,dm.mag_auto_z,dm.mag_auto_y
FROM des_dr1.des_allwise daw, des_dr1.mag dm
WHERE daw.coadd_object_id = dm.coadd_object_id
AND daw.designation IN ('J210017.92-604447.9', 'J204132.73-583158.4', 'J203048.48-611600.2')

Regards
Dave

---

Sorry you are experiencing slow performance.

In your example query, the database is using an index on designation and on mag_coadd_objectid, executing in less than 1 second.
Running it using several hundred values in the IN clause, it's still running in < 1 second.  
If it's still running slow for you, can you share your notebook to see if we can replicate the issue?