https://pinot.apache.org/ logo
Join Slack
Powered by
```SELECT * FROM tableA WHERE text_match( ...
# pinot-dev
t
Copy code
SELECT * FROM tableA WHERE 
    text_match(
      "json_data", '"instance*50331649"'
    ) 
    AND json_extract_scalar(
      "json_data", '$.instance', 'INT', 
      0
    ) = 50331649
For queries with a boolean condition for which one side has index (e.g., text index) and the other side is costly matching. Does Pinot allow filtering using the text index first before evaluating the more costly json function? Looks like today we do not evaluation in parallel (using EXPLAIN PLAN). Note that we can not build json index on the column due to large json index size.
k
It does run the text match first before applying json extract match
Also, you can index specific json fields in json
t
ok. so the boolean order matters here, right? for json index, we can not build it only for specific paths.
k
no order does not matter.. json index, you can build it for specific paths cc @Jackie
t
Due to the large number of json path patterns, we can not selectively index a subset of them. Also json_extract_scalar is just an example here -- any other costly evaluation function is the same. But if order does not matter, then the selectivity of text index already help to reduce the cost of json evaluation. Could this be a bug? @Jack Luo
j
Order matters when both side do not have index. We reorder the filters so that the one that can be solved with index is evaluated first
j
Let me clear up the confusion a little bit.
for json index, we can not build it only for specific paths.
What Ting meant is that although we can specify which paths to build json index in the table-config, in practice, because there's 8000+ tables with unique json column content, it's not possible to manually customize which specific paths we want to include or exclude. We can't use json-index in production due to the huge space overhead and high heap memory usage. JSON index takes significant % of the the entire segment size. We have already filtered out json fields deeper than 3 levels, ignored arrays. For example: A table with 8 table columns with data (7 non-json columns, 1 json column). We picked a random segment to inspect its data size. The segment (columns.psf file) is 158MB and when inspecting the 
index_map
file , json index took 98% of the segment. In contrast, if we perform text-index on the same json column, the text-index consumes 12MB.
The content typically stored in the 
json
column field is the following:
Copy code
{
  "cluster": "dca11-stateless01",
  "duration_in_sec": 604800,
  "instance": "50331648",
  "need_raw_data": "false",
  "build_time": "2023-02-15T15:17:01Z",
  "sgroupby_attributes": null,
  "language": "go",
  "source": "/opt/uber/mesos-agent/udocker-log-links/sawmill-query/jaeger/compute-0/50331648/thermos-7e71262a-97cf-4819-97cc-39c27887cd32-0-9/stdlogs/stdout",
  "type": "log",
  "clickhouse_query": "WITH if(indexOf(\"string.names.17\", 'process.service_name') > 0, \"string.values.17\"[indexOf(\"string.names.17\", 'process.service_name')], NULL) AS \"process.service_name\" SELECT DISTINCT `process.service_name` FROM \"default\".\"Distributed.jaeger-service-operation-index-staging.20221213cecd1qrhjh5vp8fuq2ng\" PREWHERE \"_namespace\" = 'jaeger-service-operation-index-staging' AND (_timestampMillis > 1679445726410) SETTINGS max_threads=5, optimize_monotonous_functions_in_order_by = 0, timeout_before_checking_execution_speed = 0, max_execution_time=55, timeout_overflow_mode='throw' FORMAT JSONCompact",
  "build_hash": "c8ba2aba90",
  "sql": "SELECT DISTINCT `process.service_name` FROM `jaeger-service-operation-index-staging` WHERE `_timestampMillis` > 1679445726410",
  "orderby_attributes": null,
  "@reserved": {
    "rawLogSize": 1635,
    "latencyTimestamps": {
      "collectorPickup": 1680050526791368400
    },
    "collector": {
      "inode": 64357155,
      "pipeline": "up-stdlogs-with-mesos-executor-id",
      "path": "/opt/uber/mesos-agent/udocker-log-links/sawmill-query/jaeger/compute-0/50331648/thermos-7e71262a-97cf-4819-97cc-39c27887cd32-0-9/stdlogs/stdout",
      "filename": "stdout",
      "kafka_topic": "sawmill-query",
      "env": "production"
    }
  },
  "condition_attributes": [
    "_timestampMillis"
  ],
  "partition": "compute-0",
  "select_attributes": [
    "process.service_name"
  ],
  "fields_count": 8,
  "processed_query": "{\n \"query\": \"SELECT DISTINCT `process.service_name` FROM `jaeger-service-operation-index-staging` WHERE `_timestampMillis` \\u003e 1679445726410\"\n}",
  "mesos_executor_id": "thermos-7e71262a-97cf-4819-97cc-39c27887cd32-0-9",
  "deployment": "3",
  "namespaceCount": 1,
  "offset": 95004648,
  "service_name": "sawmill-query",
  "datacenter": "dca11",
  "query_type": "sql",
  "env": "jaeger",
  "pipeline": "us1",
  "caller": "query/query_pattern.go:26",
  "component": "query-translator",
  "@timestamp": "2023-03-29T00:42:06.791Z",
  "application": "sawmill-query",
  "ts": 1680050526.4303305,
  "namespaces": [
    "jaeger-service-operation-index-staging"
  ]
}
In an example where most of the kv are moved out of the JSON column into dedicated column, JSON index still took majority of the segment disk consumption
j
Have you tried disabling the array nesting?
j
Copy code
"jsonIndexConfigs": {
        "json_data": {
          "maxLevels": 3,
          "excludeArray": true,
          "disableCrossArrayUnnest": true,
          "excludePaths": [
            "$.@reserved"
          ],
          "excludeFields": []
        }
      },
execludeArray
= true, 
disableCrossArrayUnnest
= true.
j
Hmm, quite surprised it is still so large
j
image.png
j
Does it have almost unique kv pairs?
j
The values may contain unique values (UUIDs), but the keys appear very frequently
My hunch is that `json_index`'s "flattened table" isn't dictionary encoded or compressed. Other columns in the table are dictionary encoded or using raw forward index + zstandard compression
j
Json index stores KV as one value for lookup
If value doesn’t repeat, all entries are treated as unique
j
Anyways, I am happy to provide some data to trigger the problem if you are interested. The problem with our alternate approach to 
json_index
is
Copy code
SELECT * FROM tableA WHERE 
    text_match(
      "json_data", '"instance*50331649"'
    ) 
    AND json_extract_scalar(
      "json_data", '$.instance', 'INT', 
      0
    ) = 50331649
from emperical observation seems like 
text_match
(with index) and 
json_extract_scalar
(scan) is executing concurrently instead of sequentially.
Copy code
# Query finished in 27ms
SELECT 
  runtime_env, 
  count(*) 
FROM 
  "sawmill-query-for-pinot" 
WHERE 
  (
    _timestampMillis <= 1679691885000 
    AND _timestampMillis > 1679432712000
  ) 
  AND text_match(
    "json_data", '"instance*33554433"'
  ) 
GROUP BY 
  runtime_env 
ORDER BY 
  count(*) desc 
LIMIT 
  10
Copy code
# Query finished in 1880ms
SELECT 
  runtime_env, 
  count(*)  
FROM 
  "sawmill-query-for-pinot" 
WHERE 
  (
    _timestampMillis <= 1679691885000 
    AND _timestampMillis > 1679432712000
  ) 
  AND (
    text_match(
      "json_data", '"instance*33554433"'
    ) 
    AND json_extract_scalar(
      "json_data", '$.instance', 'INT', 
      0
    ) = 33554433
  ) 
GROUP BY 
  runtime_env 
ORDER BY 
  count(*) desc 
LIMIT 
  10
Adding
Copy code
AND json_extract_scalar(
   "json_data", '$.instance', 'INT', 
   0
) = 33554433
slowed down query by 50x+
Querying w/o 
text_match
is around 1800ms as well.
We seem to have found a hacky work around.
Copy code
{
   "name": "json_data",
   "encodingType": "RAW",
   "indexType": "TEXT",
   "indexTypes": [
      "TEXT"
   ],
   "compressionCodec": "ZSTANDARD",
   "properties": {
      "enableQueryCacheForTextIndex": "true" <-- add this
   }
},
If we enable 
queryCacheForTextIndex
, it seem to force pinot query to be executed in sequential order (
text_match
first, then 
json_extract_scalar
). Perhaps there are some bug with the query planner. In which case, even with 
json_extract_scalar
, query now completes <50ms rather than the previous 1000ms+.
j
Interesting
Json extract scalar is expression based, which should always be evaluated in the last
j
That's what we observe as well if we don't combine it with 
text_match
. For example, 
json_match
+ 
json_extract_scalar
seems to be bug free.
j
The reordering logic reside in 
FilterOperatorUtils.reordeAndFilterChildOperators()
, and we do order 
ExpressionFilterOperator
after 
TextMatchFilterOperator
Could there be other factors that cause this?
j
I think we cannot rule out other factors. I have only described the observation which seem fits the description, but not necessary confirmed the root cause. We'll do some further performance debugging today.
I think the root cause may also be correlation to # of nodes in the cluster. It seems with larger # of nodes (say 20), 
json_match
+ 
json_extract_scalar
runs even slower than 
json_extract_scalar
by itself!
text_match
+ 
json_extract_scalar
=> 3568ms
json_extract_scalar
only (no index at all) => 620ms
text_match
only => 53ms
So @Jackie, you are right, it is indeed probably not a query planner issue because using 
text_match
+ 
json_scalar_extract
together is significantly slower than running either of them individually. It's something worse.
ack 1
2 Views
Open in Slack
PreviousNext
Powered by