for processing Y3 at the Science Portal, we are partitioning the Y3 catalog using healpix at a resolution of NSIDE=32 (that seems appropriate for our cluster at LIneA it gives us ~6 tiles per file and a total of 1492 files)
We use the DEGRADE function to get the desired resolution, however the query takes >1h to create a single file (see a complete output below)
What can be done to speed up this query? this is running from our server (desportal) at NCSA.
DESDB ~> select * from DES_ADMIN.Y3A1_COADD_OBJECT_SUMMARY where mcarras2.degrade(hpix, 64) = 34832; > 34832b.fits
Fetching data and saving it to 34832b.fits ...
| ☆ | Press Ctrl-C to abort Rows : 61525, Avg time (rows/sec): 14.5
Written 61525 rows to 34832b.fits in 4231.83 seconds and 7 trips
That's an issue that was discussed with Chris some time ago, with no solution yet. The problem is that is not possible to create indexes on these functions (strictly speaking it is possible but not efficient) so when using a condition it needs to do a full scan.
If you are set on a fixed nside, I'd recommend to create a small companion table with ID, HPIX and HPIX_32 for all objects (create an index on HPIX_32) one time then use a join between them using ID , that would be much faster as you'd be exploiting indexing
Powered by a free Atlassian Confluence Open Source Project License granted to NCSA OpenSource. Evaluate Confluence today.