Slow query in latest data when more than 950 items selected

XMLWordPrintable

    • Type: Problem report
    • Resolution: Unresolved
    • Priority: Major
    • Affects Version/s: 6.0.29
    • Component/s: API (A)
    • None
    • Environment:
      PostgreSQL 15.6
      timescaledb 2.13.1
    • Prev.Sprint, S25-W44/45, S25-W46/47, S25-W48/49
    • 1

      When loading latest data using sub-filter "With data" it works well if amount of items is bellow 950.
      We tested with ~900 items and the page loads in 1-2 seconds.
      With ~1200 items it initially timed out, but after php/web configuration is updated, it could load in 10-15 minutes.
      Main problem is query:

      LOG:  duration: 928896.744 ms  statement: SELECT DISTINCT itemid FROM history_uint WHERE (itemid IN (3898948,4980401,4980402 .......) OR itemid IN (5428917,5428919, ......)) AND clock>1714319213
      

      Query shortened here.
      We updated the query to exclude the "OR":

      SELECT DISTINCT itemid FROM history_uint WHERE itemid IN (3898948,4980401,4980402,4980403...) AND clock>1714319213
      

      And this executes almost instantly.

      This looks like a problem with the specific DB, but when we change the file:
      include/db.inc.php

      $MAX_NUM_IN = 2000;
      

      (increase form 950)
      Now the frontend also loads data very fast, 1-2 seconds for ~1700 items.

        1. ZBX-24435-7.2.diff
          1 kB
          Vladislavs Sokurenko
        2. ZBX-24435-7-0.diff
          0.8 kB
          Vladislavs Sokurenko

            Assignee:
            Kirils Seve
            Reporter:
            Edgars Melveris
            Team C
            Votes:
            1 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 38.5h
                38.5h