0

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?

Improve this question
3
  • 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.
    – dezso
    May 24, 2016 at 12:09
  • 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.
    – P.Péter
    May 24, 2016 at 12:42
  • wiki.postgresql.org/wiki/SlowQueryQuestions
    – user1822
    May 31, 2016 at 13:25

1 Answer 1

Reset to default
0

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.

Improve this answer
1
  • Which you can easily detect by looking at the output of explain (analyze, buffers)
    – user1822
    May 31, 2016 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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