A tabela "movimento" (com todos os pedidos) tem um milhão de registros.
Destes, umas poucas dezenas têm "ecommerce_orderid_fk" não-nulo:
SELECT count(*) FROM movimento WHERE ecommerce_orderid_fk is not null;
count
-------
35
(1 row)
Eis o índice:
"idx_movim_website" UNIQUE, btree (ecommerce_orderid_fk) WHERE
ecommerce_orderid_fk IS NOT NULL
Eis o comando SQL:
EXPLAIN ANALYZE
SELECT ecommerce_orderid as order_id, data_criacao, state_etapa as state,
status_situacao as status, cod_tipomov_fk as tipo, num_orcamento_fk as
numped, valor_total, status_atual as st
FROM website_pedido LEFT OUTER JOIN movimento
ON (ecommerce_orderid = ecommerce_orderid_fk);
Eis o Explain Analyze:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=71252.85..84184.09 rows=188766 width=99) (actual
time=2114.078..2114.333 rows=35 loops=1)
Hash Cond: (website_pedido.ecommerce_orderid =
movimento.ecommerce_orderid_fk)
-> Seq Scan on website_pedido (cost=0.00..1.35 rows=35 width=80)
(actual time=0.005..0.023 rows=35 loops=1)
-> Hash (cost=51448.60..51448.60 rows=1078660 width=23) (actual
time=2113.922..2113.922 rows=35 loops=1)
-> Seq Scan on movimento (cost=0.00..51448.60 rows=1078660
width=23) (actual time=0.005..1919.003 rows=1077450 loops=1)
Total runtime: 2114.367 ms
(6 rows)
A questão é: porque o índice não está sendo usado?
--
Atenciosamente,
Alexsander da Rosa
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
