Re:

(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.

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

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

> >1. Deine Partitionierung paßt nicht zur Abfrage und ist damit hier
> nicht
> >hilfreich.
>
> Mein Fehler haette mehr Informationen geben müssen.
>
> Die Partinionierung der Tabellen erfolgt nach ID. Jede 5 millionte Daten
> werden automatisch in eine neu erzeugte Tabelle geschrieben.

Ja, das ist zu sehen, nur stellt sich (mir) die Frage: WARUM partitionierst Du?

Die 'übliche' Antwort auf diese Frage ist: um Abfragen zu beschleunigen. Indem man so partitioniert,
daß von vielen Kinde-Tabellen bereits durch constraint-exclusion ganz viele Tabellen ausgeschlossen werden.
(weil man aufgrund des Constraints weiß, daß diese Tabellen keine Daten, die auf die WHERE-Condition passen)

Damit reduziert man die zu durchsuchende Menge, aber das scheint ja bei Deiner Partitionierung nicht das Ziel
gewesen zu sein. Also: warum hast Du so partitioniert?

Wie viele mögliche Werte gibt es eigentlich für NDAlarm_id?

Mit freundlichen Grüssen

Andreas Kretschmer
- Technik -

--
HINWEIS: Der internet24-Support arbeitet im Team -
bitte senden Sie daher immer die komplette Mailkommunikation mit.

-------------------------------------------------
internet24 GmbH Bayrische Str. 18 D-01069 Dresden
Fon : +49 (0)3 51 / 2 11 20 30
Fax : +49 (0)3 51 / 2 11 20 20
E-Mail : kretschmer(at)internet24(dot)de
Facebook: internet24gmbh
URL : www.internet24.de
Blog : blog.internet24.de

Geschäftsführer: Heiko Heerwagen
Registergericht: Amtsgericht Dresden HRB 12 899

Andreas Kretschmer schrieb:
> Am 23.05.2012 11:06, schrieb Gülümser Köroglu:
[...]
>> 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

>> Explain Anlayze liefert (constraint_exclusion = on):
>>
[...]
>> " -> 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))"
[...]
> > "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.

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

Stimmt, das ist aber hier, glaube ich, nicht das Problem.

Es ist klar, daß über alle Partitionen abgefragt wird.
Aber warum wird bei jeder dieser Partitionen ein Index gescannt, der
nur "actiontime" enthält? Das heißt doch, der gesamte Index muß durchsucht
werden, nicht?
Dabei gibt es einen Index, der besser ist.

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

Ja, das ist auffällig.

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

Ich sehe keinen Sprung...

Leider kenne ich mich zu wenig mit Planer bei Partitionierung aus,
um mehr sagen zu können.

Liebe Grüße,
Laurenz

> -----Ursprüngliche Nachricht-----
> Von: pgsql-de-allgemein-owner(at)postgresql(dot)org [mailto:pgsql-de-allgemein-
> owner(at)postgresql(dot)org] Im Auftrag von Albe Laurenz
> Gesendet: Mittwoch, 23. Mai 2012 16:19
> An: Andreas Kretschmer *EXTERN*; pgsql-de-allgemein(at)postgresql(dot)org
> Betreff: Re: [pgsql-de-allgemein]
>
> Andreas Kretschmer schrieb:
> > Am 23.05.2012 11:06, schrieb Gülümser Köroglu:
> [...]
> >> 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
>
> >> Explain Anlayze liefert (constraint_exclusion = on):
> >>
> [...]
> >> " -> 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))"
> [...]
> > > "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.
>
> > 1. Deine Partitionierung paßt nicht zur Abfrage und ist damit hier
> nicht
> > hilfreich.
>
> Stimmt, das ist aber hier, glaube ich, nicht das Problem.

Meine Interpretation war eher so, daß er sich von der Partitionierung einen Performancegewinn erhoffte.

>
> Es ist klar, daß über alle Partitionen abgefragt wird.
> Aber warum wird bei jeder dieser Partitionen ein Index gescannt, der
> nur "actiontime" enthält? Das heißt doch, der gesamte Index muß
> durchsucht
> werden, nicht?
> Dabei gibt es einen Index, der besser ist.

Richtig. Ich bin mir nur nicht sicher, ob z.B. dieser Index schon bestand, als er die Abfrage laufen ließ, Oder so,
es ist jedenfalls schwer nachvollziehbar, siehe auch nächster Punkt.

Vielleicht nutzt er den 'falschen' Index, weil dieser kleiner ist, damit geringere Kosten hat. Oder die Statistiken waren
nicht aktuell.

>
> > 2. Dein Index heißt im Explain ndalarmhistory_20000000_actionime_ix,
> das
> > paßt nicht zu den definierten.
>
> Ja, das ist auffällig.

siehe oben ;-)

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

