From: | Andy Wenk <andy(at)nms(dot)de> |
---|---|
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 09:03:24 |
Message-ID: | CADA9kr92EJsJ52avgyeymDcMZ5QUryGq2soNsvweqC4fGuAfjw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-de-allgemein |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gülümser Köroglu | 2012-05-23 09:06:28 | |
Previous Message | Gülümser Köroglu | 2012-05-23 08:59:22 | Index-Verwendung |