citusdata / citusdata/citus

Recursive planning should not pull unnecessary data in the target list

Open
#3,118 0 comments 0 reactions 0 assignees View on GitHub
performance
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

In multi-shard SELECT queries, Citus is mostly smart enough to pull only the necessary data to the coordinator. However, with recursive planning, Citus

```SQL
-- have less verbose output
SET citus.shard_count TO 4;

CREATE TABLE users_table (user_id int, time timestamp, value_1 int, value_2 int, value_3 float, value_4 bigint);
SELECT create_distributed_table('users_table', 'user_id');

SET citus.log_remote_commands TO ON;
SET client_min_messages TO DEBUG;

-- query pushdown, we only need user_id in the coordinator, so it doesn't make sense
-- to pull other columns
-- as you can see on the queries sent to workers, we only do ` SELECT worker_column_1 AS user_id FROM `
-- meaning that the other column is not sent to the worker
-- Note: we use random to prevent postgresl to do the optimization (pull up the subquery)
SELECT user_id FROM (SELECT user_id, value_1 * random() FROM users_table) as foo;

LOG: issuing SELECT worker_column_1 AS user_id FROM (SELECT foo.user_id AS worker_column_1 FROM (SELECT users_table.user_id, ((users_table.value_1)::double precision OPERATOR(pg_catalog.*) random()) FROM sc1.users_table_102394 users_table) foo) worker_subquery
DETAIL: on server onderkalaci@localhost:9700
....

-- another query with recursive planning forced by OFFSET 0
-- this time the worker query includes both user id and
SELECT user_id FROM (SELECT user_id, value_1 * random() FROM users_table OFFSET 0) as foo;

LOG: issuing SELECT user_id, ((value_1)::double precision OPERATOR(pg_catalog.*) random()) FROM sc1.users_table_102394 users_table WHERE true
DETAIL: on server onderkalaci@localhost:9700
....
```

There are some basic prototypes can be found here: https://github.com/citusdata/citus/pull/2504/commits/00b93382940b4c27e5d0cedf176e5ed2de3596bf

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.