Re: Index-Verwendung

Hi,

On 23 May 2012 10:59, Gülümser Köroglu <gulumser(dot)koroglu(at)nanodems(dot)com> 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:

der Output fehlt hier ...

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

--
Andy Wenk
Hamburg - Germany
RockIt!

"CouchDB - Das Praxisbuch für Entwickler und Administratoren"
http://www.galileocomputing.de/2462
http://www.couchdb-buch.de

+++ english version is coming +++
http://www.couchdb-book.com

"PostgreSQL 8.4: Das Praxisbuch"
http://www.galileocomputing.de/2008
http://www.pg-praxisbuch.de