You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "FrankChen021 (via GitHub)" <gi...@apache.org> on 2025/04/08 10:19:11 UTC

[I] Query performance significantly degrades after upgrading from 22 to 27 (druid)

FrankChen021 opened a new issue, #17891:
URL: https://github.com/apache/druid/issues/17891

   ### Description
   
   After we upgraded one of our clusters from 22 to 27, the performance of a user query drastically degraded from previously 5 seconds to more than 300 seconds when querying on a data set over 2 billion rows.
   
   The SQL looks like this:
   
   ```sql
   SELECT
   id,
   sum(val)/30 as l30d_ado
   FROM
   mp
   WHERE country = 'xx'
   AND (is_cross_border = 1
       or id IN
       (
           570092232
               ,
           ... a lots of id
       )
   )
   AND __time >= timestamp '2025-03-08 17:00:00'
   AND __time < timestamp '2025-04-07 17:00:00'
   GROUP BY id
   ORDER BY l30d_ado DESC
   ```
   
   After some investigation, we found that the EXPLAIN result is different. On 22, it generates a native query for the `id` like this:
   
   ```json
   {
           "type": "or",
           "fields": [
             {
               "type": "bound",
               "dimension": "is_cross_border",
               "lower": "1",
               "upper": "1",
               "lowerStrict": false,
               "upperStrict": false,
               "extractionFn": null,
               "ordering": {
                 "type": "numeric"
               }
             },
             {
               "type": "in",
               "dimension": "id",
               "values": [
                 "788563396",
                 "769186459",
                 "1189559866",
                 "1210227126",
                 "998697532",
                 ....a lot of id
   ```
   
   However, on Druid 27, the EXPLAIN for the `id` field is sth like:
   
   ```json
        {
           "type": "or",
           "fields": [
             {
               "type": "bound",
               "dimension": "is_cross_border",
               "lower": "1",
               "upper": "1",
               "ordering": {
                 "type": "numeric"
               }
             },
             {
               "type": "bound",
               "dimension": "id",
               "lower": "570092232",
               "upper": "570092232",
               "ordering": {
                 "type": "numeric"
               }
             },
             {
               "type": "bound",
               "dimension": "id",
               "lower": "664177432",
               "upper": "664177432",
               "ordering": {
                 "type": "numeric"
               }
             },
             {
               "type": "bound",
               "dimension": "id",
               "lower": "881910562",
               "upper": "881910562",
               "ordering": {
                 "type": "numeric"
               }
             },
            ... a lot of 'id' of 'bound' type
   ```
   
   Druid 27 generates lots of 'bound' filter for the `id` field. If we copy the native query generated on Druid 22 to Druid 27 and run it, it takes less than 1 seconds to get the result, which is as expected.
   
   And if we look at schema of the data source on two different clusters, we found that:
   on the old one(Druid 22), the `id` field is shown as `BIGINT` while it's shown as `VARCHAR` on Druid 27.
   
   In supervisor configuration of these two clusters, the `id` fields are all declared as `long` in the dimension spec.
   However, daily batch ingestion tasks overwrites all realtime segments. And in the batch ingestion task spec, the `id` field are both declared as `string`(which should be long) in the dimension spec.
   
   So I think this type conflict in segments generated by batch ingestion and realtime ingestion creates the problem.
   The following table summaries the difference of two versions:
   
    | version | type of id declared in supervisor | type of id declared in batch ingestion | type displayed in data source | Native Query Plan |
    |--------|----|--------------|-------------|-------------|
    | Druid 22 | long | string | BIGINT | all ids are converted to string, and `IN` operator is used |
    | Druid 27 | long | string | VARCHAR | all ids are converted to `bound` operator one by one |
   
   
   There comes serveral question, which is hard to explain:
   1. if the type in the data source is BIGINT on Druid 22, why does the Native Query convert it to string to use in the `IN` operator ?
   2. if the type in data soruce is VARCHAR on Druid 27, why does it generate query to use `bound`?
   3. why are the type displayed in the data source on two clusters different? which change between 22 and 27 might cause this problem?
   
   @gianm @clintropolis do u have any idea?
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [I] Query performance significantly degrades after upgrading from 22 to 27 (druid)

Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on issue #17891:
URL: https://github.com/apache/druid/issues/17891#issuecomment-2798445355

   Thank you @gianm @clintropolis for providing more context


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [I] Query performance significantly degrades after upgrading from 22 to 27 (druid)

Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 closed issue #17891: Query performance significantly degrades after upgrading from 22 to 27
URL: https://github.com/apache/druid/issues/17891


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [I] Query performance significantly degrades after upgrading from 22 to 27 (druid)

Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on issue #17891:
URL: https://github.com/apache/druid/issues/17891#issuecomment-2786340839

   For the different query plan, it should be related to the #14542 because I did see `equality` filter is used for the `id` column in this case. (This is also another weired problem that the query plan now shows the `equals` filter is used instead of previous `bound`filter)
   
   ```json
             {
               "type": "equals",
               "column": "id",
               "matchValueType": "LONG",
               "matchValue": 570092232
             }
   ```
   
   this results in skipping the converting selectors to the `in` filter
   
   <img width="1269" alt="Image" src="https://github.com/user-attachments/assets/59b9b6ad-24bc-45cf-88d3-d16994850cdf" />
   
   @clintropolis 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [I] Query performance significantly degrades after upgrading from 22 to 27 (druid)

