Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17795

Query with long IN(...) list in WHERE about 40 times slower

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Incomplete
    • 10.3.11
    • N/A
    • Optimizer
    • Ubuntu 18.04
      MariaDB Repository
      RAM 32GB
      8 CPU cores

    Description

      Query that used to take about 5 seconds in MariaDB 10.2.19 now needs 200 seconds.
      List in IN is about 1500 values.

      MariaDB 10.2.19:

      # Time: 181122  9:56:07
      # User@Host: 
      # Thread_id: 6298341  Schema: leitsystem  QC_hit: No
      # Query_time: 6.211773  Lock_time: 0.000545  Rows_sent: 25  Rows_examined: 825043
      # Rows_affected: 0
      # Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
      # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
      #
      # explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
      # explain: 1    SIMPLE  zr_te_value     range   PRIMARY,ts      PRIMARY 9       NULL    420882  412509.00       100.00  100.00  Using where; Using temporary; Using filesort
      #
      SET timestamp=1542876967;
      SELECT CONCAT("9906643000004NEXT", LPAD(next_id_int, 16, "0")) AS next_id, ts - MOD(ts, 300), ROUND(AVG(value), 3), AVG(quality) FROM leitsystem.zr_te_value WHERE next_id_in
      t IN (6447,5272,...,3104) 
      AND ts >= 1542668700 AND ts < 1542755400 AND zr_art = 12 GROUP BY next_id, ts DIV 300 ORDER BY ts ASC LIMIT 0, 25;;
      

      MariaDB 10.3.11:

      # Time: 181122  9:20:42
      # User@Host: 
      # Thread_id: 82  Schema: leitsystem  QC_hit: No
      # Query_time: 214.707173  Lock_time: 0.000781  Rows_sent: 25  Rows_examined: 211389323
      # Rows_affected: 0  Bytes_sent: 1773
      # Tmp_tables: 3  Tmp_disk_tables: 0  Tmp_table_sizes: 92843912
      # Full_scan: Yes  Full_join: Yes  Tmp_table: Yes  Tmp_table_on_disk: No
      # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes
      #
      # explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
      # explain: 1    PRIMARY <subquery2>     ALL     distinct_key    NULL    NULL    NULL    1548    1548.00 100.00  100.00  Using temporary; Using filesort
      # explain: 1    PRIMARY zr_te_value     ref     PRIMARY,ts      PRIMARY 5       const,tvc_0._col_1      218     136287.92       100.00  0.20    Using where
      # explain: 2    MATERIALIZED    <derived3>      ALL     NULL    NULL    NULL    NULL    1548    1548.00 100.00  100.00
      # explain: 3    DERIVED NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    No tables used
      #
      use leitsystem;
      SET timestamp=1542874842;
      SELECT CONCAT("9906643000004NEXT", LPAD(next_id_int, 16, "0")) AS next_id, ts - MOD(ts, 300), ROUND(AVG(value), 3), AVG(quality) FROM leitsystem.zr_te_value WHERE next_id_in
      t IN (6447,5272,...,3104) 
      AND ts >= 1542668700 AND ts < 1542755400 AND zr_art = 12 GROUP BY next_id, ts DIV 300 ORDER BY ts ASC LIMIT 0, 25;;
      

      Already discussed this in MDEV-12176

      Attachments

        Issue Links

          Activity

            Hi Igor,

            FYI I've added a really bad case of wrong semijoin optimization in https://jira.mariadb.org/browse/MDEV-18850 (note it's not assigned anymore to anyone now?)

            Thanks,
            Jocelyn

            joce jocelyn fournier added a comment - Hi Igor, FYI I've added a really bad case of wrong semijoin optimization in https://jira.mariadb.org/browse/MDEV-18850 (note it's not assigned anymore to anyone now?) Thanks, Jocelyn
            SneakyPhil Phil Porada added a comment -

            Is there any information I can provide to help get this fixed in MariaDB 10.3.x? I run a piece of software that's affected by this and the subquery optimizer.

            SneakyPhil Phil Porada added a comment - Is there any information I can provide to help get this fixed in MariaDB 10.3.x? I run a piece of software that's affected by this and the subquery optimizer .

            Phil,
            What do you mean by 'to get this fixed'?

            igor Igor Babaev (Inactive) added a comment - Phil, What do you mean by 'to get this fixed'?

            Automated message:
            ----------------------------
            Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

            julien.fritsch Julien Fritsch added a comment - Automated message: ---------------------------- Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

            Having read through the entire ticket, there are 3-5 different scenario's being discussed, most in EOL versions, including some of which have been discussed in other tickets and/or resolved.

            All reporters, if you still have any issues in current versions (i.e. >=10.5.28 or perhaps best >=10.6.21, as 10.5 will be EOL soon), please create a new bug report with a detailed SQL testcase showing the issue you are seeing. Thank you

            Roel Roel Van de Paar added a comment - Having read through the entire ticket, there are 3-5 different scenario's being discussed, most in EOL versions, including some of which have been discussed in other tickets and/or resolved. All reporters, if you still have any issues in current versions (i.e. >=10.5.28 or perhaps best >=10.6.21, as 10.5 will be EOL soon), please create a new bug report with a detailed SQL testcase showing the issue you are seeing. Thank you

            People

              igor Igor Babaev (Inactive)
              gunni Frank Sagurna
              Votes:
              6 Vote for this issue
              Watchers:
              18 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.