Re: help with too slow query

Ok, here we go:

I'm using postgresql version 8.0

Here is my query that is too slow: http://explain.depesz.com/s/GbQ

***************************************************
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

***************************************************
Here are the tables:

Table ism_floatvalues:
Table ism_floatvalues has about 100 million records.
This table is updated everyday. Everyday we delete the data stored
regarding yesterday, usually from 8 am to 13 pm moreless, and then we
insert the data for yesterday (complete data) and the data we have
available for today (usually from 8 am to 13pm) Then, tomorrow, we start
over again.... so about 30% of records are deleted at least one time.
***************************************************
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);
***************************************************

Table ism_signal:
this table has about 24K records
****************************************************
CREATE TABLE ism_signal
(
id_signal bigserial NOT NULL, -- Código numérico autoincremental.
Clave primaria de la tabla.
id_source bigint NOT NULL,
reference character varying NOT NULL, -- Cadena de caracteres con la
que se identifica de forma única cada señal ( válida para toda la
plataforma de ISM ).
"name" character varying NOT NULL, -- Cadena de caracteres con la que
se muestra este señal al usuario.
signalclass character varying NOT NULL, -- Indica la clase de la
señal. Sólo admite valores measure, global, hourly, daily, monthly,
yearly, alarm, event, constant y attribute.
signaltype character varying NOT NULL, -- Indica el tipo de dato de
la señal.
opcitem character varying, -- Cadena de caracteres que indica el item
OPC de donde debe leerse esta señal. Como un mismo servidor OPC puede
manejar varios sistemas, este item OPC se personaliza según el el
espacio de nombres para una configuración concreta del servidor OPC (si
hay 3 inversores gestionados por el mismo servidor OPC, el canal que
genéricamente se donomina PCC debe ser personalizado a 1.PCC, 2.PCC o 3.PCC.
formula character varying, -- Cadena de caracteres que indica la
fórmula de ajuste para esta señal (como como argumento el valor leido
del servidor OPC y le aplica esta fórmula).
id_opcserverconf bigint, -- Referencia a un servidor OPC configurado
de una forma determinada. Clave foránea al campo id_opcServerConf de la
tabla ism_opcServerConf
decimals smallint, -- Número de cifras decimales que se muestran al
usuario
unit character varying, -- Cadena de caracteres que indica las
unidades de medida.
description text, -- Breve descripción y comentarios adicionales.
max_value double precision, -- Límite superior para representar
gráficamente la magnitud.
min_value double precision, -- Límite inferior para representar
gráficamente la magnitud.
critical_day date, -- Indica el día a partir del cual debemos empezar
a pedir datos de esta señal.
erased boolean DEFAULT false, -- Indica si este canal debe o no
mostrarse en la web. Si vale "false" el dato debe mostrarse.
writetodb boolean DEFAULT true, -- Indica si este canal debe o no
almacenarse en la base de datos. Si vale "true" el dato debe
almacenarse
dbupdaterate integer DEFAULT 0, -- Indica el intervalo de tiempo en
el que debe recuperarse y almacenarse esta señal desde el servidor OPC a
la base de datos.
ordering integer, -- Indica el orden en el que deben mostrarse las
señales al usuarioIndica el intervalo de tiempo en el que debe
recuperarse y almacenarse esta señal desde el servidor OPC a la base de
datos. Si están a NULL los canales se ordenan por orden alfabético.
sync_level smallint NOT NULL DEFAULT 0, -- ndica el nivel de
sincronización. Si vale 0 indica que el dato se obtiene
directamente un servidor OPC asociado a un dispositivo. Si vale N (N>0)
significa que la señal se obtiene mediante cálculos sobre alguna señal
de nivel N-1 y se lee de un servidor OPC asociado a la base de datos.
Las señales de nivel N deben sincronizarse antes que las de nivel N+1.
hastodaydata boolean NOT NULL DEFAULT true, -- Indica si la medida
tiene datos en el dia actual o si por el contrario solo tiene datos de
dias ya pasados
interval_minutes smallint,
updaterate integer DEFAULT 0,
CONSTRAINT ism_signal_pkey PRIMARY KEY (id_signal),
CONSTRAINT ism_signal_id_opcserverconf_fkey FOREIGN KEY
(id_opcserverconf)
REFERENCES ism_opcserverconf (id_opcserverconf) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT ism_signal_id_source_fkey FOREIGN KEY (id_source)
REFERENCES ism_source (id_source) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT ism_signal_name_check CHECK (name::text <> ''::text),
CONSTRAINT ism_signal_reference_check CHECK (reference::text ~
'^[_A-Za-z0-9]+$'::text),
CONSTRAINT ism_signal_signalclass_check2 CHECK (signalclass::text =
'measure'::text OR signalclass::text = 'global'::text OR
signalclass::text = 'hourly'::text OR signalclass::text = 'daily'::text
OR signalclass::text = 'monthly'::text OR signalclass::text =
'yearly'::text OR signalclass::text = 'alarm'::text OR signalclass::text
= 'event'::text OR signalclass::text = 'constant'::text OR
signalclass::text = 'attribute'::text OR signalclass::text =
'DAmeasure'::text OR signalclass::text = 'filter'::text),
CONSTRAINT ism_signal_signaltype_check CHECK (signaltype::text =
'float'::text OR signaltype::text = 'integer'::text OR signaltype::text
= 'char'::text OR signaltype::text = 'string'::text OR signaltype::text
= 'boolean'::text OR signaltype::text = 'memo'::text OR signaltype::text
= 'void'::text)
)
WITH (
OIDS=FALSE
);

CREATE INDEX ism_signal_idx_id_signal
ON ism_signal
USING btree
(id_signal);

CREATE INDEX ism_signal_idx_id_source
ON ism_signal
USING btree
(id_source);

CREATE INDEX ism_signal_idx_reference
ON ism_signal
USING btree
(reference);

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

Table ism_installation:
This table has about 200 records.
******************************************

CREATE TABLE ism_installation
(
id_installation bigserial NOT NULL, -- Código numérico
autoincremental. Clave primaria de la tabla.
id_source bigint NOT NULL, -- Código único para cualquier fuente de
señales (source). Clave foránea al campo id_source de la tabla ism_source
"name" character varying NOT NULL, -- Nombre de la instalación. No
puede ser cadena vacía ni nulo. No puede haber dos instalaciones con el
mismo nombre.
description text, -- Breve descripción y comentarios adicionales.
latitude_degree smallint, -- Grados de latitud del emplazamineto de
la instalación. Entero entre -180 y +180
latitude_minute smallint, -- Minutos de latitud del emplazamineto de
la instalación. Entero entre 0 y 59
longitude_degree smallint, -- Grados de longitud del emplazamineto de
la instalación. Entero entre -180 y +180
longitude_minute smallint, -- Minutos de longitud del emplazamineto
de la instalación. Entero entre 0 y 59
city character varying, -- Nombre del término municipal donde está la
instalación
province character varying, -- Nombre de la provincia donde está la
instalación.
id_class bigint, -- Referncia a un elemento de ism_class que indica
la tecnología de la instalación(fotovoltaica, eólica, térmica). Clave
foránea al campo id_class de la tabla ism_class
initial_date date,
last_date date,
last_hour time without time zone,
ordering integer DEFAULT 0, -- Orden de las instalaciones de un grupo
short_name character varying(20), -- Nombre corto de la instalcion
para mostrarlo en un menu lateral de la web, que no debe exceder de 20
caracteres
id_owner bigint,
id_distributor bigint,
installation_type character(1),
id_syncgroup integer,
address character varying(256),
ripre character varying(256),
plantgroup character varying(256),
power character varying(256),
edecode character varying(256),
instgroup character varying(256),
id_zone bigint,
active boolean DEFAULT true,
latitude_second double precision,
longitude_second double precision,
id_node integer,
firstdataday date,
CONSTRAINT ism_installation_pkey PRIMARY KEY (id_installation),
CONSTRAINT id_syncgroup_fk FOREIGN KEY (id_syncgroup)
REFERENCES ism_syncgroup (id_syncgroup) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT id_zone_fk FOREIGN KEY (id_zone)
REFERENCES ism_counterzone (id_zone) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT ism_installation_distributor_fkey FOREIGN KEY (id_distributor)
REFERENCES ism_distributor (id_distributor) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT ism_installation_id_class_fkey FOREIGN KEY (id_class)
REFERENCES ism_class (id_class) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT ism_installation_id_source_fkey FOREIGN KEY (id_source)
REFERENCES ism_source (id_source) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT ism_installation_owner_fkey FOREIGN KEY (id_owner)
REFERENCES ism_owner (id_owner) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT ism_installation_name_key UNIQUE (name),
CONSTRAINT ism_installation_latitude_degree_check CHECK ((-180) <=
latitude_degree AND latitude_degree <= 180),
CONSTRAINT ism_installation_latitude_minute_check CHECK (0 <=
latitude_minute AND latitude_minute < 60),
CONSTRAINT ism_installation_latitude_second_check CHECK (0::double
precision <= latitude_second AND latitude_second < 60::double precision),
CONSTRAINT ism_installation_longitude_degree_check CHECK ((-180) <=
longitude_degree AND longitude_degree <= 180),
CONSTRAINT ism_installation_longitude_minute_check CHECK (0 <=
longitude_minute AND longitude_minute < 60),
CONSTRAINT ism_installation_longitude_second_check CHECK (0::double
precision <= longitude_second AND longitude_second < 60::double precision),
CONSTRAINT ism_installation_name_check CHECK (name::text <> ''::text)
)
WITH (
OIDS=FALSE
);

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

Regards.

El 05/11/2012 10:54, Виктор Егоров escribió:
> 2012/11/2 Pedro Jiménez Pérez <p(dot)jimenez(at)ismsolar(dot)com
> <mailto:p(dot)jimenez(at)ismsolar(dot)com>>
>
> I have this table definition:
>
>
> 1) Could you kindly include also information bout ism_signal and
> ism_installation tables?
> 2) Please, follow this guide to provide more input:
> http://wiki.postgresql.org/wiki/Slow_Query_Questions
>
>
> --
> Victor Y. Yegorov

--
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.