Re: Zeit und Zeitzonen richtig konfigurieren

am 03.04.2006, um 16:42:06 +0200 mailte Johannes BrXgmann folgendes:
> > 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.

Korrekt.

Vermutung:
Da der Index über (zeit, otdf_id) geht, nützt er hier nix.
2 separate Indexe über otdf_id und zeit würden vermutlich helfen.

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

Ja, hier merkt er, daß er den Index doch nutzen kann, da er sehr
selektiv ist.

>
> > Welche Version hast Du?
>
> 8.0.4

Ab 8.1 geht Bitmap Index Scan, der würde möglicherweise auch einiges
bringen.

> Ist noch im Aufbaustadium, daher nicht regelmäßig. VACUUM ANALYZE habe

Ich würde echt empfehlen, auf 8.1 umzusteigen, wenn es noch im Aufbau
ist, ist das auch einfacher.

Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===