(Sorry, hier der vollstaendige Text)
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)
);

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 (constraint_exclusion = on):

"Limit  (cost=10000000000.18..10000000052.84 rows=1 width=8) (actual time=167306.867..167306.867 rows=1 loops=1)"
"  ->  Result  (cost=10000000000.18..10001542105.12 rows=29283 width=8) (actual time=167306.865..167306.865 rows=1 loops=1)"
"        ->  Merge Append  (cost=10000000000.18..10001542105.12 rows=29283 width=8) (actual time=167306.864..167306.864 rows=1 loops=1)"
"              Sort Key: public.ndalarmhistory.actiontime"
"              ->  Sort  (cost=10000000000.01..10000000000.01 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)"
"                    Sort Key: public.ndalarmhistory.actiontime"
"                    Sort Method: quicksort  Memory: 17kB"
"                    ->  Seq Scan on ndalarmhistory  (cost=10000000000.00..10000000000.00 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)"
"                          Filter: ((action = 0) AND (ndalarm_id = 56::bigint))"
"              ->  Index Scan using ndalarmhistory_0_actionime_ix on ndalarmhistory_0 ndalarmhistory  (cost=0.00..1085.45 rows=1 width=8) (actual time=0.136..0.136 rows=0 loops=1)"
"                    Filter: ((action = 0) AND (ndalarm_id = 56::bigint))"
"              ->  Index Scan using ndalarmhistory_15000000_actionime_ix on ndalarmhistory_15000000 ndalarmhistory  (cost=0.00..22130.41 rows=1 width=8) (actual time=158.244..158.244 rows=0 loops=1)"
"                    Filter: ((action = 0) AND (ndalarm_id = 56::bigint))"
"              ->  Index Scan using ndalarmhistory_20000000_actionime_ix on ndalarmhistory_20000000 ndalarmhistory  (cost=0.00..229801.02 rows=1167 width=8) (actual time=20621.925..20621.925 rows=0 loops=1)"
"                    Filter: ((action = 0) AND (ndalarm_id = 56::bigint))"
"              ->  Index Scan using ndalarmhistory_25000000_actionime_ix on ndalarmhistory_25000000 ndalarmhistory  (cost=0.00..229801.00 rows=1 width=8) (actual time=19104.889..19104.889 rows=0 loops=1)"
"                    Filter: ((action = 0) AND (ndalarm_id = 56::bigint))"
"              ->  Index Scan using ndalarmhistory_30000000_actionime_ix on ndalarmhistory_30000000 ndalarmhistory  (cost=0.00..229801.00 rows=1 width=8) (actual time=33631.463..33631.463 rows=0 loops=1)"
"                    Filter: ((action = 0) AND (ndalarm_id = 56::bigint))"
"              ->  Index Scan using ndalarmhistory_35000000_actionime_ix on ndalarmhistory_35000000 ndalarmhistory  (cost=0.00..229801.00 rows=1 width=8) (actual time=22692.553..22692.553 rows=0 loops=1)"
"                    Filter: ((action = 0) AND (ndalarm_id = 56::bigint))"
"              ->  Index Scan using ndalarmhistory_40000000_actionime_ix on ndalarmhistory_40000000 ndalarmhistory  (cost=0.00..177217.29 rows=28108 width=8) (actual time=199.805..199.805 rows=1 loops=1)"
"                    Filter: ((action = 0) AND (ndalarm_id = 56::bigint))"
"              ->  Index Scan using ndalarmhistory_5000000_actionime_ix on ndalarmhistory_5000000 ndalarmhistory  (cost=0.00..229801.00 rows=1 width=8) (actual time=37295.011..37295.011 rows=0 loops=1)"
"                    Filter: ((action = 0) AND (ndalarm_id = 56::bigint))"
"              ->  Index Scan using ndalarmhistory_10000000_actionime_ix on ndalarmhistory_10000000 ndalarmhistory  (cost=0.00..191620.80 rows=1 width=8) (actual time=33602.814..33602.814 rows=0 loops=1)"
"                    Filter: ((action = 0) AND (ndalarm_id = 56::bigint))"
"Total runtime: 167307.212 ms"

 

Wenn ich aber nun speziell in einer child tabelle suche:

select ActionTime from NDAlarmHistory_20000000  where Action IN(0)  AND NDAlarm_id IN ('56') order by actiontime desc limit 1

"Limit  (cost=0.00..3.45 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=1)"
"  ->  Index Scan using ndalarmhistory_20000000_action_ix on ndalarmhistory_20000000  (cost=0.00..4027.96 rows=1167 width=8) (actual time=0.018..0.018 rows=0 loops=1)"
"        Index Cond: ((action = 0) AND (ndalarm_id = 56::bigint))"
"Total runtime: 0.042 ms"

Extremer Unterschied!

Warum wird auch oben nicht der Index benutzt, welches die untere Anfrage benutzt hat ? Vor der Partitionierung wurde der multi column index benutzt und meine Abfragen aus dem selben Volumen (43 Mio) schneller beantwortet.

Was kann ich verbessern?

Danke..

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.