title: Index not used in Postgresql read-replica. Why?
body: <p>I have a relatively large table on an Amazon RDS production database (in the range of 2M records). I wish to group on a number of fields, including the month of a date (server_time) in the table. To speed things up a bit, I have created an index on the master database as follows:</p>
  
  <pre><code>create index on build_requests(group_id, artifact_id, account_id, number_of_interfaces, date_trunc('month', server_build_time));
  </code></pre>
  
  <p>Then, as you would expect, the query to group the data uses the index on the master:</p>
  
  <pre><code>GroupAggregate  (cost=0.55..311308.09 rows=1633231 width=85)
    Group Key: group_id, artifact_id, account_id, number_of_interfaces, date_trunc('month'::text, server_build_time)
    -&gt;  Index Scan using build_requests_group_id_artifact_id_account_id_number_of_in_idx on build_requests  (cost=0.55..262417.68 rows=1898335 width=85)
  </code></pre>
  
  <p>However, after waiting for over an hour, the read replica still does not use the index:</p>
  
  <pre><code>GroupAggregate  (cost=434678.88..488313.41 rows=1633179 width=85)
    Group Key: group_id, artifact_id, account_id, number_of_interfaces, (date_trunc('month'::text, server_build_time))
    -&gt;  Sort  (cost=434678.88..439424.56 rows=1898274 width=85)
          Sort Key: group_id, artifact_id, account_id, number_of_interfaces, (date_trunc('month'::text, server_build_time))
          -&gt;  Seq Scan on build_requests  (cost=0.00..55053.43 rows=1898274 width=85)
  </code></pre>
  
  <p>Logging into the read replica with pgadmin, I see the index is present however. This is a problem, since the slow speed of the query on the read replica (5 minutes vs 3 seconds), causes other queries that include this query through postgres_fdw (cross database query) to return ssl connection resets (probably timeouts?).</p>
  
  <p>Any idea why the read replica does not pick up/use the index I define on the master, and how I can remedy this? The query I execute on both master and read replica is identical:</p>
  
  <pre><code>SELECT group_id, artifact_id, 
         account_id, number_of_interfaces, 
         date_trunc('month', server_build_time) as server_build_month, 
         count(*)
  FROM build_requests
  GROUP BY group_id, artifact_id, 
           account_id, number_of_interfaces, 
           date_trunc('month', server_build_time);
  </code></pre>
  
  <p>Thanks for your help!</p>