Recent versions of PostgreSQL seem to prefer 2d indexes somehow:
for a table "foo" with
"i_a" btree (a)
"i_ab" btree (a, b)
SELECT * FROM foo WHERE a=123
will often use "i_ab" and not "i_a" (even right after ANALYZE). This
raises some questions:
- is there even any benefit in still having both these indexes? (can
some operations still use "i_a" only or is "i_ab" always a sufficient
replacement for "i_a"?)
- is this even working as intended? in my experience (can't back it up
with numbers atm.), 2-dimensional indexes are often slower and they
degrade noticeably over time. Without knowing the implementation, I'd
assume that using "i_ab" would usually require more page fetches than
using "i_a" for the above query.
Regards,
Marinos