help with too slow query - Mailing list pgsql-performance

Hello,
  I have this table definition:
CREATE TABLE ism_floatvalues
(
  id_signal bigint NOT NULL, -- Indica la señal a la que pertenece este valor. Clave foránea que referencia al campo id_signal de la tabla ism_signal.
  time_stamp timestamp without time zone NOT NULL, -- Marca de tiempo que indica fecha y hora correpondiente a este dato. Junto con id_signal forma la clave primaria de esta tabla
  var_value double precision, -- Almacena el valor concreto de la señal en la marca de tiempo espeficicada.
  CONSTRAINT ism_floatvalues_id_signal_fkey FOREIGN KEY (id_signal)
      REFERENCES ism_signal (id_signal) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

CREATE INDEX ism_floatvalues_index_idsignal_timestamp
  ON ism_floatvalues
  USING btree
  (id_signal, time_stamp DESC);



*********************************************

Then I run this query....
*********************************************
EXPLAIN analyze
select round(CAST(sum(var_value) AS numeric),2) as var_value, date_trunc('month', time_stamp) as time_stamp , date_part('month',date_trunc('month', time_stamp)) as month, date_part('year',date_trunc('year', time_stamp)) as year from ism_floatvalues where id_signal in
(
select id_signal from ism_signal where reference = 'EDCA' and id_source in
(
select id_source from ism_installation where id_installation in
(select id_installation from ism_groupxinstallation where id_group = 101)
)
)
and time_stamp > date_trunc('month', current_date - interval '11 months')
group by date_trunc('month', time_stamp), month, year
order by time_stamp

******************************
And this is the result:
******************************

"GroupAggregate  (cost=4766541.62..4884678.62 rows=39483 width=16) (actual time=1302542.073..1302713.154 rows=10 loops=1)"
"  ->  Sort  (cost=4766541.62..4789932.12 rows=9356201 width=16) (actual time=1302444.324..1302531.447 rows=9741 loops=1)"
"        Sort Key: (date_trunc('month'::text, ism_floatvalues.time_stamp)), (date_part('month'::text, date_trunc('month'::text, ism_floatvalues.time_stamp))), (date_part('year'::text, date_trunc('year'::text, ism_floatvalues.time_stamp)))"
"        Sort Method:  quicksort  Memory: 941kB"
"        ->  Hash Join  (cost=545.65..3203518.39 rows=9356201 width=16) (actual time=458941.090..1302245.307 rows=9741 loops=1)"
"              Hash Cond: (ism_floatvalues.id_signal = ism_signal.id_signal)"
"              ->  Seq Scan on ism_floatvalues  (cost=0.00..2965077.57 rows=28817098 width=24) (actual time=453907.600..1002381.652 rows=29114105 loops=1)"
"                    Filter: (time_stamp > date_trunc('month'::text, (('now'::text)::date - '11 mons'::interval)))"
"              ->  Hash  (cost=544.19..544.19 rows=117 width=8) (actual time=733.782..733.782 rows=40 loops=1)"
"                    Buckets: 1024  Batches: 1  Memory Usage: 2kB"
"                    ->  HashAggregate  (cost=543.02..544.19 rows=117 width=8) (actual time=733.072..733.412 rows=40 loops=1)"
"                          ->  Hash Semi Join  (cost=27.61..542.73 rows=117 width=8) (actual time=638.175..687.934 rows=40 loops=1)"
"                                Hash Cond: (ism_signal.id_source = ism_installation.id_source)"
"                                ->  Bitmap Heap Scan on ism_signal  (cost=18.84..530.42 rows=850 width=16) (actual time=243.690..284.303 rows=850 loops=1)"
"                                      Recheck Cond: ((reference)::text = 'EDCA'::text)"
"                                      ->  Bitmap Index Scan on ism_signal_idx_reference  (cost=0.00..18.63 rows=850 width=0) (actual time=243.429..243.429 rows=865 loops=1)"
"                                            Index Cond: ((reference)::text = 'EDCA'::text)"
"                                ->  Hash  (cost=8.27..8.27 rows=40 width=8) (actual time=394.393..394.393 rows=40 loops=1)"
"                                      Buckets: 1024  Batches: 1  Memory Usage: 2kB"
"                                      ->  Hash Semi Join  (cost=3.25..8.27 rows=40 width=8) (actual time=391.966..394.000 rows=40 loops=1)"
"                                            Hash Cond: (ism_installation.id_installation = ism_groupxinstallation.id_installation)"
"                                            ->  Seq Scan on ism_installation  (cost=0.00..4.17 rows=117 width=16) (actual time=0.086..1.354 rows=117 loops=1)"
"                                            ->  Hash  (cost=2.75..2.75 rows=40 width=8) (actual time=390.274..390.274 rows=40 loops=1)"
"                                                  Buckets: 1024  Batches: 1  Memory Usage: 2kB"
"                                                  ->  Seq Scan on ism_groupxinstallation  (cost=0.00..2.75 rows=40 width=8) (actual time=389.536..389.903 rows=40 loops=1)"
"                                                        Filter: (id_group = 101)"
"Total runtime: 1302731.013 ms"


This query is very slow as you can see, it took about 20 minutos to complete.... Can someone help me to improve performance on this query??
Regards.

Pedro Jiménez Pérez
p.jimenez@ismsolar.com

Innovación en Sistemas de Monitorización, S.L. Edificio Hevimar C/ Iván Pavlov 2 y 4 - Parcela 4 2ª Planta Local 9 Parque Tecnológico de Andalucía 29590 Campanillas (Málaga) Tlfno. 952 02 07 13 contacto@ismsolar.com

	Antes de imprimir, piensa en tu responsabilidad y compromiso con el MEDIO AMBIENTE!
	Before printing, think about your responsibility and commitment with the ENVIRONMENT!

CLÁUSULA DE CONFIDENCIALIDAD.- Este mensaje, y en su caso, cualquier fichero anexo al mismo, puede contener información confidencial o legalmente protegida (LOPD 15/1999 de 13 de Diciembre), siendo para uso exclusivo del destinatario. No hay renuncia a la confidencialidad o secreto profesional por cualquier transmisión defectuosa o errónea, y queda expresamente prohibida su divulgación, copia o distribución a terceros sin la autorización expresa del remitente. Si ha recibido este mensaje por error, se ruega lo notifique al remitente enviando un mensaje al correo electrónico contacto@ismsolar.com y proceda inmediatamente al borrado del mensaje original y de todas sus copias. Gracias por su colaboración.