Postgresql – potgresql foreign key index unused for some columns on one specific server

indexpostgresql

I have a table publication, with two columns type_id and sub_type_id (there are, of course, more columns, but they are not important) on a server, all containing int8 foreign keys to tables type and sub_type, respectively.

My problem is that while indices are present for both columns, they are not used for sub_type_id, so from the below SQL queries:

EXPLAIN ANALYZE SELECT * FROM publication WHERE type_id=1068114;
EXPLAIN ANALYZE SELECT * FROM publication WHERE sub_type_id=1068114;

The first shows an index scan, the second shows a sequential scan. This is even true if I set enable_seqscan=off.

What is even more frustrating is that doing the same on my local server, both SELECTs use indices (both running 9.4 postgres on debian).

There is obviously a difference in either the indices or the columns on the server, but I do not know what. How can I find out why one index is used, and the other is not?

Best Answer

It seems that the table publication needed a VACUUM FULL. After freeing up enough space for that and running it, new indices seem to work now.

Related Question