Skip to content

[BUG]Span query in PPL is slower than date histogram aggregation in query DSL #3528

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
gaobinlong opened this issue Apr 9, 2025 · 0 comments · May be fixed by #3550
Open

[BUG]Span query in PPL is slower than date histogram aggregation in query DSL #3528

gaobinlong opened this issue Apr 9, 2025 · 0 comments · May be fixed by #3550
Labels
enhancement New feature or request performance Make it fast! PPL Piped processing language

Comments

@gaobinlong
Copy link

gaobinlong commented Apr 9, 2025 •
edited
Loading

What is the bug?

In Discover page of OSD, when executing a PPL query against an index pattern containing very huge data(about 750B documents), the query latency of PPL is much more than the latency of the similar query in DQL, which is one hundred seconds vs. seconds.

The PPL is: source = index* | where @timestamp>= '2025-03-25 03:31:32' and@timestamp<= '2025-04-09 03:31:32' | stats count() by span(@timestamp, 12h),

and the similar query DSL is:

{
  "query": {
    "bool": {
      "must": [],
      "filter": [
       
        {
          "range": {
            "@timestamp": {
              "gte": "2025-03-25T03:31:32.935Z",
              "lte": "2025-04-09T03:31:32.935Z",
              "format": "strict_date_optional_time"
            }
          }
        }
      ]
    }
  },
  "size":500,
  "aggs": {
    "2": {
      "date_histogram": {
        "field": "@timestamp",
        "fixed_interval": "12h",
        "time_zone": "+00:00",
        "min_doc_count": 1
      }
    }
  }
}

.

I see PPL will convert the span query to a composite aggregation, like this:

"aggregations": {
    "composite_buckets": {
      "composite": {
        "size": 1000,
        "sources": [
          {
            "span(@timestamp,12h)": {
              "date_histogram": {
                "field": "@timestamp",
                "missing_bucket": true,
                "missing_order": "first",
                "order": "asc",
                "fixed_interval": "12h"
              }
            }
          }
        ]
      },
      "aggregations": {
        "count()": {
          "value_count": {
            "field": "_index"
          }
        }
      }
    }
  }

, seems composite aggregation is slower than the date histogram aggregation.

How can one reproduce the bug?
Steps to reproduce the behavior:

  1. Find a big dataset which contains billions of documents
  2. Execute both the PPL and query DSL above to compare the latency

What is the expected behavior?
PPL should improve the performance.

What is your host/environment?
OpenSearch 3.0.0

Do you have any screenshots?
If applicable, add screenshots to help explain your problem.

Do you have any additional context?
Add any other context about the problem.
@gaobinlong gaobinlong added bug Something isn't working untriaged labels Apr 9, 2025
@Swiddis Swiddis added performance Make it fast! enhancement New feature or request and removed bug Something isn't working labels Apr 9, 2025
This was referenced Apr 15, 2025
Draft
Open
@penghuo penghuo mentioned this issue May 27, 2025
Merged
7 tasks
@penghuo penghuo added the PPL Piped processing language label May 29, 2025
@penghuo penghuo added this to PPL 2025 May 30, 2025
@github-project-automation github-project-automation bot moved this to Not Started in PPL 2025 May 30, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
enhancement New feature or request performance Make it fast! PPL Piped processing language
Projects
PPL 2025
Status: Not Started
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Speed up aggregation pushdown for single group-by expression LantaoJin/search-plugins-sql
4 participants
@LantaoJin @penghuo @gaobinlong @Swiddis