Query Failure on Rails Postgresql Database

Question

I have experienced an interesting issue with postgresql indexes in my application, that uses Rails 4 and Postgresql. Basically, I have a project table and an invitations table.

Invitation has a foreign key project_id and a foreign key user_id.

I have a project with the id number 770,

  Invitation.find(761816).project_id # 770
  Invitation.find(761816).user_id # 389

However, the query that searches for this project based on user_id fails.

  Invitation.where(project_id: 770, user_id: 389) #<ActiveRecord::Relation []>

Of course, Invitation belongs_to User, belongs_to Project and User has_many Invitations. The same query works for any other invitation object I could find.

These attributes on the invitation table are indexed, which could be the reason for this behavior:

  add_index :invitations, :user_id
  add_index :invitations, :project_id

This is the first time I experienced this issue in a pretty large database that has been operating for more than a year now.

I would appreciate it if someone could give me some advice on why this is happening and what could be done to fix this problem, or how postgres indexes can be rebuilt in case there is a reason to rebuild them.

Update

Here are the results of queries I have been asked to execute:

Invitation.count # 787322
Invitation.first.inspect # "#<Invitation id: 13, ... user_id: 358, ... project_id: 14, status: 5, followup_status: 0 ...>"
Invitation.first.project_id.class.name # Fixnum
Invitation.first.user_id.class.name # Fixnum
Invitation.where(project_id:770).count # 353
Invitation.where(user_id:389).count # 775
Invitation.where(project_id:770,user_id:389).count # 0

and the most interesting part:

Invitation.where(project_id:770,user_id:389,id:761816).count # 1

Update 2

Here are the explain statements of these two problem queries:

Invitation.where(project_id:770,user_id:389)

-------------------------------
Bitmap Heap Scan on invitations  (cost=45.18..49.20 rows=1 width=192)
Recheck Cond: ((user_id = 389) AND (project_id = 770))
->  BitmapAnd  (cost=45.18..45.18 rows=1 width=0)
     ->  Bitmap Index Scan on index_invitations_on_user_id  (cost=0.00..22.36 rows=792 width=0)
           Index Cond: (user_id = 389)
     ->  Bitmap Index Scan on index_invitations_on_project_id  (cost=0.00..22.57 rows=819 width=0)
           Index Cond: (project_id = 770)
(7 rows)

and the second correct one:

Invitation.where(project_id:770,user_id:389,id:761816)

----------------------
Index Scan using invitations_pkey on invitations  (cost=0.42..8.45 rows=1 width=192)
Index Cond: (id = 761816)
Filter: ((project_id = 770) AND (user_id = 389))
(3 rows)

Answer 1

Let's log our results here.

Step 1.

Invitation.find 761816 # => #<Invitation id: 761816, ... user_id: 389, ... project_id: 770, status: 6, ....>
Invitation.where(project_id: 770, user_id: 389) # => #<ActiveRecord::Relation []>

Step 2. Explains

Invitation.where(project_id: 770, user_id: 389).explain

gives

Bitmap Heap Scan on invitations (cost=45.93..49.94 rows=1 width=193)
Recheck Cond: ((project_id = 770) AND (user_id = 389))
-> BitmapAnd (cost=45.93..45.93 rows=1 width=0)
-> Bitmap Index Scan on index_invitations_on_project_id (cost=0.00..22.72 rows=839 width=0)
Index Cond: (project_id = 770)
-> Bitmap Index Scan on index_invitations_on_user_id (cost=0.00..22.96 rows=871 width=0)
Index Cond: (user_id = 389)
(7 rows)

and

Invitation.where(id: 761816).explain

gives

Index Scan using invitations_pkey on invitations (cost=0.42..8.44 rows=1 width=193)
Index Cond: (id = 761816)
(2 rows)

Step 3. Different queries

Can you please give results for these queries?

Invitation.count
Invitation.first.inspect
Invitation.first.project_id.class.name
Invitation.first.user_id.class.name
Invitation.where(project_id:770).count
Invitation.where(user_id:389).count
Invitation.where(project_id:770,user_id:389).count

Step 4. Different checks

Just to be sure, can we check the following?

1. Compare LC_COLLATE setting for production/dev machine (low chance, it affecting anything, but still interesting to know)
2. Run step #3 queries in database. We will at least know is the problem is related to Rails or Postgres.
3. Is it possible to drop (and recreate later) indexes in production database? Just to see how query behaves when indexes were removed.

Answer 2

If it is a problem with a corrupt index then it would be easy to diagnose -- drop the indexes and retry the query.

I assume that the generated query is correct?

It doesn't look like a very natural way to be using Rails, btw. Maybe:

@user.projects.find_by(:id => 770)