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

Possible performance problem with recursive CTE and UNION involving TEXT/BLOB columns

    XMLWordPrintable

    Details

      Description

      The CTE below is intentionally endless. It's controlled by max_recursive_iterations, which works just fine. But the execution time is radically different, depending on whether we use a VARCHAR column, or a TEXT column of the same length.

      DROP TABLE IF EXISTS t;
      CREATE TABLE t (c1 VARCHAR(255), c2 TINYTEXT);
      INSERT INTO t VALUES ('a','a'),('b','b'),('c','c'),('d','d');
       
      SET max_recursive_iterations = 8;
       
      WITH RECURSIVE cte(f) AS (
        SELECT c2 FROM t
        UNION
        SELECT c2 FROM t, cte
      ) SELECT COUNT(*) FROM cte;
       
      WITH RECURSIVE cte(f) AS (
        SELECT c1 FROM t
        UNION
        SELECT c1 FROM t, cte
      ) SELECT COUNT(*) FROM cte;
      

      Result with TEXT column (15.23 sec)

      MariaDB [test]> WITH RECURSIVE cte(f) AS (
          ->   SELECT c2 FROM t
          ->   UNION
          ->   SELECT c2 FROM t, cte
          -> ) SELECT COUNT(*) FROM cte;
      +----------+
      | COUNT(*) |
      +----------+
      |        4 |
      +----------+
      1 row in set (15.23 sec)
      

      Result with VARCHAR column (0.01 sec)

      MariaDB [test]> WITH RECURSIVE cte(f) AS (
          ->   SELECT c1 FROM t
          ->   UNION
          ->   SELECT c1 FROM t, cte
          -> ) SELECT COUNT(*) FROM cte;
      +----------+
      | COUNT(*) |
      +----------+
      |        4 |
      +----------+
      1 row in set (0.01 sec)
      

      If I understand the idea, with UNION the temporary table should never grow above 4 rows, so there is no reason for such long execution time.

      If I replace UNION with UNION ALL, execution time naturally grows, and it becomes approximately the same for VARCHAR and TEXT column. The result (final count) is the same, which shows that the number of iterations is the same too.

      MariaDB [test]> WITH RECURSIVE cte(f) AS (
          ->   SELECT c2 FROM t
          ->   UNION ALL
          ->   SELECT c2 FROM t, cte
          -> ) SELECT COUNT(*) FROM cte;
      +----------+
      | COUNT(*) |
      +----------+
      |   349524 |
      +----------+
      1 row in set (21.04 sec)
      

      MariaDB [test]> WITH RECURSIVE cte(f) AS (
          ->   SELECT c1 FROM t
          ->   UNION ALL
          ->   SELECT c1 FROM t, cte
          -> ) SELECT COUNT(*) FROM cte;
      +----------+
      | COUNT(*) |
      +----------+
      |   349524 |
      +----------+
      1 row in set (19.43 sec)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: