Segue o explain analyze:
Desenvolvimento:
Aggregate (cost=35.81..35.82 rows=1 width=4) (actual time=6665.861..6665.861
rows=1 loops=1)
-> Nested Loop Left Join (cost=0.01..35.80 rows=1 width=4) (actual
time=580.658..6659.291 rows=17 loops=1)
Join Filter: (cliente0_.cltp_id = clientetip1_.cltp_id)
-> Nested Loop (cost=0.01..34.24 rows=1 width=8) (actual
time=575.580..6653.892 rows=17 loops=1)
-> Nested Loop (cost=0.01..28.90 rows=1 width=12) (actual
time=570.612..6648.031 rows=17 loops=1)
-> Nested Loop (cost=0.01..23.30 rows=1 width=12) (actual
time=170.684..6530.669 rows=283 loops=1)
-> Nested Loop (cost=0.01..17.22 rows=1 width=12)
(actual time=129.631..3960.245 rows=284 loops=1)
-> Index Scan using xix1_cliente on cliente
cliente0_ (cost=0.01..8.71 rows=1 width=8) (actual time=90.682..1465.911
rows=284 loops=1)
Index Cond:
((upper((clie_nmcliente)::text) >= 'EDNALDO F'::text) AND
(upper((clie_nmcliente)::text) < 'EDNALDO G'::text))
Filter: (upper((clie_nmcliente)::text) ~~
'EDNALDO F%'::text)
-> Index Scan using xfk1_cliente_endereco on
cliente_endereco clienteend2_ (cost=0.00..8.50 rows=1 width=8) (actual
time=8.761..8.779 rows=1 loops=284)
Index Cond: (clienteend2_.clie_id =
cliente0_.clie_id)
-> Index Scan using logradouro_bairro_pkey on
logradouro_bairro logradouro3_ (cost=0.00..6.07 rows=1 width=8) (actual
time=9.047..9.048 rows=1 loops=284)
Index Cond: (logradouro3_.lgbr_id =
clienteend2_.lgbr_id)
-> Index Scan using bairro_pkey on bairro bairro4_
(cost=0.00..5.59 rows=1 width=8) (actual time=0.413..0.413 rows=0 loops=283)
Index Cond: (bairro4_.bair_id = logradouro3_.bair_id)
Filter: (bairro4_.muni_id = 960)
-> Seq Scan on municipio municipio5_ (cost=0.00..5.33 rows=1
width=4) (actual time=0.335..0.340 rows=1 loops=17)
Filter: (municipio5_.muni_id = 960)
-> Seq Scan on cliente_tipo clientetip1_ (cost=0.00..1.25 rows=25
width=4) (actual time=0.300..0.307 rows=25 loops=17)
Total runtime: 6722.038 ms
Producao:
Aggregate (cost=62946.52..62946.53 rows=1 width=4) (actual
time=367275.224..367275.224 rows=1 loops=1)
-> Nested Loop (cost=25.18..62946.51 rows=1 width=4) (actual
time=44592.052..367275.118 rows=17 loops=1)
-> Nested Loop Left Join (cost=25.18..62941.18 rows=1 width=8)
(actual time=44592.000..367274.483 rows=17 loops=1)
Join Filter: (cliente0_.cltp_id = clientetip1_.cltp_id)
-> Nested Loop (cost=25.18..62939.61 rows=1 width=12) (actual
time=44591.974..367274.113 rows=17 loops=1)
-> Nested Loop (cost=25.18..24425.16 rows=65385 width=8)
(actual time=423.162..341101.644 rows=116336 loops=1)
-> Hash Join (cost=25.18..2535.31 rows=3682
width=8) (actual time=39.029..447.948 rows=5819 loops=1)
Hash Cond: (logradouro3_.bair_id =
bairro4_.bair_id)
-> Seq Scan on logradouro_bairro logradouro3_
(cost=0.00..2033.86 rows=117186 width=8) (actual time=0.011..359.858
rows=117186 loops=1)
-> Hash (cost=24.45..24.45 rows=59 width=8)
(actual time=38.677..38.677 rows=59 loops=1)
-> Bitmap Heap Scan on bairro bairro4_
(cost=4.71..24.45 rows=59 width=8) (actual time=38.542..38.641 rows=59 loops=1)
Recheck Cond: (960 = muni_id)
-> Bitmap Index Scan on
xfk1_bairro (cost=0.00..4.69 rows=59 width=0) (actual time=38.529..38.529
rows=59 loops=1)
Index Cond: (960 = muni_id)
-> Index Scan using xfk4_cliente_endereco on
cliente_endereco clienteend2_ (cost=0.00..4.11 rows=147 width=8) (actual
time=5.669..58.525 rows=20 loops=5819)
Index Cond: (clienteend2_.lgbr_id =
logradouro3_.lgbr_id)
-> Index Scan using cliente_pkey on cliente cliente0_
(cost=0.00..0.58 rows=1 width=8) (actual time=0.224..0.224 rows=0 loops=116336)
Index Cond: (cliente0_.clie_id = clienteend2_.clie_id)
Filter: (upper((clie_nmcliente)::text) ~~ 'EDNALDO
F%'::text)
-> Seq Scan on cliente_tipo clientetip1_ (cost=0.00..1.25
rows=25 width=4) (actual time=0.004..0.009 rows=25 loops=17)
-> Seq Scan on municipio municipio5_ (cost=0.00..5.33 rows=1 width=4)
(actual time=0.032..0.033 rows=1 loops=17)
Filter: (muni_id = 960)
Total runtime: 367275.396 ms
________________________________
De: Euler Taveira de Oliveira <eu...@timbira.com>
Para: Comunidade PostgreSQL Brasileira <pgbr-geral@listas.postgresql.org.br>
Enviadas: Terça-feira, 28 de Julho de 2009 2:21:11
Assunto: Re: [pgbr-geral] Analise de uso de index entre 8.2 ou 8.3
paulo matadr escreveu:
> Sera que o postgres 8.2 não suporta este tipo de index?
>
Suporta. Execute o EXPLAIN ANALYZE em ambas consultas e poste aqui.
--
Euler Taveira de Oliveira
http://www.timbira.com/
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
____________________________________________________________________________________
Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
