Re: DUDA EXPLAIN - base datos 240GB

Andrea escribió:

> Perdón, pero sigo sin saber como analisar el retorno de EXPLAIN.
> Gracias Mariano por el material, todavia no he dicho nada sobre el
> uso de Hash Tables, Sort, Merge....

Precisamente mi punto ...

> Mira el retorno de mi comando EXPLAIN abajo que yo no sé analisar.

Muestra tambien la consulta y las definiciones de las tablas
involucradas por favor? Algunos comentarios mas abajo:

>
> --------------------------------------------------------------------------------
>
> "Unique (cost=622207.32..622207.53 rows=2 width=238)"
> " -> Sort (cost=622207.32..622207.33 rows=2 width=238)"
> " Sort Key: imovel.imov_id, imovel.imov_qteconomia, imovel.imov_dscomplementoendereco, imovel.imov_nnimovel, imovel.imov_icimovelcondominio, imovel.imov_nnmorador, imovel.imov_idimovelcondominio, imovel.imov_idimovelprincipal, imovel.imov_nnpontosuti (..)"
> " -> Nested Loop (cost=108632.69..622207.31 rows=2 width=238)"
> " -> Index Scan using cliente_pkey on cliente (cost=0.00..5.14 rows=1 width=4)"
> " Index Cond: (clie_id = 1216255)"
> " -> Nested Loop Left Join (cost=108632.69..622202.15 rows=2 width=234)"
> " Join Filter: ("outer".hili_id = "inner".hili_id)"
> " -> Nested Loop Left Join (cost=108631.58..622200.59 rows=2 width=223)"
> " Join Filter: ("outer".hitp_id = "inner".hitp_id)"
> " -> Nested Loop Left Join (cost=108630.48..622199.08 rows=2 width=212)"
> " Join Filter: ("outer".hicp_id = "inner".hicp_id)"
> " -> Nested Loop Left Join (cost=108629.32..622197.30 rows=2 width=201)"
> " -> Nested Loop Left Join (cost=108629.32..622191.25 rows=2 width=181)"
> " -> Nested Loop Left Join (cost=108629.32..622185.19 rows=2 width=179)"
> " -> Hash Join (cost=108629.32..622179.14 rows=2 width=169)"
> " Hash Cond: ("outer".imov_id = "inner".imov_id)"
> " -> Hash Left Join (cost=108626.26..609291.26 rows=1717973 width=169)"
> " Hash Cond: ("outer".imov_id = "inner".lesg_id)"
> " -> Hash Left Join (cost=100455.00..488573.13 rows=1717973 width=141)"
> " Hash Cond: ("outer".lest_id = "inner".lest_id)"
> " -> Hash Left Join (cost=100453.93..462802.46 rows=1717973 width=138)"
> " Hash Cond: ("outer".imov_id = "inner".lagu_id)"
> " -> Hash Left Join (cost=44438.31..297334.03 rows=1717973 width=126)"
> " Hash Cond: ("outer".last_id = "inner".last_id)"
> " -> Hash Join (cost=44437.20..271563.32 rows=1717973 width=123)"
> " Hash Cond: ("outer".imov_id = "inner".imov_id)"
> " -> Seq Scan on imovel (cost=0.00..120574.66 rows=1717973 width=119)"
> " Filter: (imov_icexclusao = 2)"

Un seqscan grande ...

> " -> Hash (cost=31679.36..31679.36 rows=1727936 width=4)"
> " -> Seq Scan on imovel_subcategoria imovelsubcategoria (cost=0.00..31679.36 rows=1727936 width=4)"

Otro ...

> " -> Hash (cost=1.09..1.09 rows=9 width=11)"
> " -> Seq Scan on ligacao_agua_situacao (cost=0.00..1.09 rows=9 width=11)"
> " -> Hash (cost=42752.09..42752.09 rows=1586609 width=16)"
> " -> Seq Scan on ligacao_agua (cost=0.00..42752.09 rows=1586609 width=16)"

Otro.

> " -> Hash (cost=1.06..1.06 rows=6 width=11)"
> " -> Seq Scan on ligacao_esgoto_situacao (cost=0.00..1.06 rows=6 width=11)"
> " -> Hash (cost=5642.41..5642.41 rows=245141 width=32)"
> " -> Seq Scan on ligacao_esgoto (cost=0.00..5642.41 rows=245141 width=32)"

Otro

> " -> Hash (cost=3.06..3.06 rows=2 width=8)"
> " -> Index Scan using xix1_cliente_imovel on cliente_imovel (cost=0.00..3.06 rows=2 width=8)"
> " Index Cond: (1216255 = clie_id)"
> " Filter: (((crtp_id = 2) OR (crtp_id = 3)) AND (clim_dtrelacaofim IS NULL))"
> " -> Index Scan using "micromedicao.hidrometro_instalacao_historico_pkey" on hidrometro_instalacao_historico (cost=0.00..3.01 rows=1 width=18)"
> " Index Cond: ("outer".hidi_id = hidrometro_instalacao_historico.hidi_id)"
> " -> Index Scan using "micromedicao.hidrometro_instalacao_historico_pkey" on hidrometro_instalacao_historico hidrometro_instalacao_historico_esgoto (cost=0.00..3.01 rows=1 width=10)"
> " Index Cond: ("outer".hidi_id = hidrometro_instalacao_historico_esgoto.hidi_id)"
> " -> Index Scan using "micromedicao.hidrometro_pkey" on hidrometro (cost=0.00..3.01 rows=1 width=28)"
> " Index Cond: ("outer".hidr_id = hidrometro.hidr_id)"
> " -> Materialize (cost=1.15..1.29 rows=14 width=19)"
> " -> Seq Scan on hidrometro_capacidade (cost=0.00..1.14 rows=14 width=19)"
> " -> Materialize (cost=1.10..1.19 rows=9 width=19)"
> " -> Seq Scan on hidrometro_tipo (cost=0.00..1.09 rows=9 width=19)"
> " -> Materialize (cost=1.11..1.21 rows=10 width=19)"
> " -> Seq Scan on hidrometro_local_instalacao (cost=0.00..1.10 rows=10 width=19)"

Idealmente, esos seqscan deberian convertirse en alguna clase de
indexscan, posiblemente mediante el uso juicioso de redundancia en las
condiciones que le pasas al optimizador. Es dificil decir mucho mas
basado solamente en este explain, se necesita tambien la consulta.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support