Remove slow "or" conditions from "in" statements

XMLWordPrintable

    • S25-W38/39, S25-W40/41, S25-W46/47, S25-W48/49, S25-W50/51/52/01
    • 1

      Zabbix server, Zabbix proxy and frontend can generate such queries in some places where select in batches is not performed:

      SELECT * FROM table
WHERE id IN (1, 2, ..., 1000)
   OR id IN (1001, 1002, ..., 2000)

      However especially on PostgreSQL they are known to work extremely slow, recent example:
      ZBX-26732

      They should be simplified so that database engine can analyze them easier and perform fast and efficiently:

      SELECT * FROM table WHERE id IN (1, 2, ..., 1000,1001, 1002, ..., 2000)

      MySQL can also optimize queries with or conditions into single in but there is not reason to put strain on MySQL to do this work and it's much faster with single in statement. SQLite3 can work old way with or if needed.
      Also please see ZBX-24435

        1. ZBX-26769-7-0.diff
          2 kB

            Assignee:
            Vladislavs Sokurenko
            Reporter:
            Vladislavs Sokurenko
            Team A
            Votes:
            1 Vote for this issue
            Watchers:
            10 Start watching this issue

              Created:
              Updated:
              Resolved:

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