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