router_issue.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
-- but router planner fails to push down the query
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;
		

-- this time router planner recognise this query and creates a distributed router plan
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;