|
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
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@xxxxxxxxxxxx y proceda inmediatamente al borrado del mensaje original y de todas sus copias. Gracias por su colaboración. |
