Skip site navigation (1) Skip section navigation (2)

Re: Zeit und Zeitzonen richtig konfigurieren

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-de-allgemein(at)postgresql(dot)org
Subject: Re: Zeit und Zeitzonen richtig konfigurieren
Date: 2006-04-03 16:48:45
Message-ID: 20060403164845.GA9283@webserv.wug-glas.de (view raw or flat)
Thread:
Lists: pgsql-de-allgemein
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    === 

In response to

pgsql-de-allgemein by date

Next:From: Andreas SeltenreichDate: 2006-04-03 16:54:19
Subject: Re: Zeit und Zeitzonen richtig konfigurieren
Previous:From: Johannes BrgmannDate: 2006-04-03 14:42:06
Subject: Re: Zeit und Zeitzonen richtig konfigurieren

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group