Hey All,
I have a table with around 50million reco...
# generalp
pratush maheshwari
07/23/2024, 9:45 AMHey All,
I have a table with around 50million records, while I am trying to do a filter using IN clause having 20,000 records, it takes around 7-8 sec.
sample query : select sum(value) from fact_table where names in (-----20k filters----)
But when I load those 20k filters in a other table, and use a subquery to filter it just takes 1-2 sec.
sample query : select sum(value) from fact_table where names in (select names from names_table)
So two questions :
1. Any reason normal IN clause while passing values takes 7-8 sec compared with Subquery?
2. Is there any way I can improve the performance while passing 1000s of values directly?
l
Laksh Singla
07/23/2024, 9:51 AMwhat are the two queries that are giving you different results? Can you share the native plan for those? You might wanna try setting ``inSubQueryThreshold` to some lower value and see if that helps the query runtime. From the description it seems that planning time could be high, however since you have ingested the values into another table, perhaps we are able to use the indices on that table to speed up the query
Laksh Singla
07/23/2024, 9:51 AMyou might wanna check if the planning time is high for the first query
p
pratush maheshwari
07/23/2024, 9:59 AMPlease find the explain plan for (select sum(values) from fact_table where names in ('x','y'z along with 15k records)
{
"queryType": "timeseries",
"dataSource": {
"type": "table",
"name": "fact_table"
},
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T082332.096Z/146140482-04-24T153627.903Z"
]
},
"filter": {
"type": "in",
"dimension": "names",
"values": [
"x",
"y",
"z",+15k records
]
},
"granularity": {
"type": "all"
},
"aggregations": [
{
"type": "doubleSum",
"name": "a0",
"fieldName": "value"
}
],
"context": {
"enableWindowing": "True",
"queryId": "123",
"sqlOuterLimit": 1001,
"sqlQueryId": "456",
"sqlStringifyArrays": false,
"useNativeQueryExplain": true
}
}
pratush maheshwari
07/23/2024, 10:02 AMExplain plan for : select sum(values) from fact_table where names in (select name from names_table)
{
"queryType": "timeseries",
"dataSource": {
"type": "join",
"left": {
"type": "table",
"name": "fact_table"
},
"right": {
"type": "query",
"query": {
"queryType": "groupBy",
"dataSource": {
"type": "query",
"query": {
"queryType": "scan",
"dataSource": {
"type": "table",
"name": "names_table"
},
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T082332.096Z/146140482-04-24T153627.903Z"
]
},
"resultFormat": "compactedList",
"columns": [
"names"
],
"legacy": false,
"context": {
"enableWindowing": "True",
"queryId": "4321",
"sqlOuterLimit": 1001,
"sqlQueryId": "1234",
"sqlStringifyArrays": false,
"useNativeQueryExplain": true
},
"granularity": {
"type": "all"
}
}
},
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T082332.096Z/146140482-04-24T153627.903Z"
]
},
"granularity": {
"type": "all"
},
"dimensions": [
{
"type": "default",
"dimension": "names",
"outputName": "d0",
"outputType": "STRING"
}
],
"limitSpec": {
"type": "NoopLimitSpec"
},
"context": {
"enableWindowing": "True",
"queryId": "abcd",
"sqlOuterLimit": 1001,
"sqlQueryId": "efgh",
"sqlStringifyArrays": false,
"useNativeQueryExplain": true
}
}
},
"rightPrefix": "j0.",
"condition": "(\"names\" == \"j0.d0\")",
"joinType": "INNER"
},
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T082332.096Z/146140482-04-24T153627.903Z"
]
},
"granularity": {
"type": "all"
},
"aggregations": [
{
"type": "doubleSum",
"name": "a0",
"fieldName": "value"
}
],
"context": {
"enableWindowing": "True",
"queryId": "48845376-2226-4374-b82e-02b9ffb5ecee",
"sqlOuterLimit": 1001,
"sqlQueryId": "48845376-2226-4374-b82e-02b9ffb5ecee",
"sqlStringifyArrays": false,
"useNativeQueryExplain": true
}
}
pratush maheshwari
07/23/2024, 10:03 AMPlease let me know if you need anything or suggestion, what I can see is using subquery is using join but please help me to explain this.
l
Laksh Singla
07/23/2024, 10:13 AMTry with a lower value of
inSubQueryThresholdp
pratush maheshwari
07/23/2024, 10:21 AMSetting it in context during the runtime is fine or need to change the runtime.properties file?
pratush maheshwari
07/23/2024, 10:32 AMAlso @Laksh Singla, how this threshold works? I see a huge performance improvement when I have "inSubQueryThreshold": 500, but does this mean will it consider only 500 results or what?
l
Laksh Singla
07/23/2024, 10:33 AMit changes how the query gets planned. Can you share the query plan when the inSubQueryThreshold is set
Laksh Singla
07/23/2024, 10:33 AMthe results should be identical.
p
pratush maheshwari
07/23/2024, 10:41 AM@Laksh Singla
query plan after applying inSubQueryThreshold, applied 2 just to generate the plan, but I can see it kind of create some inline table and use a join. But this parameter is very effective.
{
"queryType": "timeseries",
"dataSource": {
"type": "join",
"left": {
"type": "table",
"name": "fact_table"
},
"right": {
"type": "inline",
"columnNames": [
"ROW_VALUE"
],
"columnTypes": [
"STRING"
],
"rows": [
[
"x"
],
[
"y"
],
[
"z"
],
15k more
]
},
"rightPrefix": "j0.",
"condition": "(\"names\" == \"j0.ROW_VALUE\")",
"joinType": "INNER"
},
"intervals": {
"type": "intervals",
"intervals": [
"-146136543-09-08T082332.096Z/146140482-04-24T153627.903Z"
]
},
"granularity": {
"type": "all"
},
"aggregations": [
{
"type": "doubleSum",
"name": "a0",
"fieldName": "value"
}
],
"context": {
"enableWindowing": "True",
"inSubQueryThreshold": 2,
"queryId": "xyz123",
"sqlOuterLimit": 1001,
"sqlQueryId": "456abc",
"sqlStringifyArrays": false,
"useNativeQueryExplain": true
}
}
🙌 1
l
Laksh Singla
07/23/2024, 10:42 AMwhich version are you on btw?
p
pratush maheshwari
07/23/2024, 10:42 AM29.0.1
pratush maheshwari
07/23/2024, 10:43 AMquery plan after applying inSubQueryThreshold, applied 2 just to generate the plan, but I can see it kind of create some inline table and use a join. This understanding is correct right?
l
Laksh Singla
07/23/2024, 10:44 AMyes, its a planning side change. For queries with large amount of values in
INLaksh Singla
07/23/2024, 10:45 AMsetting the threshold will plan the query as a join. The query execution is slower as its a join afaik, however it doesn’t incur the planning overhead
Laksh Singla
07/23/2024, 10:45 AMyou can check how the query performs without the planning overhead by running the native query directly (without the threshold set). That should give an idea how the query would perform if it wasn’t incurring that 7-8s time penalty
p
pratush maheshwari
07/23/2024, 10:47 AMYes I think if this parameter is not set, it takes time to make the query plan which takes plenty of time however this parameter is very useful for large value inside IN Clause
2 Views