Return to Answer

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

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

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

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.

EDIT:1

Using the following index OP was able to get execution time 20-25 sec for cold cache and 6-8 sec for warm cache.

Create Index Idx_order_item_status_AND_product ON fact_order_item   (fk_dim_order_item_status,fk_dim_product)

But, If we changes the order of column in above index and create it as following then execution order comes down to 3-4 sec for cold cache.

Create Index Idx_order_item_status_AND_product ON fact_order_item   (fk_dim_product,fk_dim_order_item_status)

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

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

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

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.

EDIT:1

Using the following index OP was able to get execution time 20-25 sec for cold cache and 6-8 sec for warm cache.

Create Index Idx_order_item_status_AND_product ON fact_order_item   (fk_dim_order_item_status,fk_dim_product)

But, If we changes the order of column in above index and create it as following then execution order comes down to 3-4 sec for cold cache.

Create Index Idx_order_item_status_AND_product ON fact_order_item   (fk_dim_product,fk_dim_order_item_status)

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

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

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

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.

EDIT:1

Using the following index OP was able to get execution time 20-25 sec for cold cache and 6-8 sec for warm cache.

Create Index Idx_order_item_status_AND_product ON fact_order_item   (fk_dim_order_item_status,fk_dim_product)

But, If we changes the order of column in above index and create it as following then execution order comes down to 3-4 sec for cold cache.

Create Index Idx_order_item_status_AND_product ON fact_order_item   (fk_dim_product,fk_dim_order_item_status)

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

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

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

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.

EDIT:1

Using the following index OP was able to get execution time 20-25 sec for cold cache and 6-8 sec for warm cache.

Create Index Idx_order_item_status_AND_product ON fact_order_item   (fk_dim_order_item_status,fk_dim_product)

But, If we changes the order of column in above index and create it as following then execution order comes down to 3-4 sec for cold cache.

Create Index Idx_order_item_status_AND_product ON fact_order_item   (fk_dim_product,fk_dim_order_item_status)

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

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

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

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.

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

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

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

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.

EDIT:1

Using the following index OP was able to get execution time 20-25 sec for cold cache and 6-8 sec for warm cache.

Create Index Idx_order_item_status_AND_product ON fact_order_item   (fk_dim_order_item_status,fk_dim_product)

But, If we changes the order of column in above index and create it as following then execution order comes down to 3-4 sec for cold cache.

Create Index Idx_order_item_status_AND_product ON fact_order_item   (fk_dim_product,fk_dim_order_item_status)