jtuglu1 opened a new pull request, #19645: URL: https://github.com/apache/druid/pull/19645
<!-- Replace XXXX with the id of the issue fixed in this PR. Remove this section if there is no corresponding issue. Don't reference the issue in the title of this pull-request. --> <!-- If you are a committer, follow the PR action item checklist for committers: https://github.com/apache/druid/blob/master/dev/committer-instructions.md#pr-and-issue-action-item-checklist-for-committers. --> ### Description KillUnusedSegments' per-datasource find-interval query (SqlSegmentsMetadataQuery#retrieveUnusedSegmentIntervals) runs `WHERE dataSource=? AND used=? AND end<=? [AND start>=?] AND used_status_last_updated<=? ORDER BY start, end LIMIT n`. The existing (dataSource, used, end, start) index orders by end before start, so it cannot serve `ORDER BY start, end`. On a large datasource (~470k unused segments) MySQL materializes all matching rows and filesorts them just to return LIMIT n; EXPLAIN ANALYZE measured ~11s, with the scan dominating. With ~50 datasources this drove the duty to ~43s/cycle, almost all in the find-interval SQL. #### Plan w/o index: Query: ```sql EXPLAIN ANALYZE SELECT `start`,`end` FROM bdp_druid_realtime_segments WHERE dataSource='realtimeqoe_client_impressions_metrics_high_res' AND used=false AND `end` <= '9999-12-31T23:59:59.999Z' AND used_status_last_updated IS NOT NULL AND used_status_last_updated <= '2026-06-30T00:00:00.000Z' ORDER BY `start`, `end` LIMIT 1000; ``` Result: ``` -> Limit: 1000 row(s) (cost=118118 rows=1000) (actual time=12449..12449 rows=1000 loops=1) -> Sort: bdp_druid_realtime_segments.`start`, bdp_druid_realtime_segments.`end`, limit input to 1000 row(s) per chunk (cost=118118 rows=1.52e+6) (actual time=12449..12449 rows=1000 loops=1) -> Filter: ((bdp_druid_realtime_segments.used = false) and (bdp_druid_realtime_segments.`end` <= '9999-12-31T23:59:59.999Z') and (bdp_druid_realtime_segments.used_status_last_updated is not null) and (bdp_druid_realtime_segments.used_status_last_updated <= '2026-06-30T00:00:00.000Z')) (cost=118118 rows=1.52e+6) (actual time=0.375..11234 rows=418976 loops=1) -> Index lookup on bdp_druid_realtime_segments using IDX_97D22DC483E0FC520AB2C5ECE6CBAE3475553D04 (dataSource='realtimeqoe_client_impressions_metrics_high_res') (cost=118118 rows=1.52e+6) (actual time=0.373..11100 rows=806775 loops=1) ``` <!-- In each section, please describe design decisions made, including: - Choice of algorithms - Behavioral aspects. What configuration values are acceptable? How are corner cases and error conditions handled, such as when there are insufficient resources? - Class organization and design (how the logic is split between classes, inheritance, composition, design patterns) - Method organization and design (how the logic is split between methods, parameters and return types) - Naming (class, method, API, configuration, HTTP endpoint, names of emitted metrics) --> <!-- It's good to describe an alternative design (or mention an alternative name) for every design (or naming) decision point and compare the alternatives with the designs that you've implemented (or the names you've chosen) to highlight the advantages of the chosen designs and names. --> <!-- If there was a discussion of the design of the feature implemented in this PR elsewhere (e. g. a "Proposal" issue, any other issue, or a thread in the development mailing list), link to that discussion from this PR description and explain what have changed in your final design compared to your original proposal or the consensus version in the end of the discussion. If something hasn't changed since the original discussion, you can omit a detailed discussion of those aspects of the design here, perhaps apart from brief mentioning for the sake of readability of this PR description. --> <!-- Some of the aspects mentioned above may be omitted for simple and small changes. --> #### Release note <!-- Give your best effort to summarize your changes in a couple of sentences aimed toward Druid users. If your change doesn't have end user impact, you can skip this section. For tips about how to write a good release note, see [Release notes](https://github.com/apache/druid/blob/master/CONTRIBUTING.md#release-notes). --> <hr> ##### Key changed/added classes in this PR * `MyFoo` * `OurBar` * `TheirBaz` <hr> <!-- Check the items by putting "x" in the brackets for the done things. Not all of these items apply to every PR. Remove the items which are not done or not relevant to the PR. None of the items from the checklist below are strictly necessary, but it would be very helpful if you at least self-review the PR. --> This PR has: - [ ] been self-reviewed. - [ ] using the [concurrency checklist](https://github.com/apache/druid/blob/master/dev/code-review/concurrency.md) (Remove this item if the PR doesn't have any relation to concurrency.) - [ ] added documentation for new or modified features or behaviors. - [ ] a release note entry in the PR description. - [ ] added Javadocs for most classes and all non-trivial methods. Linked related entities via Javadoc links. - [ ] added or updated version, license, or notice information in [licenses.yaml](https://github.com/apache/druid/blob/master/dev/license.md) - [ ] added comments explaining the "why" and the intent of the code wherever would not be obvious for an unfamiliar reader. - [ ] added unit tests or modified existing tests to cover new code paths, ensuring the threshold for [code coverage](https://github.com/apache/druid/blob/master/dev/code-review/code-coverage.md) is met. - [ ] added integration tests. - [ ] been tested in a test Druid cluster. -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