Posted by "gianm (via GitHub)" <gi...@apache.org>.
gianm commented on issue #17891:
URL: https://github.com/apache/druid/issues/17891#issuecomment-2797391837

   Like @clintropolis I also believe and hope that Druid 32 would do a better job with `IN` filters generally. In addition to #16039 (native numeric `IN` filter) there is also #16388 (speeds up planning). If there are still issues in Druid 32 then we should keep working on it.
   
   > I'm wondering if we can introduce 'Default Behaviour Change' label to PRs and add a dedicated section in the release note, or add such label to the description of related features/changes in the release note?
   
   I do think it makes sense to add another label like "Upgrade Notes" for something that should definitely appear in upgrade notes. I will say why. I dug a little into what happened here. The original PR #14319 is tagged "Release Notes" and has release note text from @clintropolis that says:
   
   > A new broker configuration, druid.sql.planner.metadataColumnTypeMergePolicy adds configurable modes to how column types are computed for the SQL table schema when faced with differences between segments. A new leastRestrictive mode allows choosing the most appropriate type that data across all segments can best be coerced into, and is now the default behavior. This is a subtle behavior change around when segment driven schema migrations will take effect for the SQL schema. With latestInterval, the SQL schema will be updated as soon as the first job with the new schema has published segments in the latest time interval of the data, while using the new leastRestrictive mode, the schema will only be updated once all segments are reindexed to the new type. However, leastRestrictive is likely to have "better" query time behavior and eliminates some query time errors and other oddities that can occur when using latestInterval.
   
   It does mention "new default" and "subtle behavior change", and also justifies why the change was made ("eliminates some query time errors and other oddities").
   
   However, the actual release notes did not use this text, it says:
   
   > You can now better control how Druid reacts to schema changes between segments. This can make querying more resilient when newer segments introduce different types, such as if a column previously contained LONG values and newer segments contain STRING.
   >
   > Use the new Broker configuration, druid.sql.planner.metadataColumnTypeMergePolicy to control how column types are computed for the SQL table schema when faced with differences between segments.
   >
   >Set it to one of the following:
   >
   >    leastRestrictive: the schema only updates once all segments are reindexed to the new type.
   >    latestInterval: the SQL schema gets updated as soon as the first job with the new schema publishes segments in the latest time interval of the data.
   >
   > leastRestrictive can have better query time behavior and eliminates some query time errors that can occur when using latestInterval.
   
   It appears in the "Additional features and improvements" and doesn't mention that this is a behavior change. So it seems that @clintropolis's text was rewritten and lost some important information.
   
   Typically when pulling together release notes, the release manager will look at changes tagged "Release Notes" and "Incompatible". If we had another label like "Upgrade Notes" for something that should definitely appear in upgrade notes, that would guard against omissions like this. The problem with "Release Notes" by itself is that it's too broad: it can be there for any reason, including just being an important new feature.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [I] Query performance significantly degrades after upgrading from 22 to 27 (druid)

