Forum OpenACS Q&A: Response to "vacuum analyse" messing up the row ordering?
5: Response to "vacuum analyse" messing up the row ordering? (response to 1)
Posted by Jonathan Marsden on 05/03/01 06:14 PM
Don is the expert on this stuff around here; I'd say that since your query uses a where clause involving game_id = 9, having an index on that game_id field would probably make more difference as the table grows than whatever you order on -- the number of moves per game will presumably stay within reasonably small bounds, so ordering moves from a single game isn't likely to be as expensive as simply finding all the moves from a game in the first place from a table containing thousands or tens of thousands of games.
Once you get comfortable with SQL itself, you can use the EXPLAIN command to get you information on how PostgreSQL will plan to execute your queries, and then create an index, or rewrite the query, or whatever, to improve performance.
But getting the thing working right should probably take precedence over worrying about how fast it will go.
One place to start learning more about SQL's approach to life would be Phil Greenspun's online book SQL for Web Nerds.