Re:

Am 23.05.2012 11:06, schrieb Gülümser Köroglu:
> (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?

1. Deine Partitionierung paßt nicht zur Abfrage und ist damit hier nicht
hilfreich.

2. Dein Index heißt im Explain ndalarmhistory_20000000_actionime_ix, das
paßt nicht zu den definierten.

Ich versteh grad nicht den Sprung für actual time in Deinem Explain,
daher die Frage: Du kannst Copy&Paste - Fehler ausschließen?

--
Andreas Kretschmer
http://internet24.de