heg3SH6V

1DROP TABLE IF EXISTS Shards, FooBar, FooBarAgg;
2
3CREATE TABLE Shards (
4ShardID integer NOT NULL,
5Running boolean NOT NULL DEFAULT FALSE,
6PRIMARY KEY (ShardID)
7);
8
9INSERT INTO Shards (ShardID) SELECT generate_series(0,6);
10
11CREATE TABLE FooBar (
12FooID bigint NOT NULL,
13BarID bigint NOT NULL,
14ShardID integer NOT NULL GENERATED ALWAYS AS (ABS(hashint8(FooID)%7)) STORED,
15PRIMARY KEY (FooID, BarID)
16);
17
18CREATE TABLE FooBarAgg (
19FooID bigint NOT NULL,
20BarCount bigint NOT NULL,
21PRIMARY KEY (FooID)
22);
23
24INSERT INTO FooBar (FooID, BarID) SELECT FooID, 1 FROM generate_series(1,10000000) AS FooID;
25INSERT INTO FooBar (FooID, BarID) SELECT FooID, 2 FROM generate_series(1,10000000) AS FooID;
26
27CREATE INDEX ON FooBar(ShardID);
28ANALYZE FooBar;
29
30/*
31
32echo ; for shardid in `seq 0 6` ; do time psql -c "INSERT INTO FooBarAgg (FooID, BarCount) SELECT FooID, COUNT(*) FROM FooBar WHERE ShardID = $shardid GROUP BY FooID" & done
33
34*/
35
36/*
37
38Query to verify correctness, expected COUNT(*)=0:
39
40SELECT COUNT(*) FROM (
41  SELECT * FROM (
42    SELECT FooID, COUNT(*) FROM FooBar GROUP BY FooID
43    EXCEPT
44    SELECT FooID, BarCount FROM FooBarAgg GROUP BY FooID
45  ) AS X
46  UNION ALL
47  SELECT * FROM (
48    SELECT FooID, BarCount FROM FooBarAgg GROUP BY FooID
49    EXCEPT
50    SELECT FooID, COUNT(*) FROM FooBar GROUP BY FooID
51  ) AS Y
52) AS Z
53;
54
55*/
56