Fabio/Flávio/Marcone, me enganei quando disse que o plano havia passado a usar o indice correto. Reescrevi a query como manda o manual, mas deu na mesma. Nada elimina o fato de serem left joins. E realmente não tem como não ser. Veja como ficou a cláusula FROM da View (2). Ainda não sei como forçar os left joins a usarem a chave. Creio que meu problema seja o
OR da where no select da view (1). Se o otimizador não consegue estabelecer índice pra tabela nfe, as demais vão entrar de gaiato, visto que a razao de registros é mais ou menos esta: registros venda + registros compra = registros nfe (+- isso) Espero que eu tenha consegui explicar. 1) Select na view: select * from view_fila_nfe where (id_empresa = 1) and (id_fila = 1) and (status_retorno = '1') and (coalesce(email, '') <> '') AND (((dh_cancelamento IS NULL) AND (dh_envio_email IS NULL) AND (nao_enviar_email = false)) OR ((dh_cancelamento IS NOT NULL) AND (dh_envio_email_canc IS NULL) AND (nao_enviar_email_canc = false))) order by numero_nf, id 2) Clausula from da View: FROM nfe JOIN nfe_filas f ON f.id = nfe.id_fila LEFT JOIN (compras c LEFT JOIN romaneios r2 ON r2.id = c.id_romaneio_devolucao) ON c.id = nfe.id_compra LEFT JOIN (vendas v LEFT JOIN romaneios r1 ON r1.id = v.id_romaneio) ON v.id = nfe.id_venda JOIN terceiros t1 ON nfe.id_terceiro = t1.id JOIN naturezas_operacoes nop1 ON nfe.id_nat_operacao = nop1.id LEFT JOIN terceiros t2 ON t2.id = nfe.id_func_emissao LEFT JOIN terceiros t3 ON t3.id = nfe.id_func_cancelamento LEFT JOIN nfe_contingencia ctg ON ctg.id = nfe.id_contingencia; 3) Plan: "QUERY PLAN" "Sort (cost=428000.27..428787.57 rows=314920 width=631) (actual time=1916.863..1916.865 rows=9 loops=1)" " Sort Key: nfe.numero_nf, nfe.id" " Sort Method: quicksort Memory: 34kB" " -> Hash Left Join (cost=83251.92..307825.09 rows=314920 width=512) (actual time=1193.258..1916.790 rows=9 loops=1)" " Hash Cond: (nfe.id_contingencia = ctg.id)" " -> Hash Left Join (cost=83250.88..222401.98 rows=314920 width=510) (actual time=1193.045..1915.920 rows=9 loops=1)" " Hash Cond: (c.id_romaneio_devolucao = r2.id)" " -> Hash Left Join (cost=81392.90..213830.23 rows=314920 width=498) (actual time=1179.432..1902.286 rows=9 loops=1)" " Hash Cond: (v.id_romaneio = r1.id)" " -> Hash Left Join (cost=79534.93..204510.86 rows=314920 width=486) (actual time=1165.219..1888.047 rows=9 loops=1)" " Hash Cond: (nfe.id_func_cancelamento = t3.id)" " -> Hash Left Join (cost=78728.84..200158.77 rows=314920 width=464) (actual time=1156.528..1879.338 rows=9 loops=1)" " Hash Cond: (nfe.id_func_emissao = t2.id)" " -> Nested Loop (cost=77922.75..193264.02 rows=314920 width=442) (actual time=1147.371..1870.155 rows=9 loops=1)" " -> Seq Scan on nfe_filas f (cost=0.00..1.04 rows=1 width=11) (actual time=0.011..0.016 rows=1 loops=1)" " Filter: (id = 1)" " -> Hash Join (cost=77922.75..190113.78 rows=314920 width=433) (actual time=1147.356..1870.125 rows=9 loops=1)" " Hash Cond: (nfe.id_terceiro = t1.id)" " -> Hash Join (cost=77116.66..182615.64 rows=314920 width=388) (actual time=1137.210..1859.961 rows=9 loops=1)" " Hash Cond: (nfe.id_nat_operacao = nop1.id)" " -> Hash Left Join (cost=77115.17..178284.00 rows=314920 width=364) (actual time=1137.173..1859.900 rows=9 loops=1)" " Hash Cond: (nfe.id_compra = c.id)" " -> Hash Right Join (cost=70981.04..167401.37 rows=314920 width=346) (actual time=1034.179..1756.879 rows=9 loops=1)" " Hash Cond: (v.id = nfe.id_venda)" " -> Seq Scan on vendas v (cost=0.00..63304.69 rows=751569 width=27) (actual time=0.005..722.987 rows=749100 loops=1)" " -> Hash (cost=53512.54..53512.54 rows=314920 width=327) (actual time=639.931..639.931 rows=9 loops=1)" " Buckets: 16384 Batches: 4 Memory Usage: 2kB" " -> Seq Scan on nfe (cost=0.00..53512.54 rows=314920 width=327) (actual time=560.724..560.831 rows=9 loops=1)" " Filter: ((((dh_cancelamento IS NULL) AND (dh_envio_email IS NULL) AND (NOT nao_enviar_email)) OR ((dh_cancelamento IS NOT NULL) AND (dh_envio_email_canc IS NULL) AND (NOT nao_enviar_email_canc))) AND ((COALESCE(email, ''::character varying))::text <> ''::text) AND (id_fila = 1) AND (id_empresa = 1) AND (status_retorno = '1'::bpchar))" " -> Hash (cost=5137.39..5137.39 rows=79739 width=26) (actual time=102.958..102.958 rows=79637 loops=1)" " Buckets: 8192 Batches: 1 Memory Usage: 4828kB" " -> Seq Scan on compras c (cost=0.00..5137.39 rows=79739 width=26) (actual time=0.008..77.745 rows=79637 loops=1)" " -> Hash (cost=1.22..1.22 rows=22 width=32) (actual time=0.017..0.017 rows=22 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 2kB" " -> Seq Scan on naturezas_operacoes nop1 (cost=0.00..1.22 rows=22 width=32) (actual time=0.004..0.007 rows=22 loops=1)" " -> Hash (cost=621.04..621.04 rows=14804 width=49) (actual time=10.132..10.132 rows=14148 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 1148kB" " -> Seq Scan on terceiros t1 (cost=0.00..621.04 rows=14804 width=49) (actual time=0.006..5.062 rows=14148 loops=1)" " -> Hash (cost=621.04..621.04 rows=14804 width=30) (actual time=9.134..9.134 rows=14148 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 881kB" " -> Seq Scan on terceiros t2 (cost=0.00..621.04 rows=14804 width=30) (actual time=0.006..4.244 rows=14148 loops=1)" " -> Hash (cost=621.04..621.04 rows=14804 width=30) (actual time=8.675..8.675 rows=14148 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 881kB" " -> Seq Scan on terceiros t3 (cost=0.00..621.04 rows=14804 width=30) (actual time=0.004..3.581 rows=14148 loops=1)" " -> Hash (cost=1630.21..1630.21 rows=18221 width=20) (actual time=14.187..14.187 rows=18112 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 991kB" " -> Seq Scan on romaneios r1 (cost=0.00..1630.21 rows=18221 width=20) (actual time=0.007..8.796 rows=18112 loops=1)" " -> Hash (cost=1630.21..1630.21 rows=18221 width=20) (actual time=13.585..13.585 rows=18112 loops=1)" " Buckets: 2048 Batches: 1 Memory Usage: 991kB" " -> Seq Scan on romaneios r2 (cost=0.00..1630.21 rows=18221 width=20) (actual time=0.005..8.298 rows=18112 loops=1)" " -> Hash (cost=1.02..1.02 rows=2 width=6) (actual time=0.008..0.008 rows=2 loops=1)" " Buckets: 1024 Batches: 1 Memory Usage: 1kB" " -> Seq Scan on nfe_contingencia ctg (cost=0.00..1.02 rows=2 width=6) (actual time=0.005..0.006 rows=2 loops=1)" "Total runtime: 1917.180 ms" _______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
