Re: Optimising SELECT on a table with one million rows

Cultural Sublimation skrev:
> Hi,
>
> I'm fairly new with Postgresql, so I am not sure if the performance
> problems I'm having are due to poorly constructed queries/indices,
> or if I bumped into more fundamental problems requiring a design of
> my database structure.  That's why I'm requesting your help.
>
> Here's the situation: I have three tables: Users, Stories, and Comments.
> Stories have an author (a user), and a comment is associated with a
> story and with the user who posted it.  The structure of the database
> is therefore fairly simple:  (there are also some sequences, which I've
> omitted for clarity)
>
>
> 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)
>         );

You need indices on comment.comment_story (and probably later for
comment_author). You should ALWAYS add an index on a FOREIGN KEY column
unless you have a very good reason not to. So:

CREATE INDEX comments_story_idx ON comments(comment_story);
CREATE INDEX comments_author_idx ON comments(comment_author);
CREATE INDEX story_author_idx ON story(story_author);

Thge first of these should remove the need for a seqscan on comments for
your query. The seqscan on users is not a problem - you are returning
data from all the rows, so a seqscan is the smart thing to do.

Nis