On the UK-DAC I’ve noticed that select count(*) from dp02_dc2_catalogs.Object queries are slow via our portal but fast via our notebook. On the US RSP they are fast both ways.
We have statistics on our qserv tables so I’m assuming that the difference is that the portal adds a limit clause. So there is either something different in our qserv or RSP that is not parsing/removing this.
We’re a few releases behind on qserv but our RSP is up to date so leaning towards the difference being in qserv but wondered if anyone knows?
I had a serch of various github repos but couldn’t readiliy find anything.
Thanks
Mike
What version of Qserv are you running?
Yes, LIMIT <N> is what’s ruining the optimization. I will open a JIRA ticket to address this issue. The result of the query shouldn’t be affected by the presence of LIMIT <N>.
It’s not a version-specific problem. It’s in all versions of Qserv since the optimization was introduced ~3 years ago.
Think we’re on 2024.5.1-rc7 on our prod cluster.
Thanks both.
Though I don’t understand why the US-RSP portal query is quick? That has the limit condition in AFAICT
I’m curious, how faster are queries submitted via the US-RSP portal compared to yours?
I have registered the following JIRA ticket to address the issue: https://rubinobs.atlassian.net/browse/DM-51585
On the US-RSP the portal counts query returns in around 5 seconds. This seemed like it’s using the precalculated stats. If I add a WHERE clause it takes a bit longer 30s? Maybe it’s just that you’re qserv is that much quicker.
On ours it takes tens of minutes and I can see it querying each chunk, i.e. actually doing a count.
The optimized queries should be returned within less than a second. 5 seconds means scan, which is very fast if WHERE is not present. 30 seconds is a reasonable performance if some light-weight WHERE is present. Qserv needs to be fixed to address this problem.