Search Postgresql Archives

Compound Indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux