3 \begin{enumerate}[label=(\alph*)]
8 SELECT distinct(displayname) FROM posts p JOIN users u ON u.id = p.owneruserid WHERE p.viewcount > u.views;
12 Just converted the selection to a JOIN
16 Planning time: 0.978 ms
17 Execution time: 86179.847 ms
22 Planning time: 0.960 ms
23 Execution time: 37.354 ms
30 SELECT score FROM comments WHERE text ILIKE 'yes\%'; <- did not work (100ms instead of 33ms)
35 Planning time: 0.540 ms
36 Execution time: 60.946 ms
41 TODO, current query is slower..
48 SELECT DISTINCT postid FROM votes WHERE postid NOT IN (SELECT postid FROM votes WHERE votetypeid != 2);
52 Instead of doing about twelve million Seq Scans this is now reduces it to two Seq Scans by inverting the condition.
56 Did not terminate. On a different machine with SSD and no time limit it took 16 minutes.
61 Planning time: 1.093 ms
62 Execution time: 81.776 ms
69 SELECT p.*, c.*, u.* FROM posts p JOIN comments c ON p.id = c.postid JOIN users u ON p.owneruserid = u.id JOIN badges b on u.id = b.userid WHERE u.upvotes+3 >= (SELECT AVG(upvotes) FROM users WHERE u.creationdate > c.creationdate) AND EXISTS (SELECT 1 FROM postlinks l WHERE l.relatedpostid > p.id) AND b.name IN ('Autobiographer','Supporter');
74 Planning time: 4.084 ms
75 Execution time: 4006.044 ms
80 Planning time: 2.954 ms
81 Execution time: 3081.461 ms