Posted by "clintropolis (via GitHub)" <gi...@apache.org>.
clintropolis commented on issue #17891:
URL: https://github.com/apache/druid/issues/17891#issuecomment-2787719181

   >For the different query plan, it should be related to the #14542 because I did see equality filter is used for the id column in this case. (This is also another weired problem that the query plan now shows the equals filter is used instead of previous boundfilter)
   
   I think this should be better behaved in even more recent versions of Druid since https://github.com/apache/druid/pull/16039 which allowed us to move away from the OR of bound/range filters to handle numeric types (check out some of the test changes in that PR to see what i mean).
   
   I think 27 might have ended up in a bit of a sub-optimal state out of the box being released in the middle of some of those transitions from filters matching everything as strings to where we are now by default which is the native layer handles matching directly in the match value type.
   
   You might be able to set context parameter `sqlUseBoundAndSelectors` to prevent the planner from using the new type aware filters in 27, but shouldn't be needed in 30 or newer since the typed in filter can handle either case.
   
   >For backward compatibility druid.sql.planner.metadataColumnTypeMergePolicy should be set to latestInterval.
   But this is not mentioned in the Upgrade notes and incompatible changes section of the release note: https://github.com/apache/druid/releases/tag/druid-27.0.0
   
   I think this is an oversight that the behavior change wasn't called out there, though the new default is generally better behavior in most use cases I think since it allows you to query all of your data correctly


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [I] Query performance significantly degrades after upgrading from 22 to 27 (druid)

Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on issue #17891:
URL: https://github.com/apache/druid/issues/17891#issuecomment-2788399921

   The problem is clear now.
   
   For the `IN` operator in SQL, the planner first converts it to `BoundFilter` and then tries to convert it into `SelectorDimFilter`(1), and at last tries to optimize the selector dim filters into `InDimFilter`(2).
   
   At step 1, 
   
   ```java
         final StringComparator comparator = RowSignatures.getNaturalStringComparator(
             rowSignature,
             SimpleExtraction.of(bound.getDimension(), bound.getExtractionFn())
         );
   
         if (bound.hasUpperBound()
             && bound.hasLowerBound()
             && bound.getUpper().equals(bound.getLower())
             && !bound.isUpperStrict()
             && !bound.isLowerStrict()
             && bound.getOrdering().equals(comparator)) {
           return new SelectorDimFilter(
               bound.getDimension(),
               bound.getUpper(),
               bound.getExtractionFn()
           );
         } else {
           return filter;
         }
   ```
   
   the check `bound.getOrdering().equals(comparator)` is false in this case on Druid 27, because the `bound.getOrdering` is numeric comparator while the comparator calculated by rowSignature is string comparator, so the `BoundFilter` is not converted to `SelectorDimFilter` which mean the step 2 does not run.
   
   After `druid.sql.planner.metadataColumnTypeMergePolicy` to the `latestInterval`, the column type of id is computed as LONG in this case, so the above step 1 and step 2 works to generate a `InDimFilter` as expected.
   
   BTW, the SQL which contains lots of items in the `IN` operator takes seconds to generate a native query plan, while the native query takes only sub-second to execute the query. I'm not sure if https://github.com/apache/druid/pull/16039 addresses the performance problem, will check it later.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [I] Query performance significantly degrades after upgrading from 22 to 27 (druid)

Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on issue #17891:
URL: https://github.com/apache/druid/issues/17891#issuecomment-2791368014

   @clintropolis 
   
   I understand that adding `druid.sql.planner.metadataColumnTypeMergePolicy` is not an incompatible change.
   
   I'm wondering if we can introduce 'Default Behaviour Change' label to PRs and add a dedicated section in the release note, or add such label to the description of related features/changes in the release note?
   
   In this way we can give users more clear information and decide how to deal with these change before they upgrade their clusters.
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [I] Query performance significantly degrades after upgrading from 22 to 27 (druid)

Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on issue #17891:
URL: https://github.com/apache/druid/issues/17891#issuecomment-2786801544

   The difference of column types in different version, is due to #14319
   
   Previously, on Druid 22, the following code ensure that the column type is always from the newest segment, which in this case, is from realtime ingestion, which is type of long.
   
   ```java
               columnTypes.compute(column, (c, existingType) -> {
                 if (existingType == null) {
                   return columnType;
                 }
                 if (columnType == null) {
                   return existingType;
                 }
                 // if any are json, are all json
                 if (ColumnType.NESTED_DATA.equals(columnType) || ColumnType.NESTED_DATA.equals(existingType)) {
                   return ColumnType.NESTED_DATA;
                 }
                 // "existing type" is the 'newest' type, since we iterate the segments list by newest start time
                 return existingType;
               });
   ```
   
   However, on Druid 27, the behaviour is changed to `leastRestrictive`, which returns STRING if STRING and LONG both exists, and in this case since the batch ingestion generates segment with STRING, so the final column type is resolved to STRING.
   
   ```
     public static ColumnType leastRestrictiveType(@Nullable ColumnType type, @Nullable ColumnType other) throws Types.IncompatibleTypeException
     {
       // if either argument is a string, type becomes a string
       if (Types.is(type, ValueType.STRING) || Types.is(other, ValueType.STRING)) {
         return ColumnType.STRING;
       }
   
       ...
     }
   ```
   
   For backward compatibility  `druid.sql.planner.metadataColumnTypeMergePolicy`  should be set to `latestInterval`.
   But this is not mentioned in the `Upgrade notes and incompatible changes` section of the release note: https://github.com/apache/druid/releases/tag/druid-27.0.0
   
   I'm not complaining here, but just try to find out what happend and how we can do better in future.
   
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [I] Query performance significantly degrades after upgrading from 22 to 27 (druid)

Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on issue #17891:
URL: https://github.com/apache/druid/issues/17891#issuecomment-2788112564

   @clintropolis thanks for the reply.
   
   I tried to set `sqlUseBoundAndSelectors` yesterday but it does not help in this case. When it's set the `false`(true is the default), the query plans shows that the `in` operator in SQL is turned out to be a series `equals` filter. The following code should be the cause the optimization of a serieis equal filter is skipped because in this case, the match value type is LONG
   
   ![Image](https://github.com/user-attachments/assets/b115b547-80cc-4646-b247-23237fe77ffd)
   
   I will try to set `druid.sql.planner.metadataColumnTypeMergePolicy` to the `latestInterval`


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org


Re: [I] Query performance significantly degrades after upgrading from 22 to 27 (druid)

Posted by "gianm (via GitHub)" <gi...@apache.org>.
gianm commented on issue #17891:
URL: https://github.com/apache/druid/issues/17891#issuecomment-2797401111

   I added a label https://github.com/apache/druid/labels/Upgrade%20Note and sent a mail about it to the dev list.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org