-- Basic aggregate + filter by sharding key
EXPLAIN (
    ANALYZE,
    VERBOSE
)
SELECT
    count(*)
FROM
    documents
WHERE
    company_id = 5;

EXPLAIN (
    ANALYZE,
    VERBOSE
)
SELECT
    count(*)
FROM
    documents_node2
WHERE
    company_id = 5;

-- Basic aggregate + filter by sharding key + fake group by -> then it is pushdown sometimes
EXPLAIN (
    ANALYZE,
    VERBOSE
)
SELECT
    count(*)
FROM
    documents
WHERE
    company_id = 5
GROUP BY company_id;

EXPLAIN (
    ANALYZE,
    VERBOSE
)
SELECT
    sum(id)
FROM
    documents
WHERE
    company_id = 5
GROUP BY company_id;

-- Basic join by secondary key + filter by sharding key
EXPLAIN (
    ANALYZE,
    VERBOSE
)
SELECT
    *
FROM
    documents
    INNER JOIN users ON documents.user_id = users.id
WHERE
    documents.company_id = 5
    AND users.company_id = 5;

EXPLAIN (
    ANALYZE,
    VERBOSE
)
SELECT
    *
FROM
    documents_node2
    INNER JOIN users_node2 ON documents_node2.user_id = users_node2.id
WHERE
    documents_node2.company_id = 5
    AND users_node2.company_id = 5;

-- Join by secondary key + aggregate + filter by sharding key
EXPLAIN (
    ANALYZE,
    VERBOSE
)
SELECT
    count(*)
FROM
    documents
    INNER JOIN users ON documents.user_id = users.id
WHERE
    documents.company_id = 5
    AND users.company_id = 5;

EXPLAIN (
    ANALYZE,
    VERBOSE
)
SELECT
    count(*)
FROM
    documents_node2
    INNER JOIN users_node2 ON documents_node2.user_id = users_node2.id
WHERE
    documents_node2.company_id = 5
    AND users_node2.company_id = 5;

-- Join by secondary key + aggregate + group by secondary key
EXPLAIN (
    ANALYZE,
    VERBOSE
)
SELECT
    user_id,
    count(*) AS documents_count
FROM
    documents
    INNER JOIN users ON documents.user_id = users.id
WHERE
    documents.company_id = 5
    AND users.company_id = 5
GROUP BY
    user_id;

EXPLAIN (
    ANALYZE,
    VERBOSE
)
SELECT
    user_id,
    count(*) AS documents_count
FROM
    documents_node2
    INNER JOIN users_node2 ON documents_node2.user_id = users_node2.id
WHERE
    documents_node2.company_id = 5
    AND users_node2.company_id = 5
GROUP BY
    user_id;