Re: Compound Indexes

"Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com> writes:

>> > 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 ;

Well I would start with testing:

trades(id, c_id, s_id)
trades(id, s_id, created_on)

However you may (and actually probably will, i expect) find that the third
column is not helping. That's especially true if the result of matching id and
either c_id or s_id is always a small enough set of records that sorting them
is quick (I would expect the point where an extra column in the index would
start to save you anything to be somewhere around 100-1,000, possibly even as
much as 10,000 or more).

Note that in released versions getting an index which is useful for ORDER BY
created_on *DESC* is actually quite difficult. So unless these queries are
returning thousands of records I would suggest ignoring the ORDER BY clauses
and just looking at the WHERE clauses.

If id,s_id and id,c_id are selective enough to return only a few records I
would actually expect you to end up with just

trades(id, s_id)
trades(id, c_id)

You might also be able to build some kind of index to help the ~* clause. If
you do a lot of queries like that and the id,s_id restriction isn't very
selective you might look into tsearch2 which can index that type of query.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com