%ex1.5

\begin{enumerate}[label=(\alph*)]
% (a)
\item
Optimized query:
\begin{verbatim}
SELECT distinct(displayname) FROM posts p
    JOIN users u ON u.id = p.owneruserid
    WHERE p.viewcount > u.views;
\end{verbatim}

Improvements:\\
Just converted the selection to a JOIN

Original version:
\begin{verbatim}
 Planning time: 0.978 ms
 Execution time: 86179.847 ms
\end{verbatim}

Optimized version:
\begin{verbatim}
 Planning time: 0.960 ms
 Execution time: 37.354 ms
\end{verbatim}

% (b)
\item
Optimized query:
\begin{verbatim}
SELECT score FROM comments WHERE text ILIKE 'yes\%';
\end{verbatim}

Original version:
\begin{verbatim}
 Planning time: 0.540 ms
 Execution time: 60.946 ms
\end{verbatim}

Optimized version:
\begin{verbatim}
TODO, current query is slower..
\end{verbatim}

% (b)
\item
Optimized query:
\begin{verbatim}
SELECT DISTINCT postid FROM votes WHERE postid NOT IN (
    SELECT postid FROM votes WHERE votetypeid != 2
);
\end{verbatim}

Improvements:\\
Instead of doing about twelve million Seq Scans this is now reduces it to two Seq Scans by inverting the condition.

Original version:
\begin{verbatim}
Did not terminate.
On a different machine with SSD and no time limit it took 16 minutes.
\end{verbatim}

Optimized version:
\begin{verbatim}
 Planning time: 1.093 ms
 Execution time: 81.776 ms
\end{verbatim}

% (d)
\item
Optimized query:
\begin{verbatim}
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');
\end{verbatim}

Original version:
\begin{verbatim}
 Planning time: 4.084 ms
 Execution time: 4006.044 ms
\end{verbatim}

Optimized version:
\begin{verbatim}
 Planning time: 2.954 ms
 Execution time: 3081.461 ms
\end{verbatim}
\end{enumerate}
