\set date '\'2010-02-12\''::timestamp -- materialize=> \i q04.sql -- CREATE MATERIALIZED VIEW -- Time: 595.780 ms -- CREATE INDEX -- Time: 523.990 ms -- person.id | person.firstName | person.lastName | creationdate | messagecount -- ----------------+------------------+-----------------+----------------------------+-------------- -- 4398046519825 | Ahmad Rafiq | Akbar | 2010-05-23 13:24:43.393+00 | 1195 -- 6597069774064 | Ayesha | Ahmed | 2010-08-25 11:58:50.853+00 | 948 -- 15393162797132 | Shweta | Kapoor | 2011-04-17 02:52:48.739+00 | 835 -- 2199023265231 | Wei | Wang | 2010-04-21 01:27:43.705+00 | 738 -- 24189255819865 | Sombat | Amarttayakul | 2011-11-12 21:04:25.195+00 | 435 -- 2199023260815 | Chen | Li | 2010-04-27 14:01:48.888+00 | 402 -- 10995116284819 | John | Khan | 2010-12-24 17:41:09.731+00 | 382 -- 2866 | Eve-Mary Thai | Hoang | 2010-02-05 03:07:01.196+00 | 345 -- 6597069770739 | Abdul Haris | Gallagher | 2010-08-29 01:13:55.834+00 | 292 -- 28587302329901 | Shweta | Khan | 2012-03-30 09:33:32.228+00 | 261 -- 15393162799092 | Ge | Wang | 2011-03-25 19:48:01.039+00 | 151 -- 32985348840714 | John | Singh | 2012-07-23 00:06:08.096+00 | 64 -- 10995116279311 | Abhishek | Kumar | 2010-12-04 19:27:01.714+00 | 54 -- 28587302327043 | A. | Kumar | 2012-04-19 07:03:33.413+00 | 48 -- 32985348841684 | Jie | Zhu | 2012-09-02 10:37:26.12+00 | 48 -- 4398046518418 | Yang | Li | 2010-05-23 19:28:52.655+00 | 46 -- 10995116285596 | Taufik | Budjana | 2010-12-25 15:16:21.391+00 | 46 -- 26388279068970 | Amit | Reddy | 2012-02-08 20:18:06.166+00 | 46 -- 8796093028172 | Karan | Khan | 2010-10-10 03:24:24.86+00 | 44 -- 28587302328636 | Anh | Ha | 2012-04-12 19:20:15.578+00 | 44 -- 28587302329156 | A. | Singh | 2012-04-24 09:01:46.435+00 | 44 -- 6597069775034 | Abdul Jamil | Qureshi | 2010-08-26 20:49:21.329+00 | 43 -- 19791209304987 | John | Singh | 2011-08-09 05:18:25.45+00 | 43 -- 26388279071390 | Rahul | Khan | 2012-01-21 02:24:23.843+00 | 43 -- 32985348843490 | Yang | Li | 2012-07-12 22:09:43.58+00 | 42 -- 7937 | Rahul | Kumar | 2010-01-23 14:07:41.044+00 | 41 -- 2199023261398 | Nguyen Huu | Nguyen | 2010-03-07 00:09:13.471+00 | 41 -- 26388279074760 | A. | Nair | 2012-02-22 23:18:47.392+00 | 41 -- 6597069768174 | Jun | He | 2010-08-06 22:04:49.717+00 | 40 -- 24189255820986 | Bichang | Zhang | 2011-11-17 20:02:46.129+00 | 38 -- 13194139541713 | Deepak | Singh | 2011-01-23 15:36:38.709+00 | 37 -- 17592186048064 | V. | Joshi | 2011-05-13 06:34:40.153+00 | 37 -- 21990232556039 | Naresh | Kumar | 2011-10-26 19:55:13.777+00 | 37 -- 21990232559111 | Karan | Sharma | 2011-10-12 23:23:49.411+00 | 37 -- 30786325582458 | R. | Kapoor | 2012-07-05 05:01:47.042+00 | 37 -- 4398046512214 | Zheng | Li | 2010-06-22 01:39:52.845+00 | 36 -- 6597069773793 | Jun | Wang | 2010-07-17 12:38:41.01+00 | 36 -- 15393162788923 | Rahul | Sharma | 2011-03-10 15:49:01.365+00 | 36 -- 17592186054998 | Manuel | Cosio | 2011-05-10 02:03:12.983+00 | 36 -- 32985348841739 | Anupam | Rao | 2012-08-24 03:56:50.79+00 | 36 -- 2208 | Shweta | Sharma | 2010-01-16 16:40:35.139+00 | 35 -- 2199023263560 | Wuttichai | Boy | 2010-03-28 18:55:23.334+00 | 35 -- 6597069768442 | Abhishek | Kapoor | 2010-09-01 18:11:31.748+00 | 35 -- 17592186051684 | Abhishek | Singh | 2011-07-02 11:05:24.354+00 | 35 -- 19791209309552 | Abdul Haris | Anwar | 2011-07-27 16:59:54.637+00 | 35 -- 26388279073421 | Mirza Kalich | Ahmed | 2012-02-16 05:07:07.084+00 | 35 -- 28587302327725 | Aama | Shrestha | 2012-04-09 00:22:34.226+00 | 35 -- 30786325580960 | Duleep | Banda | 2012-05-15 15:04:55.025+00 | 35 -- 30786325581241 | Jean | Arnaud | 2012-05-21 14:14:11.353+00 | 35 -- 15393162798428 | Chen | Zhang | 2011-03-12 01:23:21.165+00 | 34 -- 17592186051872 | John | Sharma | 2011-06-11 07:51:02.867+00 | 34 -- 28587302329829 | Oleg | Chezhina | 2012-05-02 11:31:39.054+00 | 34 -- 5452 | Agustiar | Balawan | 2010-01-04 19:14:27.142+00 | 33 -- 2199023264344 | R. | Kapoor | 2010-04-30 05:24:51.797+00 | 33 -- 8796093023211 | Amir | Chen | 2010-10-31 01:03:17.379+00 | 33 -- 13194139542917 | Lei | Li | 2011-01-15 02:09:59.71+00 | 33 -- 19791209305377 | John | Singh | 2011-07-31 15:48:27.794+00 | 33 -- 19791209306643 | John | Sen | 2011-07-27 17:48:01.487+00 | 33 -- 35184372090954 | Arun | Chopra | 2012-10-25 02:12:07.425+00 | 33 -- 6597069771115 | Ami | Chatterjee | 2010-08-30 16:00:52.716+00 | 32 -- 6597069774644 | Zhang | Chen | 2010-08-02 13:39:20.452+00 | 32 -- 6597069777069 | Wei | Wang | 2010-08-06 01:42:36.282+00 | 32 -- 13194139543607 | A. | Khan | 2011-02-06 23:23:19.378+00 | 32 -- 21990232559935 | Meera | Kapoor | 2011-09-23 11:55:58.941+00 | 32 -- 21990232565605 | Prakash | Price | 2011-11-06 07:17:04.122+00 | 32 -- 26388279076723 | Deepak | Kumar | 2012-02-10 18:26:21.335+00 | 32 -- 28587302331910 | Isabel | Reyes | 2012-04-27 03:16:45.436+00 | 32 -- 30786325588075 | Anupam | Rao | 2012-06-23 10:16:17.317+00 | 32 -- 32985348843444 | Wei | Chen | 2012-07-30 09:52:43.39+00 | 32 -- 10281 | James | Raghu | 2010-01-13 12:20:03.403+00 | 31 -- 6597069768018 | Rahul | Singh | 2010-07-24 22:08:17.051+00 | 31 -- 13194139534180 | Arjun | Khan | 2011-01-27 17:05:31.134+00 | 31 -- 13194139539554 | K. | Sharma | 2011-01-17 09:20:04.865+00 | 31 -- 13194139543101 | Shweta | Kapoor | 2011-02-09 09:48:05.32+00 | 31 -- 19791209302082 | K. | Kumar | 2011-09-01 10:14:27.166+00 | 31 -- 19791209304789 | Amit | Rao | 2011-08-13 06:55:48.125+00 | 31 -- 26388279071096 | Prakash | Khan | 2012-02-01 00:18:37.691+00 | 31 -- 30786325582257 | Ashok | Sharma | 2012-05-12 07:08:40.547+00 | 31 -- 2435 | Bibit | Balawan | 2010-01-23 23:37:20.444+00 | 30 -- 2199023261346 | Peng | Li | 2010-04-28 01:12:38.381+00 | 30 -- 4398046517801 | Jun | Wang | 2010-06-01 17:59:47.303+00 | 30 -- 8796093029212 | Jie | Zhang | 2010-11-01 23:57:40.37+00 | 30 -- 15393162789525 | Albaye Papa | Faye | 2011-03-22 05:03:56.918+00 | 30 -- 21990232561857 | Paul | Clerc | 2011-10-22 00:12:31.924+00 | 30 -- 26388279076501 | Chen | Li | 2012-01-12 20:31:19.036+00 | 30 -- 28587302327643 | Deepak | Reddy | 2012-03-19 22:38:23.233+00 | 30 -- 30786325579031 | Amir | Lee | 2012-05-22 16:12:22.233+00 | 30 -- 30786325580998 | John | Singh | 2012-05-20 13:27:29.263+00 | 30 -- 30786325581320 | Deepak | Roy | 2012-05-17 11:23:42.909+00 | 30 -- 30786325583499 | Ashok | Reddy | 2012-05-18 16:26:08.414+00 | 30 -- 35184372094081 | Faisal | Malik | 2012-09-28 16:29:51.372+00 | 30 -- 3903 | Wei | Li | 2010-02-03 08:12:07.624+00 | 29 -- 2199023258884 | Peng | Chen | 2010-03-18 21:51:12.03+00 | 29 -- 6597069767028 | Grigore | Bologan | 2010-08-19 23:12:33.496+00 | 29 -- 6597069772286 | Arun | Rao | 2010-08-22 17:43:58.802+00 | 29 -- 6597069775770 | Pol | Nath | 2010-08-06 17:28:25.712+00 | 29 -- 8796093032553 | Priyanka | Singh | 2010-10-14 18:38:00.87+00 | 29 -- 10995116282430 | Batong | Tran | 2011-01-02 03:43:55.824+00 | 29 -- 10995116282865 | Ashok | Khan | 2010-11-26 06:29:03.99+00 | 29 -- 10995116286396 | Yang | Li | 2010-12-25 12:16:40.949+00 | 29 -- (100 rows) -- -- Time: 40116.090 ms (00:40.116) -- -- BEFORE REWRITING: -- materialize=> \i q04.sql -- CREATE MATERIALIZED VIEW -- Time: 791.930 ms -- CREATE INDEX -- Time: 349.982 ms -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "offline" at 2023-07-20 18:17:02.072258+00 -- HINT: The cluster replica may be restarting or going offline. -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "online" at 2023-07-20 18:17:07.113753+00 -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "offline" at 2023-07-20 22:19:42.544537+00 -- HINT: The cluster replica may be restarting or going offline. -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "online" at 2023-07-20 22:19:47.566627+00 -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "offline" at 2023-07-21 01:54:04.832211+00 -- HINT: The cluster replica may be restarting or going offline. -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "online" at 2023-07-21 01:54:09.850194+00 -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "offline" at 2023-07-21 05:49:35.058072+00 -- HINT: The cluster replica may be restarting or going offline. -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "online" at 2023-07-21 05:49:40.109144+00 -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "offline" at 2023-07-21 08:42:54.897804+00 -- HINT: The cluster replica may be restarting or going offline. -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "online" at 2023-07-21 08:42:59.928996+00 -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "offline" at 2023-07-21 12:02:54.517557+00 -- HINT: The cluster replica may be restarting or going offline. -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "online" at 2023-07-21 12:02:59.559505+00 -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "offline" at 2023-07-21 13:30:02.559734+00 -- HINT: The cluster replica may be restarting or going offline. -- psql:q04.sql:63: NOTICE: cluster replica default.r1 changed status to "online" at 2023-07-21 13:30:07.566586+00 -- ^CCancel request sent -- psql:q04.sql:63: ERROR: canceling statement due to user request -- Time: 93951684.641 ms (1 d 02:05:51.685) /* Q4. Top message creators by country \set date '\'2012-09-01\''::timestamp */ CREATE OR REPLACE MATERIALIZED VIEW Top100PopularForumsQ04 AS SELECT T.id AS id, Forum.creationdate AS creationDate, T.maxNumberOfMembers AS maxNumberOfMembers FROM (SELECT ForumId AS id, MAX(numberOfMembers) AS maxNumberOfMembers FROM (SELECT Forum_hasMember_Person.ForumId AS ForumId, count(Person.id) AS numberOfMembers, City.PartOfCountryId AS CountryId FROM Forum_hasMember_Person JOIN Person ON Person.id = Forum_hasMember_Person.PersonId JOIN City ON City.id = Person.LocationCityId GROUP BY City.PartOfCountryId, Forum_hasMember_Person.ForumId) ForumMembershipPerCountry GROUP BY ForumId) T, Forum WHERE T.id = Forum.id; CREATE INDEX Top100PopularForumsQ04_id ON Top100PopularForumsQ04 (id); WITH Top100_Popular_Forums AS ( SELECT id, creationDate, maxNumberOfMembers FROM Top100PopularForumsQ04 WHERE creationDate > :date ORDER BY maxNumberOfMembers DESC, id LIMIT 100 ), au AS ( SELECT * FROM Person WHERE EXISTS (SELECT 1 FROM Top100_Popular_Forums INNER JOIN Forum_hasMember_Person ON Forum_hasMember_Person.ForumId = Top100_Popular_Forums.id WHERE Forum_hasMember_Person.PersonId = Person.id) ), Top100_Message AS ( SELECT MessageId, CreatorPersonId FROM Message WHERE Message.ContainerForumId IN (SELECT id FROM Top100_Popular_Forums) ) SELECT au.id AS "person.id" , au.firstName AS "person.firstName" , au.lastName AS "person.lastName" , au.creationDate -- a single person might be member of more than 1 of the top100 forums, so their messages should be DISTINCT counted , COUNT(Top100_Message.MessageId) AS messageCount FROM au LEFT JOIN Top100_Message ON au.id = Top100_Message.CreatorPersonId GROUP BY au.id, au.firstName, au.lastName, au.creationDate ORDER BY messageCount DESC, au.id LIMIT 100 ;