Hi,
> If you have no index on comments.comment_author, then a seqscan will be
> required for your join between comments and users. Similarly, if you
> have no index on comments.comment_story, then any query against comments
> that uses that column as part of a predicate will require a seqscan of
> the comments table.
I see. As I said, I'm still fairly new to this...
> Note that an FK constraint does not automatically create an index on the
> underlying column. You need to create the actual index yourself if it
> will be necessary for your queries.
I see what you mean. The basic idea then is to take a look at the typical
queries and to create indices based on them. Is there a good guide on index
creation for optimisation purposes?
> Partitioning on comments.comment_timestamp won't help you at all for
> this particular query, since you don't have a condition in your query
> dependent upon that value. It might help you for other queries (such as
> gathering up all the comments posted on a particular day, or during some
> other time range), but it won't make any positive difference for this query.
You are right. Come to think of it, partitioning the comments table based
on comment_story might make more sense, since the overwhelming majority of
queries will be like the one I just mentioned: asking for all comments of
a given story.
Anyway, thanks a lot for your help! (And that goes for all the other people
who also given their 2 cents)
Best regards,
C.S.
____________________________________________________________________________________Ready for the edge of your seat?
Check out tonight's top picks on Yahoo! TV.
http://tv.yahoo.com/
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
