job query does not finish

Question

Hello. I have submitted asynchronously the following query:

select coadd_object_id, ra, dec, mag_auto_g, mag_auto_r,
mag_auto_i, mag_auto_z from des_dr2.main where mag_auto_i < 20.5 and mag_auto_i > 20 and
flags_i < 4 and extended_class_coadd > 2.5

This should return a 7M object catalog (around 400 MB in fits format).

However, after one full day it never finishes, and is still executing. The job id is:

f3y5o5gzpljo2n3r

Is this expected? Thanks!

Answer 1

Async queries will timeout in the database after 24 hours, this appears to be what happened to that job since there is no record in the TAP or Data Lab logs about its completion. 

Note that your query uses constraints on two columns ('flags_i' and 'extended_class_coadd') which are not indexed.  By re-writing the query I was able to get a result in ~15 seconds, e.g.

select count(*) from  
    (select * from des_dr2.main  
     where mag_auto_i < 20.5 and mag_auto_i > 20) ss  
  where flags_i < 4 and extended_class_coadd > 2.5;

Note also that the use of sub-selects in queries like this is not supported for ASync queries so remember to submit as a normal Sync query.  Hope this helps.