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

Question

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?

• András Váczi: How many rows do you have in each table, and how many different values for type_id and sub_type_id? Also, the actual table definition could help.
• P.Péter: Different values: type_id: 10, sub_type_id: 91, publication has about 6M rows; but that should not matter, as enable_seqscan was turned off, so sequential scan is used only if postgres sees no other option. As for the actual table definition, it has 133 columns, which would be an overkill to post, but I will try to extract the relevant parts.
• user1822: wiki.postgresql.org/wiki/SlowQueryQuestions

Answer 1

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.

• user1822: Which you can easily detect by looking at the output of explain (analyze, buffers)