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 ([email protected])
> 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