Question on partial keys and an index on a database table

Question

Suppose I have two queries on a database table.

The queries are defined in terms of fields used in the query:

Query1: depends on f1, f2, and f3

Query2: depends on f1, f2, f3 and f4

I remember reading somewhere that the SQL query engine (mySQL in this case) parses the index tree starting from the leftmost fields in the index.

If that is correct, then I assume that instead of having two indices defined on the table like this:

Index 1 (for Query1) : CREATE INDEX idx_1 {f1, f2, f3}
Index 2 (for Query2) : CREATE INDEX idx_2 {f1, f2, f3, f4}

I can simply define one index which contains the union of the keys used in both queries - i.e.

I only need to define this index:

(for BOTH Query1) : CREATE INDEX the_idx {f1, f2, f3, f4}

I have two questions:

1. Is my assumption correct?. i.e. can I simply define the one index (the_idx) instead of the previous two?

2. Does this index behavior hold true for PostgreSQL query engine as well?

Answer 1

in general the more populated index will be useable. however the more you add in to that index, the more overhead it will require.

the best thing would be to try it, and look at the execution plan to see if it is used in the manner you expect.

depending on the actual columns returned in the result set, it may be more beneficial to use a shorter index.

Answer 2

The MySQL manual makes it reasonably clear that yes, any "prefix" of a key can be searched in any non-hash index (which is most of them).

I couldn't find any comparable documentation for PostgreSQL, but you could always create the table and then do an EXPLAIN (not a bad idea anyway).

Answer 3

Is my assumption correct?. i.e. can I simply define the one index (the_idx) instead of the previous two?

Yes.
It's called a covering index, and you want to order the columns based on which is most likely to be used queries. IE: If f2 is the most common column, you'd want to use:

CREATE INDEX the_idx {f2, f1, f3, f4}

Does this index behavior hold true for PostgreSQL query engine as well?

No, Postgres does not support covering indexes.

Indexes are not ANSI standard; it's a miracle the terminology is so consistent between vendors as it is.