Re:

genau …

und:
-> 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))"

die tabelle ist leer … da gibts für den index nichts zu filtern.
dort geht auch nicht die zeit verloren … schau auf die "actual time" werte.
da siehst du sofort, wo die zeit verloren geht.
führs noch ein zweites mal aus :) so zum vergleich :).

lg,

hans

On May 23, 2012, at 11:49 AM, Andreas Kretschmer wrote:

> 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
>
> --
> Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-de-allgemein
>

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de