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”

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
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

7

@fritzm Is there a reason to not have the DiaSource table isn’t indexed on band, visit, and detector ? I would imagine those are frequently column columns to filter on?

8

@mschwamb Not that I am aware of – we’ll add them to the list of indices to generate and deploy during the RSP patch period this afternoon.

9

Thanks, @fritzm . Would it be possible to add those to the Source table as well? One other to consider for an index is detector (for both DiaSource and Source tables). I can imagine wanting to pull all the detections from a detector during a visit to see if the same behavior is happening if there’s something odd happening.

10

Table Source doesn’t have the column designation

11

Sorry, @gapon . I should have been more specific. I was trying to ask could indexes on band, visit, and detector be added for the Source catalog? I imagine many of us are filtering the table on those columns.

12

@mschwamb, another way to reduce your query execution time is to perform the sort locally in your Notebook. When you include ORDER BY in your TAP query, you force the backend database to gather all the results and sort them before it can send the data back to you. If possible, I recommend removing the ORDER BY clause from your ADQL query to get the data back as quickly as possible, and then sort it in Python:

results = job.fetch_result().to_table()
results.sort('midpointMjdTai')
13

My apologies - I misinterpreted your previous request! It does indeed make sense to add indexes on these three columns. I will discuss this with the rest of the team.

14

Thanks, @gapon. Much appreciated.

15

Hi @mschwamb, the indexes on 3 columns of the table Source have been created.

16

Thanks very much.