"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"

von unten nach oben: 0.007..0.007 -> 167306.864..167306.864, bei rows=1 a bissl heftig...

>
> Leider kenne ich mich zu wenig mit Planer bei Partitionierung aus,
> um mehr sagen zu können.

Naja, er geht halt alle in Frage kommenden Partitionen durch (basierend auf constraint exclusion) und pappt die einzelnen
Resultate zusammen. Keine große Magie. Nur wenn die Partitionierung nicht zur Abfrage paßt, hat man außer einem höheren Aufwand
( komplexerer Plan, prüfen der einzelnen constraints, zusammenpappen, ...) keinen Nutzen.

Bei 'nur' 43 Millionen Rows würde ich noch nicht so schnell an Partitionierung denken, aber das ist Geschmackssache und
von der Anwendung abhängig.

Mit freundlichen Grüssen

Andreas Kretschmer
- Technik -

--
HINWEIS: Der internet24-Support arbeitet im Team -
bitte senden Sie daher immer die komplette Mailkommunikation mit.

-------------------------------------------------
internet24 GmbH Bayrische Str. 18 D-01069 Dresden
Fon : +49 (0)3 51 / 2 11 20 30
Fax : +49 (0)3 51 / 2 11 20 20
E-Mail : kretschmer(at)internet24(dot)de
Facebook: internet24gmbh
URL : www.internet24.de
Blog : blog.internet24.de

Geschäftsführer: Heiko Heerwagen
Registergericht: Amtsgericht Dresden HRB 12 899

Andreas Kretschmer schrieb:
[schlechter Ausführungsplan für Abfrage auf partitionierter Tabelle]
>>> Ich versteh grad nicht den Sprung für actual time in Deinem Explain,
>>> daher die Frage: Du kannst Copy&Paste - Fehler ausschließen?
>>
>> Ich sehe keinen Sprung...
>
>
> "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"
>
>
> von unten nach oben: 0.007..0.007 -> 167306.864..167306.864, bei rows=1 a bissl heftig...

Ah, ich sehe Deinen Irrtum.
Der Sort bezieht sich nur auf die Elterntabelle:

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))

Weil diese Tabelle leer ist, ist ein Sequential Scan die beste Abfrageart.
Die Resultate müssen dann allerdings sortiert werden.

Bei den anderen Partitionen muß nicht sortiert werden, weil dort ein Index Scan
auf einen Index durchgeführt wird, der schon richtig sortiert ist.

Die tatsächliche Zeit des Merge Append ergibt sich so:
0.007 (ndalarmhistory)
0.136 (ndalarmhistory_0)
158.244 (ndalarmhistory_15000000)
20621.925 (ndalarmhistory_20000000)
19104.889 (ndalarmhistory_25000000)
33631.463 (ndalarmhistory_30000000)
22692.553 (ndalarmhistory_35000000)
199.805 (ndalarmhistory_40000000)
37295.011 (ndalarmhistory_5000000)
33602.814 (ndalarmhistory_10000000)
----------
167306.847

Ich beginne jetzt auch das eigentliche Problem besser zu verstehen (wahrscheinlich
weil ich so lange über dem Plan gebrütet habe, um diese Rechnung anzustellen):

Die Abfrage hat ein "order by actiontime".

Jetzt hat der Planer zwei Möglichkeiten:

