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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
