there are many other discussions you will find for the same issue on the web. Just google it. I have attached few good reads for your own knowledge. there are different causes and different solutions related to Postgres DB engine configurations.

[hash join vs nested loop join][1]

[Have Postgresql query planner use nested loop w/ indices over hash join][2]

[PostgreSQL query runs faster with index scan, but engine chooses hash join][3]


A root problem I see is Postgres is choosing "Hash" join instead "Nested Loop" join and end up using Seq Scan with higher read cost. there are options using which you can either "disable HASH join" or "disable the Seq Scan" for a specific transaction but that will not be an ideal solution. An ideal solution is we want to write the query in a way so that Postgres pick up correct(most efficient) join type appropriate for the query. I would suggest to rewrite the query as below and lets us know how that works.


    Select foi.fk_dim_product,Count(1)
    From evino_bi.fact_order_item foi
    inner join
    (
    	select id_dim_order_item_status
    	From dim_order_item_status
    	Where is_reserved = 1
    ) dois
    on dois.id_dim_order_item_status = foi.fk_dim_order_item_status
    group by foi.fk_dim_product;

If rewriting the query does not make Postgres switch to Nested Loop join type then my next best bet is checking the postgres version and follow the instruction to update configuration property "cpu_tuple_cost" along with other mentioned in one of the above-referenced discussion.

  [1]: https://www.postgresql.org/message-id/CAD1stZv-CsoBSpH3Oimu-OrhXPAf6ouA-pYLjbE6obgNpVNX8A@mail.gmail.com
  [2]: http://dba.stackexchange.com/questions/66678/have-postgresql-query-planner-use-nested-loop-w-indices-over-hash-join
  [3]: http://stackoverflow.com/questions/10643215/postgresql-query-runs-faster-with-index-scan-but-engine-chooses-hash-join