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: | |
Date: | 2012-05-23 09:06:28 |
Message-ID: | 1337763988.47450.YahooMailNeo@web2811.biz.mail.ne1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-de-allgemein |
(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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2012-05-23 09:49:28 | Re: |
Previous Message | Andy Wenk | 2012-05-23 09:03:24 | Re: Index-Verwendung |