Re: Zeit und Zeitzonen richtig konfigurieren

Hallo Andreas,
hallo Liste,

danke nochmals für Deine Mühe, Andreas!

"A. Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> writes:

> am 03.04.2006, um 14:48:14 +0200 mailte Johannes BrXgmann folgendes:
>> >> 5. Für (c) benötige ich nun eine performantere Anfrage, die zu jedem
>> >> Fremdschlüssel-Tupel seine aktuellsten Datensätze liefert. Eine
>> >> Aggregatfunktion newest(timestamptz) oder so habe ich nicht gefunden,
>> >
>> > max(timestamptz)
>>
>> habe ich probiert; Antwortzeit > 1 min; viel zu langsam. Ich bräuchte
>> irgendetwas < 10s, eher weniger. Noch andere Ideen?
>
> Zeig uns die Tabellenstruktur und ein explain analyse der Abfrage.
> Ich habe auf Hinterhof-hardware in einer mehrere Millionen Zeilen
> umfassender Tabelle und Suche nach vielleicht 6 Bedingungen Suchzeiten
> im 2-stelligen Millisekundenbereich.

1. Tabelle:
===========

CREATE TABLE verkehrsdaten_current (
zeit TIMESTAMP WITH TIME ZONE,
otdf_id char(14),
FOREIGN KEY (otdf_id) REFERENCES compat_otdf (otdf_id),
fluss smallint,
dichte real,
geschwindigkeit smallint,
reisezeit smallint,
verkehrslage smallint,
CHECK (((fluss > 0) AND (fluss < 360))
AND ((dichte > 0.009) AND (dichte < 200.0))
AND ((geschwindigkeit > 0) AND (geschwindigkeit < 150))
AND ((reisezeit > 1) AND (reisezeit < 3600))
AND ((verkehrslage > 0) AND (verkehrslage < 10))),
PRIMARY KEY (zeit, otdf_id)
);

CREATE UNIQUE INDEX verkehrsdaten_current_zeit_index ON verkehrsdaten_current(zeit, otdf_id);

Index habe ich vorhin erstellt.

2. Anfragen und Dauer
=====================
- grösser als 1 Minute, Hardware: Dual Xeon 3,6 GHz 1GB RAM:
SELECT DISTINCT otdf_id,
max(zeit)
FROM verkehrsdaten_current
GROUP BY otdf_id;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=397630.85..397640.12 rows=1236 width=26) (actual time=128562.326..128564.593 rows=1240 loops=1)
-> Sort (cost=397630.85..397633.94 rows=1236 width=26) (actual time=128562.323..128562.924 rows=1240 loops=1)
Sort Key: otdf_id, max(zeit)
-> HashAggregate (cost=397564.28..397567.37 rows=1236 width=26) (actual time=128558.353..128559.832 rows=1240 loops=1)
-> Seq Scan on verkehrsdaten_current (cost=0.00..355692.52 rows=8374352 width=26) (actual time=70999.398..118637.183 rows=8415880 loops=1)
Total runtime: 128565.455 ms
(6 Zeilen)

Lese ich das richtig, dass Postgres hier einen Sequential-Scan über
8415880 Zeilen durchführt? Ich verstehe nicht warum.

- weniger als 1 Sekunde:
SELECT DISTINCT otdf_id,
max(zeit)
FROM verkehrsdaten_current
WHERE zeit > localtimestamp(0) - interval '3 hour'
GROUP BY otdf_id;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1472.74..1472.75 rows=1 width=26) (actual time=389.795..393.038 rows=1240 loops=1)
-> Sort (cost=1472.74..1472.75 rows=1 width=26) (actual time=389.793..390.600 rows=1240 loops=1)
Sort Key: otdf_id, max(zeit)
-> HashAggregate (cost=1472.73..1472.73 rows=1 width=26) (actual time=383.276..385.640 rows=1240 loops=1)
-> Index Scan using verkehrsdaten_current_zeit_index on verkehrsdaten_current (cost=0.01..1468.62 rows=822 width=26) (actual time=0.106..247.613 rows=71920 loops=1)
Index Cond: (zeit > (('now'::text)::timestamp(0) without time zone - '@ 3 hours'::interval))
Total runtime: 394.069 ms
(7 Zeilen)

Die 3 Stunden bei interval bedeuten letztlich nur Daten, die höchstens
eine Stunde zurückliegen, siehe OP.

> Welche Version hast Du?

8.0.4

>> >> ich bin mir aber auch nicht sicher, ob das der richtige Weg
>> >> ist, denn da muss die Datenbank doch das jeweilige Maximum ermitteln
>> >> (alle Datensätze durchsuchen), oder? Welcher Weg ist am
>> >
>> > Nein, nicht, wenn ein Index drauf liegt.
>>
>> Hm. Ein Primärschlüssel legt doch einen impliziten Index an. Reicht
>> das nicht?
>
> Jein, hast Du weitere Spalten in der WHERE, wo kein Idex ist? Regelmäßig
> VACUUM? Ein 'EXPLAIN' bzw. 'EXPLAIN ANALYSE' kann Dir die Schwachstellen
> zeigen.

Ist noch im Aufbaustadium, daher nicht regelmäßig. VACUUM ANALYZE habe
ich aber Freitag oder so laufen lassen.

Danke jedenfalls,
Johannes
--