Index-Verwendung

Hallo,

nach Herrn Renners Empfehlung habe ich nun mit der Partitionierung der grossen Tabellen angefangen.

Leider ist nun die Abfrage der Daten langsamer. Folgender Fall:

CREATE TABLE ndalarmhistory
(
  id bigserial NOT NULL,
  user_id text,
  ndalarm_id bigint,
  action integer,
  actiontime timestamp without time zone,
  touser text,
  CONSTRAINT ndalarmhistory_id_pk PRIMARY KEY (id ),
  CONSTRAINT ndalarmhistory_ndalarm_id_fkey FOREIGN KEY (ndalarm_id)
      REFERENCES ndalarm (ndalarm_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT ndalarmhistory_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES users (user_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

Einer der Child-Table:

CREATE TABLE ndalarmhistory_20000000
(
  CONSTRAINT ndalarmhistory_20000000_pkey PRIMARY KEY (id ),
  CONSTRAINT ndalarmhistory_20000000_ndalarm_id_fkey FOREIGN KEY (ndalarm_id)
      REFERENCES ndalarm (ndalarm_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT ndalarmhistory_20000000_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES users (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT ndalarmhistory_20000000_id_check CHECK (id >= 20000000 AND id < 25000000)
)
INHERITS (ndalarmhistory)
WITH (
  OIDS=FALSE
);

Indices auf dieser Tabelle:

CREATE INDEX ndalarmhistory_20000000_action_ix
  ON ndalarmhistory_20000000
  USING btree
  (action , ndalarm_id , actiontime  DESC);

CREATE INDEX ndalarmhistory__20000000_actiontime_ix
  ON ndalarmhistory_20000000
  USING btree
  (actiontime  DESC);

Abfrage:

select ActionTime from NDAlarmHistory  where Action  = 0  AND NDAlarm_id ='56' order by actiontime desc limit 1

Damit möchte ich aus aktuell 43 Mio Daten (unterteilt in child table a 5Mio Daten) die letzte Aktivitaet des Alarms 56 ermitteln (Für 56 sind ca. 25.000 Aktivitaeten gespeichert)

Explain Anlayze liefert:

Gülümser Köroğlu
Junior Software Engineer

________________________________

________________________________

nanodems Ltd.
Because Integration Matters

________________________________

________________________________

Gazi Teknopark 
Golbasi
06830
Ankara / Turkey

Tel:+90 312 485 06 78Fax: +90 312 485 06 78
________________________________

This e-mail and the files attached to it (if any) have been sent by the senderunder his/her own individual discretion; they can not be copied, disclosed or sold for any purpose.
If you are not the intended recipient (or have received this e-mail in error)please notify the sender immediately and destroy this e-mail.
Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
Nanodems ltd. sti. accepts no responsibility on the accuracy, integrity and currency of the information transmitted with this message.