Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?

Additionally, I created an index on A, but Postgres still uses the composite index for queries on only A. If the previous answer is positive, I guess it doesn't really matter, but why does it select the composite index by default, if the single A index is available?

  • I tried to set up a small test for this. In my case, however, the two-column index was used only when I dropped the single-column one, unrelated to which one was created first. It is interesting that if I created the two-column index first, the initial plan used a bitmap heap scan. If I created the one-column index, then run the query (used index scan) and dropped the newly created index, the plan involving the two-column index switched to index scan. See the steps on SQLFiddle – dezso Oct 23 '12 at 19:38
  • @dezso Interesting. Where are the costs for each query? – Luciano Oct 23 '12 at 20:05
  • Bitmap index scan cost: 107.98, 43 ms execution time. Index scan one-column: cost 8.69, two-column: 43.69. Execution times do not differ significantly (the fluctuation is bigger than the difference between the two). – dezso Oct 23 '12 at 20:10
  • @Luciano Can you show the explain analyze and the query text? – Craig Ringer Oct 23 '12 at 22:32

It certainly is. We discussed that in great detail under this related question:

Space is allocated in multiples of MAXALIGN, which is typically 8 bytes on a 64-bit OS or (less common) 4 bytes on a 32-bit OS. If you are not sure, check pg_controldata. It also depends on data types of indexed columns (some require alignment padding) and actual content.

An index on, say, two integer columns typically ends up to be exactly as big as an index on one. For just one integer (4 bytes) another 4 bytes of alignment padding are added.

In such a case there is really no downside for the query planner to use an index on (a,b) - compared to an index on just (a). And it is generally preferable for multiple queries to use the same index. The chance for it to reside in cache already (or parts of it) grows when shared.

If you already maintain an index on (a,b), then it doesn't make sense to create another index on just (a) - unless it is substantially smaller. The same is not true for (b,a) vs. (a). Follow the link in the first line for more on that.

On the other hand, there is a potential downside to including additional columns in an index, even if that only uses space otherwise lost to alignment padding. If an additional column is updated, the index needs an update, too. Also prevents potential HOT (Heap Only Tuple) updates on the table when this column is involved.

More on HOT updates:

How to measure object sizes:

According to your question you have a table with field A and B. If you your query is:

SELECT * FROM [YOUR TBL]
WHERE A='XXXX'

Optimizer will chose the Composite index to avoid Extract random access!

It is in the case if you just use just first in the predicate.

It will do scan if you use first columns of composite key and non-key column of composite key.

To trick it you can just dummy predicates like this and then non-key column:

[A,B] is your index, [C] - another column

To utilize index you write as:

SELECT
    A,B,C,D,E
FROM 
    test
WHERE
   A=1
AND
   B=B
AND 
   C=3

...why does it select the composite index by default, if the single A index is available?

It will use index only in the case if there are one or two predicates [A] Or [A],[B]. It will not use it in the order [B],[A] or [A],[C]. To be able to utilize index with additional column [C], you need to enforce index by ordering predicates as [A],[B] and [C].

  • 2
    What exactly do you achieve with B=B? I think you achieve nothing so I'm voting down absent any evidence this isn't just ignored by the optimizer – Jack Douglas Oct 24 '12 at 9:57
  • 2
    B=B is effectively the same as B IS NOT NULL, which seems uncalled for. Certainly not needed to use an index on (a,b). – Erwin Brandstetter May 27 '14 at 15:05

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.