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!
