A question about optimizer

Hi all. I’d like to know that does CockroachDB allow generating new Group when applying a transformation?

Says I have a Filter operator which has multiple conjunctions, and part of them can be pushed down, while the rest can not.

Here’s an example:

SELECT * FROM t
WHERE pred_a AND pred_b

Assume that the relation t has partitions that can be pruned by pred_a, we can use a transformation like PredicatePushDown to perform the pruning. How would CockroachDB handle this? Will CockroachDB generate a new Group for it or merge the Filter with the operator downside?

I’ll assume that by “Group” you mean a memo group.

Transformations can create new groups. Consider the table:

CREATE TABLE t (
  k INT PRIMARY KEY,
  a INT,
  b INT,
  INDEX a_idx (a) STORING (b)
)

And the query:

SELECT k FROM t WHERE a = 1 AND b = 2

The “canonical plan”, the base query plan generated before any transformations have been applied, is a scan on the primary index followed by a filter on a and b. This plan can be viewed by forcing a primary index scan:

EXPLAIN SELECT k FROM t@primary WHERE a = 1 AND b = 2;
              info
---------------------------------
  • filter
  │ filter: (a = 1) AND (b = 2)
  │
  └── • scan
        missing stats
        table: t@primary
        spans: FULL SCAN

If we do not force a primary index scan, we see a query plan that scans a_idx.

EXPLAIN SELECT k FROM t WHERE a = 1 AND b = 2;
           info
--------------------------
  • filter
  │ filter: b = 2
  │
  └── • scan
        missing stats
        table: t@a_idx
        spans: [/1 - /1]

The scan on the primary index and the scan on a_idx are in different groups. They must be because all expressions in the same group must be logically equivalent, but the two scans are not logically equivalent. The primary index scan produces all rows and the scan on a_idx produces only rows where a = 1.

In this case the GenerateConstrainedScans rule (most of its logic lives in a custom function) is adding the scan on a_idx to a new memo group.

2 Likes

Thanks! That makes sense.

1 Like