Otimizador PostgreSQL

Boa tarde pessoal.

Estou com um problema em um PostgreSQL que está na Amazon RDS. versao 9.5.2.

O otimizador de consultas não está funcionando corretamente.

Seria um simples join entre uma fato e uma dimensão.


explain
select foi.fk_dim_product, count(1)
from evino_bi.fact_order_item foi
inner join dim_order_item_status dois on dois.id_dim_order_item_status = foi.fk_dim_order_item_status
where dois.is_reserved::varchar='1'
group by 1;

HashAggregate (cost=340762.43..340929.23 rows=3336 width=4)
Group Key: foi.fk_dim_product
-> Nested Loop (cost=2398.66..340486.11 rows=55263 width=4)
-> Seq Scan on dim_order_item_status dois (cost=0.00..6.62 rows=1 width=4)
Filter: (((is_reserved)::character varying)::text = '1'::text)
-> Bitmap Heap Scan on fact_order_item foi (cost=2398.66..334400.59 rows=121578 width=8)
Recheck Cond: (fk_dim_order_item_status = dois.id_dim_order_item_status)
-> Bitmap Index Scan on fact_order_item_fk_dim_order_item_status (cost=0.00..2368.27 rows=121578 width=0)
Index Cond: (fk_dim_order_item_status = dois.id_dim_order_item_status)


explain
select foi.fk_dim_product, count(1)
from evino_bi.fact_order_item foi
inner join dim_order_item_status dois on dois.id_dim_order_item_status = foi.fk_dim_order_item_status
where dois.is_reserved =1
group by 1;

HashAggregate (cost=663845.36..664012.16 rows=3336 width=4)
Group Key: foi.fk_dim_product
-> Hash Join (cost=9.75..644503.41 rows=3868389 width=4)
Hash Cond: (foi.fk_dim_order_item_status = dois.id_dim_order_item_status)
-> Seq Scan on fact_order_item foi (cost=0.00..428278.35 rows=6078897 width=8)
-> Hash (cost=6.08..6.08 rows=70 width=4)
-> Seq Scan on dim_order_item_status dois (cost=0.00..6.08 rows=70 width=4)
Filter: (is_reserved = 1)

A query onde forço o nested loops fazendo parse para varchar roda em 1s, porem a HashAggregate demora 1 min 47s.

Tenho indices criados nas colunas que faço join (fk_dim_order_item_status,id_dim_order_item_status) do tipo B-tree asc.

Já tentei entender o que acontece, testei alguns parâmetros porém sem sucesso.


SET random_page_cost =2
SET enable_hashjoin=0;
SET enable_mergejoin=0;
set enable_nestloop=1;

O plano de execução com os parametros acima:

explain
select foi.fk_dim_product, count(1)
from evino_bi.fact_order_item foi
inner join dim_order_item_status dois on dois.id_dim_order_item_status = foi.fk_dim_order_item_status
where dois.is_reserved =1
group by 1;

HashAggregate (cost=1982551.79..1982718.59 rows=3336 width=4)
Group Key: foi.fk_dim_product
-> Nested Loop (cost=0.14..1963209.84 rows=3868389 width=4)
-> Seq Scan on fact_order_item foi (cost=0.00..428278.35 rows=6078897 width=8)
-> Index Scan using dim_order_item_status_pkey on dim_order_item_status dois (cost=0.14..0.20 rows=1 width=4)
Index Cond: (id_dim_order_item_status = foi.fk_dim_order_item_status)
Filter: (is_reserved = 1)


E esses parametros de custo:

cpu_tuple_cost -> 0.05
random_page_cost = 2
seq_page_cost = 0.1
seq_page_cost = 0.1


Não tive sucesso a tabela principal tem 5 milhões de registros e a dimensão 110.

Preciso de ajuda para entender o que acontece e se temos a possibilidade de melhorar com algum parametro.

Obrigado desde já.