Re:

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