Constraints for optimization

[nikvas0]

I hereby agree to the terms of the CLA available at: https://yandex.ru/legal/cla/?lang=en

Changelog category (leave one):

• New Feature

Changelog entry (a user-readable short description of the changes that goes to CHANGELOG.md):

• Added CONSTRAINT ... ASSUME ... (without checking during INSERT)
• Added query transformation to CNF (Optimize boolean conditions using CNF / DNF #11749) for more convenient optimization
• Added simple query rewriting using constraints (only simple matching now, will be improved to support <,=,>... later)
• Added ability to replace heavy columns with light
• Added ability to use the index in queries

Detailed description / Documentation draft:

TODO:

• documentation
• more tests for CNF
• improve constraint matching (now it is a simple all-to-all matching)
• refactoring
• settings

[UnamedRus]

EXPLAIN SYNTAX
SELECT count()
FROM
(
    SELECT
        number AS key_a
    FROM numbers(20)
) AS a
WHERE ((key_a >= 1) AND (key_a <= 5)) OR ((key_a >= 5) AND (key_a <= 10)) OR ((key_a >= 10) AND (key_a <= 15))

┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ SELECT count()                                                                                                         │
│ FROM                                                                                                                   │
│ (                                                                                                                      │
│     SELECT number AS key_a                                                                                             │
│     FROM numbers(20)                                                                                                   │
│     WHERE (((((key_a >= 10) AND (key_a >= 5)) AND (key_a <= 5)) AND (key_a <= 10)) AND (key_a >= 1)) AND (key_a <= 15) │
│ ) AS a                                                                                                                 │
│ WHERE (key_a <= 15) AND (key_a >= 1) AND (key_a <= 10) AND (key_a <= 5) AND (key_a >= 5) AND (key_a >= 10)             │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

 set convert_query_to_cnf=0;
 
 EXPLAIN SYNTAX
SELECT count()
FROM
(
    SELECT number AS key_a
    FROM numbers(20)
) AS a
WHERE ((key_a >= 1) AND (key_a <= 5)) OR ((key_a >= 5) AND (key_a <= 10)) OR ((key_a >= 10) AND (key_a <= 15))

Query id: d2d4275f-07b2-412b-9eca-bc40e0b5d180

┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ SELECT count()                                                                                                     │
│ FROM                                                                                                               │
│ (                                                                                                                  │
│     SELECT number AS key_a                                                                                         │
│     FROM numbers(20)                                                                                               │
│     WHERE ((key_a >= 1) AND (key_a <= 5)) OR ((key_a >= 5) AND (key_a <= 10)) OR ((key_a >= 10) AND (key_a <= 15)) │
│ ) AS a                                                                                                             │
│ WHERE ((key_a >= 1) AND (key_a <= 5)) OR ((key_a >= 5) AND (key_a <= 10)) OR ((key_a >= 10) AND (key_a <= 15))     │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

But it looks like it working correctly if we have only 2 groups:

 set convert_query_to_cnf=1;
 
 EXPLAIN SYNTAX
SELECT count()
FROM
(
    SELECT number AS key_a
    FROM numbers(20)
) AS a
WHERE ((key_a >= 1) AND (key_a <= 5)) OR ((key_a >= 5) AND (key_a <= 10))

Query id: 966cb5fb-58a5-4bd2-b8aa-e39c8c7fc9e4

┌─explain─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ SELECT count()                                                                                                                                          │
│ FROM                                                                                                                                                    │
│ (                                                                                                                                                       │
│     SELECT number AS key_a                                                                                                                              │
│     FROM numbers(20)                                                                                                                                    │
│     WHERE ((((key_a <= 5) OR (key_a >= 5)) AND ((key_a <= 10) OR (key_a <= 5))) AND ((key_a >= 1) OR (key_a >= 5))) AND ((key_a >= 1) OR (key_a <= 10)) │
│ ) AS a                                                                                                                                                  │
│ WHERE ((key_a >= 1) OR (key_a <= 10)) AND ((key_a >= 1) OR (key_a >= 5)) AND ((key_a <= 10) OR (key_a <= 5)) AND ((key_a <= 5) OR (key_a >= 5))         │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

[nikvas0]

But it looks like it working correctly if we have only 2 groups:

Fixed

[UnamedRus]

It's possible that #10685 going to be covered by this pr?

[CLAassistant]

All committers have signed the CLA.

[CurtizJ]

@nikvas0 Please, can you sign the CLA? So, I'll be able to merge this PR.

[sevirov]

Internal documentation ticket: DOCSUP-18735