Re: Problema uso de indices...

Aqui esta solo el explain analyze de la seccion que comentaba alvaro,
que justamente donde esta el problema:

Server1

# explain analyze select * from datosentrada_his where unitno='142'
and flota='Transportes Bueno' and fechacreacion between '07/13/2011
05:00' and '07/15/2011 04:59';

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..697.42 rows=2 width=600) (actual
time=80569.005..173377.349 rows=2865 loops=1)
-> Append (cost=0.00..697.42 rows=2 width=600) (actual
time=80568.997..173364.737 rows=2865 loops=1)
-> Index Scan using fki_vehiculo_his on datosentrada_his
(cost=0.00..697.42 rows=2 width=600) (actual
time=80568.991..173356.094 rows=2865 loops=1)
Index Cond: (((unitno)::text = '142'::text) AND
((flota)::text = 'Transportes Bueno'::text))
Filter: ((fechacreacion >= '2011-07-13
05:00:00'::timestamp without time zone) AND (fechacreacion <=
'2011-07-15 04:59:00'::timestamp without time zone))
Total runtime: 173381.978 ms
(6 rows)

server2

# explain analyze select * from datosentrada_his where unitno='142'
and flota='Transportes Bueno' and fechacreacion between '07/13/2011
05:00' and '07/15/2011 04:59';

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=4329.79..4385.75 rows=14 width=600) (actual
time=918.279..4858.531 rows=2865 loops=1)
-> Append (cost=4329.79..4385.75 rows=14 width=600) (actual
time=918.268..4844.345 rows=2865 loops=1)
-> Bitmap Heap Scan on datosentrada_his
(cost=4329.79..4385.75 rows=14 width=600) (actual
time=918.264..4835.848 rows=2865 loops=1)
Recheck Cond: (((unitno)::text = '142'::text) AND
((flota)::text = 'Transportes Bueno'::text) AND (fechacreacion >=
'2011-07-13 05:00:00'::timestamp without time zone) AND (fechacreacion
<= '2011-07-15 04:59:00'::timestamp without time zone))
-> BitmapAnd (cost=4329.79..4329.79 rows=14 width=0)
(actual time=889.621..889.621 rows=0 loops=1)
-> Bitmap Index Scan on fki_vehiculo_his
(cost=0.00..31.70 rows=1496 width=0) (actual time=364.576..364.576
rows=224090 loops=1)
Index Cond: (((unitno)::text = '142'::text)
AND ((flota)::text = 'Transportes Bueno'::text))
-> Bitmap Index Scan on ind_fecha
(cost=0.00..4298.04 rows=331110 width=0) (actual time=490.006..490.006
rows=334068 loops=1)
Index Cond: ((fechacreacion >= '2011-07-13
05:00:00'::timestamp without time zone) AND (fechacreacion <=
'2011-07-15 04:59:00'::timestamp without time zone))
Total runtime: 4863.894 ms
(10 rows)

Saludos

Roberto Campos