3 \begin{enumerate}[label=(\alph*)]
8 SELECT distinct(displayname) FROM posts p
9 JOIN users u ON u.id = p.owneruserid
10 WHERE p.viewcount > u.views;
14 Just converted the selection to a JOIN
18 Planning time: 0.978 ms
19 Execution time: 86179.847 ms
24 Planning time: 0.960 ms
25 Execution time: 37.354 ms
32 SELECT score FROM comments WHERE text ILIKE 'yes\%';
37 Planning time: 0.540 ms
38 Execution time: 60.946 ms
43 TODO, current query is slower..
50 SELECT DISTINCT postid FROM votes WHERE postid NOT IN (
51 SELECT postid FROM votes WHERE votetypeid != 2
56 Instead of doing about twelve million Seq Scans this is now reduces it to two Seq Scans by inverting the condition.
61 On a different machine with SSD and no time limit it took 16 minutes.
66 Planning time: 1.093 ms
67 Execution time: 81.776 ms
74 SELECT p.*, c.*, u.* FROM posts p
75 JOIN comments c ON p.id = c.postid
76 JOIN users u ON p.owneruserid = u.id
77 JOIN badges b on u.id = b.userid
78 WHERE u.upvotes+3 >= (
79 SELECT AVG(upvotes) FROM users
80 WHERE u.creationdate > c.creationdate
82 SELECT 1 FROM postlinks l
83 WHERE l.relatedpostid > p.id
84 ) AND b.name IN ('Autobiographer','Supporter');
89 Planning time: 4.084 ms
90 Execution time: 4006.044 ms
95 Planning time: 2.954 ms
96 Execution time: 3081.461 ms