Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-5416

Increase in WHERE clauses causes query planner problems

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Duplicate
    • Affects Version/s: 8.0.13-4
    • Fix Version/s: None
    • Component/s: MyRocks
    • Labels:
      None
    • Environment:

      Running on Ubuntu 18.04 inside of Docker

      Description

      I am having a weird issue where adding more WHERE clauses to a query switches to a full table/index scan, but doesn't provide details in EXPLAIN. This increases the query execution time 1000x from ~40ms to ~40s. In this specific scenario, it happens when I go from 36 to 37 clauses.

       

      The query looks like this

      SELECT pinterest_id,domain_id,url_id FROM linkmetrics_pinterest
      {{ WHERE (}}
      {{  (domain_id=1 AND url_id=5 AND retrieved_at>=1549324800) OR }}
        (domain_id=2 AND url_id=6 AND retrieved_at>=1549324800) OR
        ...
      {{ );}}

       

      against this table

      {{CREATE TABLE `linkmetrics_pinterest` (}}
      `pinterest_id` bigint(20) NOT NULL,
      `domain_id` bigint(20) NOT NULL,
      `url_id` bigint(20) NOT NULL,
      `retrieved_at` bigint(20) NOT NULL,
      `shared` int(11) DEFAULT NULL,
      PRIMARY KEY (`pinterest_id`),
      KEY `linkmetrics_pinterest_urlsretrieved_at_idx` (`domain_id`,`url_id`,`retrieved_at`) COMMENT 'rev:default'
      {{ ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8}}

        

      EXPLAIN is identical, except for the huge jump in rows scanned:

       

      With 36 WHERE clauses

      id: 1
      select_type: SIMPLE
      table: linkmetrics_pinterest
      partitions: NULL
      type: range
      {{ possible_keys: linkmetrics_pinterest_urlsretrieved_at_idx}}
      key: linkmetrics_pinterest_urlsretrieved_at_idx
      key_len: 24
      ref: NULL
      rows: 36
      filtered: 100.00
      Extra: Using where; Using index

       

      With 37 WHERE clauses

      id: 1
      select_type: SIMPLE
      table: linkmetrics_pinterest
      partitions: NULL
      type: range
      {{ possible_keys: linkmetrics_pinterest_urlsretrieved_at_idx}}
      key: linkmetrics_pinterest_urlsretrieved_at_idx
      key_len: 24
      ref: NULL
      rows: 8408659
      filtered: 100.00
      Extra: Using where; Using index

       

      I tried running SHOW WARNINGS after the EXPLAIN to get extended output, but all that did was prefix my original query with: | Note | 1003 | /* select#1 */ 

      I also tried running the same query with and without FORCE INDEX, and that didn't make any difference in the query plan. I also executed the same logical query by executing each where clause independently and doing a UNION ALL, which stayed in the ~40ms execution range, so there isn't another bottleneck slowing down the slightly larger query.

      I'm happy to provide any further output that might help to make this easier to debug/reproduce.

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  lalit.choudhary Lalit Choudhary
                  Reporter:
                  derekperkins Derek Perkins
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: