Menu
With PostgreSQL, I want to use a compound index on three columns A, B, C. B is the created_at datetime, and occasionally I might query without B.
What happens if I compound index on (A, B, C) but then query with conditions on A and C, but not B? (That is, A and C but want it over all time, not just some specific time range?)
Is Postgres smart enough to still use the (A, B, C) compound index but just skip B?
425
So the order of columns in a multi-column index definitely matters. One type of query may need a certain column order for the index. If you have several types of queries, you might need several indexes to help them, with columns in different orders.
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
MongoDB can use the intersection of indexes to fulfill queries. For queries that specify compound query conditions, if one index can fulfill a part of a query condition, and another index can fulfill another part of the query condition, then MongoDB can use the intersection of the two indexes to fulfill the query.
You can create composite indexes using CREATE INDEX or ALTER TABLE. An SQL GUI tool can also be used. When creating one, think of what column is mostly used in the query and make it the first column in the key. Finally, if you need to cover other columns other than for searches and lookups, use included columns.
Postgres can use non-leading columns in a b-tree index, but in a far less efficient mode.
If the first column is very selective (only few rows per A) then you will hardly notice a difference in performance since either access method (even a sequential scan over the reduced set) is cheap. The performance hit grows with the number of rows per A.
For the case you describe I suggest to create the index on (A, C, B) or (C, A, B) (just make sure that B comes last) to optimize performance. This way you get best performance for queries on (A, B, C) and on (A, C) alike.
Unlike the sequence of columns in the index, the sequence of predicates in the query does not matter.
We have discussed this in great detail on dba.SE:
Note that it does not matter whether you lead with A, C or C, A for the case at hand:
There are also some other considerations, but your question does not have all the relevant details.
89
Yes it is.
I did a quick check by doing an EXPLAIN of a query that had a condition for the first and third column of an index. It did output that it would do a Bitmap Index Scan on that index and mentioned both the first and the third column in the index condition.
(Tested on 9.3.5)
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
