I have a table with ten columns. My queries basically one column as the first WHERE condition, so an index on that column is certain. But the columns after that one vary depending on end-user's choice (this is a reporting application) and so does the sorting order. In MySQL world, I had sort_buffer in the config file, and I made a compound index with the columns most often used in these types of queries. So my index looked like: INDEX idx_trades(id, t_id, c_id, s_id, t_brief, created_on); This has five columns in it. While reading the pgsql documentation, I gather than anything beyond three columns offers diminishing benefits. My queries will look like these: SELECT * from trades where id = 99999 and c_id = 9999 ORDER by s_id; SELECT * from trades where id = 99999 and s_id = 99990 ORDER by created_on desc ; SELECT * from trades where id = 99999 and s_id = 99990 and t_brief ~* 'more|than|one|word' ORDER by created_on desc ; So my question: how does PGSQL optimize its sorts? If I were to index merely the columns that are most commonly used in the reporting WHERE clause, would that be ok? Some ofthese columns may be "TEXT" type -- how should I include these in the index (in MySQL, I included only the first 100 words in the index). TIA! ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
