PolarDB:Limits

General limits

SELECT statement limits

Columnstore indexes only accelerate SELECT queries. If a query contains any of the following structures, the query cannot be accelerated by the columnstore index and will fall back to row store execution:

• Locking reads: SELECT ... FOR UPDATE or SELECT ... FOR SHARE

• A window function with a frame definition — for example, ROWS UNBOUNDED PRECEDING in the OVER() clause:

  SELECT
      time,
      subject,
      val,
      SUM(val) OVER (
          PARTITION BY subject
          ORDER BY time
          ROWS UNBOUNDED PRECEDING  -- Frame definition not supported by IMCI
      ) AS running_total
  FROM
      observations;

• A subquery in the GROUP BY clause — for example, SELECT SUM(a) FROM t1 GROUP BY (SELECT ... FROM ...) as some_subquery

• A subquery in an ORDER BY expression — for example, SELECT a FROM t1 ORDER BY (SELECT ... FROM ...) as some_subquery

• A correlated subquery in the ON condition of a JOIN — for example, WHERE t1.a in (SELECT t2.a FROM t2 INNER JOIN t3 on t2.a = t3.a AND t2.b > t1.b)

• A subquery that contains a window function, where a correlated item appears in the HAVING condition

• A subquery that contains a UNION, where a correlated item appears in one of the UNION subqueries

Expression and function limits

If a SELECT statement includes any unsupported expression or function, the entire query falls back to row store execution.