RE: por que SEQ SCAN Y NO INDEX SCAN

ok, entonces muestranos el explain analyze de la vista *con* el where
(la que dices que te arroja 11 registros)... el select que mostraste
devuelve 446922 registros...

tambien dinos la version de postgres... por cierto porque el select de
la vista tiene un ORDER BY (esta consumiendo tiempo y posiblemente no
sea realmente necesario)?

--
Atentamente,
Jaime Casanova
Soporte de PostgreSQL
Guayaquil - Ecuador
Cel. 087171157

***********ESTE ES EL EXPLAIN ANALYZE **********************************+

"Hash Left Join (cost=61680.50..68144.24 rows=3 width=122) (actual
time=32106.612..32344.086 rows=11 loops=1)"
" Hash Cond: (((almacen_crudo.idrollo)::text = (tejidos2.idrollo)::text)
AND (almacen_crudo.anio_maqui = tejidos1.anio_maqui) AND
((almacen_crudo.idmaqui)::text = (tejidos1.idmaqui)::text))"
" -> Nested Loop Left Join (cost=0.00..41.29 rows=3 width=108) (actual
time=0.156..0.732 rows=11 loops=1)"
" -> Index Scan using hrt3_idx1 on hrt3 (cost=0.00..16.38 rows=3
width=76) (actual time=0.101..0.115 rows=11 loops=1)"
" Index Cond: (((idpartida)::text = '5000'::text) AND
(anio_partida = '2008'::bpchar))"
" -> Index Scan using almacen_crudo_idx4 on almacen_crudo
(cost=0.00..8.29 rows=1 width=32) (actual time=0.049..0.051 rows=1
loops=11)"
" Index Cond: (((hrt3.idrollo)::text =
(almacen_crudo.idrollo)::text) AND (hrt3.anio_maqui =
almacen_crudo.anio_maqui) AND ((hrt3.idmaqui)::text =
(almacen_crudo.idmaqui)::text))"
" -> Hash (cost=44837.18..44837.18 rows=596361 width=56) (actual
time=28517.188..28517.188 rows=595637 loops=1)"
" -> Hash Join (cost=5037.26..44837.18 rows=596361 width=56)
(actual time=257.406..5755.329 rows=595637 loops=1)"
" Hash Cond: (tejidos2.idapu = tejidos1.idapu)"
" -> Seq Scan on tejidos2 (cost=0.00..18360.61 rows=596361
width=37) (actual time=0.029..406.783 rows=596313 loops=1)"
" -> Hash (cost=3688.33..3688.33 rows=66394 width=27) (actual
time=176.824..176.824 rows=66211 loops=1)"
" -> Hash Join (cost=18.60..3688.33 rows=66394
width=27) (actual time=0.756..126.713 rows=66211 loops=1)"
" Hash Cond: (((tejidos1.idmaqui)::text =
(mmaqtejido1.idmaqui)::text) AND (tejidos1.anio_maqui =
mmaqtejido1.anio_maqui))"
" -> Seq Scan on tejidos1 (cost=0.00..1345.94
rows=66394 width=22) (actual time=0.027..35.197 rows=66394 loops=1)"
" -> Hash (cost=10.44..10.44 rows=544 width=19)
(actual time=0.714..0.714 rows=544 loops=1)"
" -> Seq Scan on mmaqtejido1
(cost=0.00..10.44 rows=544 width=19) (actual time=0.027..0.329 rows=544
loops=1)"
"Total runtime: 32344.459 ms"

***************este es el SQL
explain analyze SELECT
hrt3.orden_partida,
hrt3.idpartida,
hrt3.item_partida,
hrt3.anio_partida,
hrt3.idrollo,
hrt3.idmaqui,
hrt3.anio_maqui,
hrt3.kg_armado,
hrt3.kg_despacho,
almacen_crudo.rollo_kilos,
vejecutado_tejeduria.idot,
vejecutado_tejeduria.item_ot,
vejecutado_tejeduria.idmaquo,
vejecutado_tejeduria.fecha_tejido,
vejecutado_tejeduria.idtejedor
FROM
hrt3
LEFT OUTER JOIN almacen_crudo ON (hrt3.idrollo = almacen_crudo.idrollo)
AND (hrt3.anio_maqui = almacen_crudo.anio_maqui)
AND (hrt3.idmaqui = almacen_crudo.idmaqui)
LEFT OUTER JOIN vejecutado_tejeduria ON (almacen_crudo.idrollo =
vejecutado_tejeduria.idrollo)
AND (almacen_crudo.anio_maqui = vejecutado_tejeduria.anio_maqui)
AND (almacen_crudo.idmaqui = vejecutado_tejeduria.idmaqui)
where hrt3.idpartida = '5000' and hrt3.anio_partida = '2008'

Miguel