Thanks for the answer. It's an RDS instance using SSD storage and the default `random_page_cost` set to 4.0. I don't expect a lot of repetitive queries here, so I think caching may not be extremely useful. I wonder if the selectivity of the query is wrongly estimated (out of 500 million rows, only a few thousands are returned).
I tried lowering the `random_page_cost` to 1.2 and it didn't make a difference in the query plan.
iulian
On Fri, Aug 21, 2020 at 6:30 PM Michael Lewis <mlewis@entrata.com> wrote:
Your system is preferring sequential scan to using test_result_module_result_id_idx in this case. What type of storage do you use, what type of cache hits do you expect, and what do you have random_page_cost set to? That comes to mind as a significant factor in choosing index scans based on costs.
