Improve router planner coverage for queries which involve range filter
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
We've recently enabled router planner to support range partitioned tables with #1272 . That led the opportunity of range filter on the query hit to a single shard (i.e., this issue does not apply to hash partitioned tables).
In plain words, the issue is that range filters on the partition keys are not distributed to the joining tables.
The issue is related to #1204. Without this, we may generate unnecassary worker queries in some cases.
```SQL
CREATE TABLE events (
user_id int,
event_id bigint,
event_type character varying(255),
event_time bigint
);
SELECT master_create_distributed_table('events', 'user_id', 'range');
SELECT master_create_empty_shard('events') AS new_shard_id
\gset
UPDATE pg_dist_shard SET shardminvalue = 1, shardmaxvalue = 100
WHERE shardid = :new_shard_id;
SELECT master_create_empty_shard('events') AS new_shard_id
\gset
UPDATE pg_dist_shard SET shardminvalue = 101, shardmaxvalue = 200
WHERE shardid = :new_shard_id;
\COPY events FROM STDIN WITH CSV
10,20001,click,1472807012
10,20002,submit,1472807015
10,20003,pay,1472807020
11,20010,click,1472807022
12,20011,click,1472807023
13,20012,submit,1472807025
15,20013,pay,1472807030
110,20014,click,1472807032
111,20015,click,1472807033
111,20016,click,1472807034
111,20017,submit,1472807035
\.
CREATE TABLE users (
user_id int,
lastseen bigint
);
SELECT master_create_distributed_table('users', 'user_id', 'range');
SELECT master_create_empty_shard('users') AS new_shard_id
\gset
UPDATE pg_dist_shard SET shardminvalue = 1, shardmaxvalue = 100
WHERE shardid = :new_shard_id;
SELECT master_create_empty_shard('users') AS new_shard_id
\gset
UPDATE pg_dist_shard SET shardminvalue = 101, shardmaxvalue = 200
WHERE shardid = :new_shard_id;
\COPY users FROM STDIN WITH CSV
10,1472807115
11,1472807215
111,1472807315
\.
-- the query hits single shard
-- and router plannable since the filter on user_id on the users
-- table is equality (users.user_id = 5)
SELECT
user_id,
array_agg(event_type) AS events
FROM
(SELECT
users.user_id,
event_type,
events.event_time
FROM
users,
events
WHERE
(users.user_id) = (events.user_id) AND
users.user_id = 5 AND
event_type IN ('click', 'submit', 'pay')) AS subquery
GROUP BY
user_id;
-- the query hits single shard
-- but not router plannable since the filter on user_id on the users
-- table is a range (users.user_id >= 5 AND users.user_id <= 50)
SELECT
user_id,
array_agg(event_type) AS events
FROM
(SELECT
users.user_id,
event_type,
events.event_time
FROM
users,
events
WHERE
(users.user_id) = (events.user_id) AND
users.user_id >= 5 AND
users.user_id <= 50 AND
event_type IN ('click', 'submit', 'pay')) AS subquery
GROUP BY
user_id;
-- the query hits single shard
-- and router plannable since the both tables have the range filters
-- (users.user_id >= 5 AND users.user_id <= 50) AND
-- (events.user_id >= 5 AND events.user_id <= 50)
SELECT
user_id,
array_agg(event_type) AS events
FROM
(SELECT
users.user_id,
event_type,
events.event_time
FROM
users,
events
WHERE
(users.user_id) = (events.user_id) AND
users.user_id >= 5 AND
users.user_id <= 50 AND
events.user_id >= 5 AND
events.user_id <= 50 AND
event_type IN ('click', 'submit', 'pay')) AS subquery
GROUP BY
user_id;
```
(@anarazel mentioned a solution but I barely remember. I'll update the issue later on)
Contributor guide
Assessment
This issue has not been assessed yet.