From: | Gülümser Köroglu <gulumser(dot)koroglu(at)nanodems(dot)com> |
---|---|
To: | "pgsql-de-allgemein(at)postgresql(dot)org" <pgsql-de-allgemein(at)postgresql(dot)org> |
Subject: | Index-Verwendung |
Date: | 2012-05-23 08:59:22 |
Message-ID: | 1337763562.65561.YahooMailNeo@web2819.biz.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-de-allgemein |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Wenk | 2012-05-23 09:03:24 | Re: Index-Verwendung |
Previous Message | Michael Renner | 2012-05-07 09:20:47 | Re: Insert-Zeit |