Re: Query plan prefers hash join when nested loop is much faster

Hi Michael,

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(at)entrata(dot)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.
>