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?
type_id
andsub_type_id
? Also, the actual table definition could help.