Slow Solar System query

Support Data Products
,
#1

Maybe my sense of how fast queries should go is way off, but I’m surprised that this query is taking nearly 40s to return back. Is there a better way to write the following to get the query to return back faster?

“select dia.midpointMjdTai,scisql_nanojanskyToAbMag(dia.psfFlux) AS mag, scisql_nanojanskyToAbMagSigma(dia.psfFlux, dia.psfFluxErr) as rmsmag from dp2.SSSource as sss INNER JOIN dp2.DiaSource as dia on dia.diaSourceId = sss.diaSourceId where sss.designation=‘2005 RN43’ and dia.pixelFlags_bad = ‘False’ and dia.isDipole = 0 and dia.psfFlux_flag = 0 and dia.pixelFlags_bad = 0 and dia.band=‘r’ order by dia.midpointMjdTai”

1 Like
#2

Hi Meg, I’ve tried your query and can confirm that it is taking ~40s for me as well. It all looks pretty sensible to me, I can’t think of any obvious ways to make it run faster. In my experience queries have been running slower on DP2 compared to similar queries in DP1 and I presume it’s because the DP2 tables are so much bigger! But we can take a look and see if there are any ways to make the query more efficient.

I had a little trouble with characters in your query so have copied it out below as a code block in case anyone else wants to try:

SELECT 
    dia.midpointMjdTai, 
    scisql_nanojanskyToAbMag(dia.psfFlux) AS mag, 
    scisql_nanojanskyToAbMagSigma(dia.psfFlux, dia.psfFluxErr) as rmsmag
FROM
    dp2.SSSource as sss
INNER JOIN
    dp2.DiaSource as dia ON dia.diaSourceId = sss.diaSourceId
WHERE
    sss.designation='2005 RN43'
    AND dia.pixelFlags_bad ='False'
    AND dia.isDipole = 0
    AND dia.psfFlux_flag = 0
    AND dia.pixelFlags_bad = 0
    AND dia.band='r' 
ORDER BY 
    dia.midpointMjdTai
1 Like
#3

Yeah, I worry this will be a slow crawl in DR1. Doing some digging I see that now indexes are in the schema website or I just never noticed because by default they’re hidden. Looking at indexes in the DP2 schema. There’s no index on SSSource.designation. That’s likely slowing this query down significantly. Querying on designation is going to be frequent so that I think indexes are needed on that values forPPDB and any future data releases. I’m not sure if that could be added to DP2 to speed up queries.

I would also think that should be there for SSObject as well. There’s no index on designation there as well.

Note DiaSource also doesn’t have an index for ssObjectId. I would have thought it would.

#4

Update - some indexes are being added (considered for addition) or added. Thanks to @gpdf

#5

Hi folks – the database team is investigating this query as well.

We noticed the missing SSSource.designation index also; on a test instance, adding this provides approximately 4x speedup. We’ll be rolling this index out to production shortly.

We unfortunately can’t direct this query to a single spatial shard, since it aggregates SSSources that collectively may be placed in more than one. So it gets executed as a full scan over all shards. There is still substantial parallelism, but it unfortunately won’t be as quick to respond as a tightly directable query.

#6

Thanks. I appreciate it @fritzm