One example of a slow query.

At one site, I managed to verify that one of these two queries ran 10 to
100 times faster than the other:

select * from customers
where country = 'US' and
state = 'TX' and
City = 'Dallas'

select * from customers
where state = 'TX' and
City = 'Dallas'


I expected the first one to run much faster and it did. Why? Because
there was an index with a compound index key, namely country, state, and
city. In the second case the poor optimizer was faced with walking the
index instead of a quick lookup, as it could do in the first case.

But the clever programmers had used the second form, because they knew that
all the customers were in US, and they wanted to keep things simple for the
DBMS.

Not only were they running way too slow, but also, they had written code
that could break when the user community decided to extend the customer base
to other countries.

This is just one example out of hundreds of possible examples, where the
programmers code strangely in order to speed things up, and instead they
slow things down.

Arved Sandstrom, 25.06.2008 13:55:
How common is the latter implementation? MySQL evidently does this, so that
if you had an index on (col1,col2,col3), queries on (col1), (col1,col2) and
(col1,col2,col3) all use the index, but a query on (col2,col3), for example,
does not. What is this incomplete use of multicolumn indexes called,
exactly?

I think that this is related to the fact that most of the time the index is implemented as a B-Tree index. And technically it is not possible to access the "second level" directly without finding the "first level".

As I understand it, it wouldn't be totally useless to have only the separate
indexes for the three columns, namely (col1), (col2), and (col3), as the
DBMS might use index combination (as does PostgreSQL 8.1 and up). perhaps
even better, have the multicolumn index and one or more single-column
indexes, depending on what you anticipate the most common queries to be.

Yes and no. Keep in mind that each index will add an overhead when updating, deleting or inserting rows. So if you have a lot of indexes, your update performance may degrade. The "art" is finding the proper balance between the two requirements (fast reads and fast writes)

Postgres does a indeed good job in "re-using" indexes. For e.g. something similar could be achieved using bitmap indexes (but they have higher update costs and are not always feasible)

Thomas