Skip site navigation (1) Skip section navigation (2)

help with too slow query

From: Pedro Jiménez Pérez <p(dot)jimenez(at)ismsolar(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: help with too slow query
Date: 2012-11-02 12:13:52
Message-ID: 5093B900.7020206@ismsolar.com (view raw or flat)
Thread:
Lists: 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.
-- 
Documento sin título

**Pedro Jiménez Pérez
**p(dot)jimenez(at)ismsolar(dot)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(at)ismsolar(dot)com

firma_gpt.jpg, 1 kB

	

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(at)ismsolar(dot)com y proceda 
inmediatamente al borrado del mensaje original y de todas sus copias. 
Gracias por su colaboración.

Responses

pgsql-performance by date

Next:From: Petr PrausDate: 2012-11-02 14:09:52
Subject: Re: Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries
Previous:From: Ants AasmaDate: 2012-11-02 11:27:40
Subject: Re: dbt2 performance regresses from 9.1.6 to 9.2.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group