Entweder er nimmt den "schnellen" Index auf jeder Partition und sortiert nachher
die geschätzten 29283 Rows, oder er nimmt den "langsameren" Index, muß dann aber
nicht mehr sortieren. Scheinbar hat die zweite Variante die bessere Kostenschätzung
ergeben.

Insofern kann man auch die beiden Abfragen (auf die ganze Tabelle oder nur auf die
Partition) nicht vergleichen, weil die Sortierung nach "actiontime" *vor* dem
LIMIT 1 passiert. Das heißt, bei der ganzen Tabelle muß möglicherweise viel
mehr sortiert werden.

Jetzt ist mir auch klar, wo der Hund begraben liegt: bei der schlechten Schätzung
in den Partitionen ndalarmhistory_20000000 (1167 geschätzte Rows statt tatsächlich 0)
und ndalarmhistory_40000000 (28108 geschätzte Rows statt tatsächlich 1).

Ich bin ziemlich sicher, daß ein anderer Plan verwendet wird, wenn diese
Schätzungen besser werden.

Sind die Statistiken aktuell? Ändert eine Erhöhung von default_statistics_target
vor einem ANALYZE etwas?
Speziell vielleicht
ALTER TABLE ndalarmhistory_40000000
ALTER ndalarm_id SET STATISTICS 1000
ALTER action SET STATISTICS 1000;

Liebe Grüße,
Laurenz Albe

> -----Ursprüngliche Nachricht-----
> Von: Albe Laurenz [mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at]
> Gesendet: Donnerstag, 24. Mai 2012 09:54
> An: Andreas Kretschmer *EXTERN*; pgsql-de-allgemein(at)postgresql(dot)org
> Betreff: RE: [pgsql-de-allgemein]
>
> Andreas Kretschmer schrieb:
> [schlechter Ausführungsplan für Abfrage auf partitionierter Tabelle]
> >>> Ich versteh grad nicht den Sprung für actual time in Deinem Explain,
> >>> daher die Frage: Du kannst Copy&Paste - Fehler ausschließen?
> >>
> >> Ich sehe keinen Sprung...
> >
> >
> > "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"
> >
> >
> > von unten nach oben: 0.007..0.007 -> 167306.864..167306.864, bei
> rows=1 a bissl heftig...
>
> Ah, ich sehe Deinen Irrtum.
> Der Sort bezieht sich nur auf die Elterntabelle:
>
> 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))
>
> Weil diese Tabelle leer ist, ist ein Sequential Scan die beste
> Abfrageart.
> Die Resultate müssen dann allerdings sortiert werden.
>
> Bei den anderen Partitionen muß nicht sortiert werden, weil dort ein
> Index Scan
> auf einen Index durchgeführt wird, der schon richtig sortiert ist.
>
> Die tatsächliche Zeit des Merge Append ergibt sich so:
> 0.007 (ndalarmhistory)
> 0.136 (ndalarmhistory_0)
> 158.244 (ndalarmhistory_15000000)
> 20621.925 (ndalarmhistory_20000000)
> 19104.889 (ndalarmhistory_25000000)
> 33631.463 (ndalarmhistory_30000000)
> 22692.553 (ndalarmhistory_35000000)
> 199.805 (ndalarmhistory_40000000)
> 37295.011 (ndalarmhistory_5000000)
> 33602.814 (ndalarmhistory_10000000)
> ----------
> 167306.847
>

*Patsch*

Ja, nun sehe auch ich meinen Irrtum, Danke für Deine Mühe!

Und ACK zum Rest Deiner Ausführungen.

Mit freundlichen Grüssen

Andreas Kretschmer
- Technik -

--
HINWEIS: Der internet24-Support arbeitet im Team -
bitte senden Sie daher immer die komplette Mailkommunikation mit.

-------------------------------------------------
internet24 GmbH Bayrische Str. 18 D-01069 Dresden
Fon : +49 (0)3 51 / 2 11 20 30
Fax : +49 (0)3 51 / 2 11 20 20
E-Mail : kretschmer(at)internet24(dot)de
Facebook: internet24gmbh
URL : www.internet24.de
Blog : blog.internet24.de

Geschäftsführer: Heiko Heerwagen
Registergericht: Amtsgericht Dresden HRB 12 899