Postgres Is Ignoring A Timestamp Index, Why?

I have the following tables: users (id, network_id) networks (id) private_messages (id, sender_id, receiver_id, created_at) I have indexes on users.network_id, and all 3 columns i

Solution 1:

Let's try something different. I am only suggesting this as an "answer" because of its length and you cannot format a comment. Let's approach the query modularly as a series of subsets that need to get intersected. Let's see how long it takes each of these to execute (please report). Substitute your timestamps for t1 and t2. Note how each query builds upon the prior one, making the prior one an "inline view".

EDIT: also, please confirm the columns in the Networks table.

1

select PM.receiver_id from private_messages PM
 where PM.create_at between (t1 and t2)

2

select U.id, U.network_id from users U
 join
 (select PM.receiver_id from private_messages PM 
   where PM.create_at between (t1 and t2)
 ) as FOO
 on U.id = FOO.receiver_id

3

select N.* from networks N
join
(select U.id, U.network_id from users U
 join
 (
   select PM.receiver_id from private_messages PM 
   where PM.create_at between (t1 and t2)
 ) as FOO
 on U.id = FOO.receiver_id
) as BAR
on N.id = BAR.network_id

Solution 2:

First, I think you want an index on network.created_at, even though right now with over 10% of the table matching the WHERE, it probably won't be used.

Next, I expect you will get better speed if you try to get as much logic as possible into one query, instead of splitting some into a subquery. I believe the plan is indicating iterating over each value of network.id that matches; usually an all-at-once join works better.

I think the code below is logically equivalent. If not, close.

SELECTCOUNT(*) 
FROM 
 (SELECT users.network_id FROM "networks"
  JOIN users 
  ON users.network_id = networks.id
  JOIN private_messages
  ON private_messages.receiver_id = users.id
   AND (private_messages.created_at 
    BETWEEN ((timestamp'2013-03-01')) 
         AND (( (timestamp'2013-03-31') +interval'-1 second')))
  WHERE 
   networks.created_at 
    BETWEEN ((timestamp'2013-01-01')) 
     AND (( (timestamp'2013-01-31') +interval'-1 second'))
  GROUPBY users.network_id)
     AS main_subquery  
;

My experience is that you will get the same query plan if you move the networks.created_at into the ON clause for the users-networks join. I don't think your issue is timestamps; it's the structure of the query. You may also get a better (or worse) plan by replacing the GROUP BY in the subquery with SELECT DISTINCT users.network_id.