Re: Optimising SELECT on a table with one million rows

Cultural Sublimation wrote:
> CREATE TABLE users
>         (
>         user_id                 int UNIQUE NOT NULL,
>         user_name               text,
>         PRIMARY KEY (user_id)
>         );
>
>
> CREATE TABLE stories
>         (
>         story_id                int UNIQUE NOT NULL,
>         story_title             text,
>         story_body              text,
>         story_timestamp         timestamptz,
>         story_author            int REFERENCES users (user_id) NOT NULL,
>         PRIMARY KEY (story_id)
>         );
>
>
> CREATE TABLE comments
>         (
>         comment_id              int UNIQUE NOT NULL,
>         comment_title           text,
>         comment_body            text,
>         comment_timestamp       timestamptz,
>         comment_story           int REFERENCES stories (story_id) NOT NULL,
>         comment_author          int REFERENCES users (user_id) NOT NULL,
>         PRIMARY KEY (comment_id)
>         );
>
>
> I've also populated the database with some test data, comprising 1,000
> users, 1,000 stories (one per user), and 1,000,000 comments (one comment
> per user per story).
>
> Now, the query I wish to optimise is also simple: get me all comments (the
> comment_id suffices) and corresponding user *names* for a given story.
> If for example the story_id is 100, the query looks like this:
>
> SELECT comments.comment_id, users.user_name
> FROM comments, users
> WHERE comments.comment_story = 100 AND comments.comment_author = users.user_id;
>
> The problem is that this query takes a *very* long time.  With the said
> 1,000,000 comments, it needs at least 1100ms on my system.  "Explain
> analyze" tells me that a sequential scan is being performed on both
> users and comments:

What else is it supposed to do? You haven't created any indexes. I'm
also guessing that you haven't analysed the tables either.

--
   Richard Huxton
   Archonet Ltd