Criterio para crear indices

Saludos cordiales, esta es mi primera participación en la lista.
He usado postgres desde hace unos 7 años (a nivel basico) para pequeñas
aplicaciones sin muchos registros.

Explico brevemente, tengo una tabla que a su vez tiene varias
particiones, estas particiones a su vez tienen otras particiones
separadas por meses.

La cantidad de registros en total supera los 40 millones y cada hora se
realiza un minimo de 45000 inserts.

El haber particionado las tablas mejoró extraordinariamente el desempeño
en los inserts. El problema se da al hacer selects a la tabla, resulta
muy muy lento cuando quiero filtrar por ciertos campos.

La extructura esta asi:

events (padre)
events_size (heredada)
events_size_201509 (heredada)
events_size_201510 (heredada)
events_db (heredada)
events_db_201509 (heredada)
events_db_201510 (heredada)

Tengo un trigger en las tablas hijas para dipararse luego de cada
insert que internamente lo que hace es realizar varias consultas e
inserts, pero la que tengo problemas es una consulta a la tabla events
(padre) en busca de unas coincidencias, que por lo general no devuelve
mas de 10 registros.

Si comento esta parte del codigo el fluido es rapido (unos 10 inserts
por segundo) pero si lo descomento todo se atasca (uno 10 inserts por
minuto).

Dije que seria breve, lamento haberme extendido, agradezco toda la ayuda
que me puedan dar.

La tabla en cuestión es la siguiente:

CREATE TABLE events
(
idevent bigserial NOT NULL,
ts timestamp without time zone DEFAULT now(),
loaded timestamp without time zone DEFAULT now(),
dateevent timestamp without time zone DEFAULT now(),
status integer NOT NULL DEFAULT 0, -- Este campo se actualiza
automaticamente segun el estado del ultimo comentario ingresado para
este evento.
idaccount integer,
code text,
priority integer DEFAULT (-1),
ideventtype integer DEFAULT 0,
description text,
idadmin integer DEFAULT 0, -- IdAdmin del usuario que crea el evento,
generalmente es el sistema quien ingresa el evento, por seguridad es
necesario registrar si un administrador inserta un evento manualmente.
last_comment timestamp without time zone, -- Fecha del ultimo
comentario registrado.
account text,
zu bigint NOT NULL DEFAULT 0,
note text,
CONSTRAINT pk_events_idevent PRIMARY KEY (idevent),
CONSTRAINT fk_events_ideventtype FOREIGN KEY (ideventtype)
REFERENCES eventtypes (ideventtype) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE SET DEFAULT,
CONSTRAINT uniq_events_idaccount_idevettype_zu UNIQUE (dateevent,
idaccount, ideventtype, zu)
)
WITH (
OIDS=FALSE,
autovacuum_enabled=true,
toast.autovacuum_enabled=true
);
ALTER TABLE events
OWNER TO postgres;
COMMENT ON TABLE events
IS 'Las notificaciones de los eventos solo se muestran al usuario si
su proiridad es <= 20';
COMMENT ON COLUMN events.status IS 'Este campo se actualiza
automaticamente segun el estado del ultimo comentario ingresado para
este evento.';
COMMENT ON COLUMN events.idadmin IS 'IdAdmin del usuario que crea el
evento, generalmente es el sistema quien ingresa el evento, por
seguridad es necesario registrar si un administrador inserta un evento
manualmente.';
COMMENT ON COLUMN events.last_comment IS 'Fecha del ultimo comentario
registrado.';

-- Index: index_events_idevent_idaccount_ideventtype_zu

-- DROP INDEX index_events_idevent_idaccount_ideventtype_zu;

CREATE INDEX index_events_idevent_idaccount_ideventtype_zu
ON events
USING btree
(idevent, idaccount, ideventtype, zu);

-- Trigger: on_before_insert_event on events

-- DROP TRIGGER on_before_insert_event ON events;

CREATE TRIGGER on_before_insert_event
BEFORE INSERT
ON events
FOR EACH ROW
EXECUTE PROCEDURE events_before_insert_redirect_partition();
COMMENT ON TRIGGER on_before_insert_event ON events IS 'Se dispara
cuando se trata de insertar un evento y se redirige a la tabla que le
corresponde segun la particion.';

-- Trigger: on_changed_table on events

-- DROP TRIGGER on_changed_table ON events;

CREATE TRIGGER on_changed_table
AFTER INSERT OR UPDATE OR DELETE
ON events
FOR EACH ROW
EXECUTE PROCEDURE on_changed_table();
ALTER TABLE events DISABLE TRIGGER on_changed_table;

-- Trigger: on_insert_event on events

-- DROP TRIGGER on_insert_event ON events;

CREATE TRIGGER on_insert_event
AFTER INSERT
ON events
FOR EACH ROW
EXECUTE PROCEDURE events_on_insert();
ALTER TABLE events DISABLE TRIGGER on_insert_event;

-- Trigger: on_update_row on events

-- DROP TRIGGER on_update_row ON events;

CREATE TRIGGER on_update_row
BEFORE UPDATE
ON events
FOR EACH ROW
EXECUTE PROCEDURE on_update_row_update_ts();
ALTER TABLE events DISABLE TRIGGER on_update_row;

Esta es la parte del código donde se vuelve extremadamente lento dentro
del trigger:

SELECT idevent FROM events WHERE ideventtype =
ANY(et.auto_close_on_event_defined)
AND status = 0 AND idaccount = NEW.idaccount AND zu = NEW.zu AND idevent
!= NEW.idevent

El campo zu antes era tipo texto, lo cambie a bigint, eso mejoro un poco
pero sigue lento, no se que mas hacer.
Que campos debo indexar o que podria hacer?

/*
FOR idevent_for_close_on_restore IN SELECT idevent FROM events WHERE
ideventtype = ANY(et.auto_close_on_event_defined)
AND status = 0 AND idaccount = NEW.idaccount AND zu = NEW.zu AND idevent
!= NEW.idevent LOOP

INSERT INTO event_comments(
comment_event, status,
idevent)
VALUES ('Event closed for restoring event idevent '||NEW.idevent,
7, idevent_for_close_on_restore);

END LOOP;
*/

Todo esto pertenece a una aplicacion web de monitoreo de servidores y
aplicaciones (o algo asi), la he publicado en
http://openamsdemo-edwinspire.rhcloud.com/oams_contacts.php

Aunque aun no esta la base de datos instalada.

Atte:
Edwin De La Cruz (edwinspire)
Quito - Ecuador