From: | Hans-Jürgen Schönig <postgres(at)cybertec(dot)at> |
---|---|
To: | Gülümser Köroglu <gulumser(dot)koroglu(at)nanodems(dot)com> |
Cc: | "pgsql-de-allgemein(at)postgresql(dot)org" <pgsql-de-allgemein(at)postgresql(dot)org> |
Subject: | Re: Index-Verwendung |
Date: | 2012-05-23 10:56:40 |
Message-ID: | 657267BE-EFAA-4C4E-A685-74C0835C1A0C@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-de-allgemein |
hallo …
du wirst einen eher langen plan haben, weil die constraint exclusion keine einzige tabelle exkludieren kann.
das könnte ein "längliches" explain sein, das in einem sort step mündet …
entweder du splittest anders auf oder du gibst ex-post noch mehr constraints dazu …
anders wirst du es nicht in den griff bekommen.
die ndalarm_id solltest du indizieren, damit du das neue "MergeAppend" feature nutzen kannst.
lg,
hans
On May 23, 2012, at 10:59 AM, Gülümser Köroglu wrote:
> 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 78 Fax: +90 312 485 06 78
> This e-mail and the files attached to it (if any) have been sent by the sender under 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.
>
>
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
From | Date | Subject | |
---|---|---|---|
Next Message | Hans-Jürgen Schönig | 2012-05-23 10:59:53 | Re: |
Previous Message | Andreas Kretschmer | 2012-05-23 09:49:28 | Re: |